CODEWITHSUNDEEP
phppostgresqlsymfonydoctrine-ormdoctrine
user2710303
user2710303

Reputation: 1

doctrine dbal querybuilder as prepared statement

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

Upvotes: 0

Views: 7514

Answers (5)

zakrzu
zakrzu

Reputation: 595

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();

Upvotes: 0

roman
roman

Reputation: 117550

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

Upvotes: 5

Nek
Nek

Reputation: 3125

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

Upvotes: 0

Fabiano
Fabiano

Reputation: 433

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();

Upvotes: 1

George
George

Reputation: 1499

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.

Upvotes: 1

Related Questions