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

回答1:

I think you just need to remove colon from setparameter command

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

At least it works in Doctrine help http://docs.doctrine-project.org/en/latest/reference/query-builder.html

回答2:

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.

回答3:

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