doctrine dbal querybuilder as prepared statement

Question

I'm trying to create a Doctrine DBAL querybuilder object and setting a parameter in it. (using a postgres db, dbal 2.3.4, doctrine

$connection = $this->_em->getConnection();
$qb = $connection->createQueryBuilder();
$qb->select('tbl_user_contract.pkid AS pkid');
$qb->from('tbl_user_contract', 'tbl_user_contract');
$qb->join('tbl_user_contract', 'tbl_user', 'tbl_user', 'tbl_user_contract.fk_user = tbl_user.pkid');
$qb->where('tbl_user.pkid = :userid');
$qb->setParameter(':userid', 10);

When I try to get the results of this querybuilder object I get the following error:

SQLSTATE[08P01]: <<Unknown error>>: 7 ERROR: bind message supplies 0 parameters, 
but prepared statement "pdo_stmt_00000002" requires 1

When I check the postgres logs, I see the query passing by and I notice that it expects a parameter, but I won't get a parameter passed in.

I tried to set the id in the where expression itself (without using prepared statements), that worked. But I really want to get this working with prepared statements.

Anyone knows how to solve this?

Thanks in advance

You use a `?` wildcard for the param, but then try to set it by name. Usually you have to use *either* named parameters or ordinal position parameters, not mix the two. Maybe you need to try something like `$qb->setParameter(1, 10);` ? (it might count from zero or from one). I don't use Doctrine or PHP so this is untested. — Craig Ringer, Aug 23 '13 at 09:12
Excuse me, did something wrong when copy-pasting parts of code to stackoverflow. Changed it to both named parameters, but the problem still occurs. — user2710303, Aug 23 '13 at 09:25

score 5 · Answer 1 · edited Aug 05 '19 at 19:42

5

I think you just need to remove colon from setparameter command

$qb->setParameter('userid', 10);

At least it works in Doctrine help https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/query-builder.html

edited Aug 05 '19 at 19:42

Nek

2,715
1
20
34

answered Aug 23 '13 at 12:12

Roman Pekar

107,110
28
195
197

That doesn't seem to do the trick. Same error when I'm not using the colon. – user2710303 Aug 23 '13 at 12:56

score 1 · Answer 2 · answered Jan 22 '14 at 18:48

The setParameter part of your querybuillder function is wrong. You do not need the :, you can put it all on one line like this, and only include one talbe in your FROM statement. You may have a problem in your JOIN or FROM statement if there is another table named tbl_user and need to check your entity definitions to make sure the annotations are correct.

$connection = $this->_em->getConnection();
$qb = $connection->createQueryBuilder();
$qb->select('tbl_user_contract.pkid AS pkid')
  ->from('tbl_user_contract')
  ->join('tbl_user_contract', 'tbl_user', 'tbl_user', 'tbl_user_contract.fk_user =   tbl_user.pkid')
  ->where('tbl_user.pkid = :userid')
  ->setParameter('userid', 10);

Refer to the docs here http://docs.doctrine-project.org/en/latest/reference/query-builder.html#binding-parameters-to-your-query.

score 1 · Answer 3 · edited Apr 06 '18 at 11:44

I ran into the same problem with DBAL 2.5.13.

I'm writing a tool that uses Symfony components and DBAL, therefore there is no entityManager object but to maintain similar structure due team previous knowledge with Symfony, I made a repository class with a method like:

public function getAtendimentoRealFromOffline($foo3, $foo4)
{
    $query = $this->createQueryBuilder()
                    ->select("foo1, foo2")
                    ->from("bar_table")
                    ->andWhere("foo3 = :foo3")
                    ->andWhere("foo4 = :foo4")
                    ->setParameter(":foo3", $foo3)
                    ->setParameter(":foo4", $foo4);
}

Now if you run like:

$this->connection->fetchAll($query);

It will indeed show you the error, because you're using the fetchAll from connection with no relation at all with the statement you just created with QueryBuilder. One solution would be use the second parameter to send an array of parameters:

$this->connection->fetchAll($query, ["foo3" => "bar3", "foo4" => "bar4"]);

and you can remove the setParameters from the query builder:

    $query = $this->createQueryBuilder()
                    ->select("foo1, foo2")
                    ->from("bar_table")
                    ->andWhere("foo3 = :foo3")
                    ->andWhere("foo4 = :foo4")

What can be unseen by the documentation is that the QueryBuilder actually has an execute method, which will then indeed run with the parameters, therefore you can just:

$query->execute()->fetchAll();

score 0 · Answer 4 · answered Aug 05 '19 at 16:38

The documentation states you can do the following:

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ' .  $queryBuilder->createNamedParameter($userInputEmail))
;

https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/query-builder.html#binding-parameters-to-placeholders

score 0 · Answer 5 · answered Nov 05 '20 at 23:08

try this:

$qb->where('tbl_user.pkid = :userid');
$qb->setParameter(':userid', 10);

then

 $params = $qb->getParameters();
 $stmt = $connection->prepare($qb->getSQL());
 foreach ($params as $key=>$value){
    $stmt->bindParam($key,$value);
 }

$stmt->execute();
$result = $stmt->fetchAllAssociative();

doctrine dbal querybuilder as prepared statement

5 Answers5

Linked