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
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:Now if you run like:
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:and you can remove the
setParameters
from the query builder: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: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.
Refer to the docs here http://docs.doctrine-project.org/en/latest/reference/query-builder.html#binding-parameters-to-your-query.
I think you just need to remove colon from
setparameter
commandAt least it works in Doctrine help http://docs.doctrine-project.org/en/latest/reference/query-builder.html