We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Querybuilder & PDO & Binding & data types

Hi all,

I've tried everything I could think of but can't get the QueryBulder to return integers when making counting queries or using partial models (FYI different than when using a full model). I've read quite a lot, and will drop a few links below 'cos that could be useful to others:

https://forum.phalcon.io/discussion/263/binding-parameters-to-querybuilder https://github.com/phalcon/cphalcon/issues/2111 https://stackoverflow.com/questions/22084499/how-to-avoid-pdostatementbindparam-messing-with-the-referenced-value https://github.com/phalcon/cphalcon/issues/2111 https://www.php.net/manual/en/pdostatement.bindparam.php

So now, my setup: I'm on Ubuntu16, php7.0.8, mysql 5.5.44, phalcon 3.0, php-mysqlnd and I've tried a few things:

±1. I though I set up PDO with PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_STRINGIFY_FETCHES => false... The query builder then returns numeric values, great, but the basic model features stop working (oModel->create doesn't update the pk/id, oModel->update doesn't update the DB)

±2. Tried to force bind params in the QB, but nothing:

$aParams = [
'models' => ['client' => '\Phapp\Model\Client'],
'columns' => ['client.clientpk'],
'order' => ['client.date_created']
];
$oQueryBuilder = $oService->modelsManager->createBuilder($aParams);
$oQueryBuilder->andWhere('client.clientpk = :clientpk:', ['clientpk' => 1], ['clientpk' => \Phalcon\Db\Column::BIND_PARAM_INT]);
$oQuery = $oQueryBuilder->getQuery()->execute();

but that still returns a strings.

±3. I tried to play with $oQuery->getBindParams(), $oQuery->getBindTypes() , change PDO setting on teh fly... nothing.

So I take any input/solution on this problem, because it looks like the answer end up being there (https://stackoverflow.com/questions/22084499/how-to-avoid-pdostatementbindparam-messing-with-the-referenced-value & https://www.php.net/manual/en/pdostatement.bindparam.php), in the fact we should be able to bindValues instead of bindParams.

Cheers



85.5k

i am using both PDO::ATTREMULATEPREPARES => false and PDO::ATTRSTRINGIFYFETCHES => false, and for me this is working for me:

(new Model(['name' => "myname"] ) -> create();

or you mean some relations are not working ? Cuz in my app i use only create and update

If anybody is interested, I've finally found the problem after a long and painful debgging session. The issue is related to boolean columns in Mysql and the BIND_PARAM_BOOLEAN in the model.

For some reason, with my current setup, if I bind a model attribute with BIND_PARAM_BOOLEAN, it fails to create/update records. Changing the BIND_PARAM to _INT or the DB column to integer seems to fix the problem. So I don't know if the problem comes from the link between mysql-server & php7-mysqlnd , or from php7-PDO and Phalcon3 model bu tthe problem is there.

Will try to use a different mysql version to see if it makes things better (was on Ubuntu 15, will move to Ubuntu16 too), but otherwise I'll replace all my boolean columns to integers.



85.5k

you can create a simple test case and put it as an issue in Git if you fint that it is a bug at the end.

I have the same problem, I just found this thread, I put on github an issue + a gist : https://github.com/phalcon/cphalcon/issues/12197 https://gist.github.com/pfz/310cba1a8d0e6cb5a9c1617c713b249c