Phalcon\Db\Adapter\Pdo messes up the input data

[iby]

That drove me ABSOLUTE NUTTTTS!!! After making this call with $params array of int values, those values become strings.

var_dump($params); // array(2) { [0]=> int(6609) [1]=> int(6664) }
$adapter->fetchAll($sql, Db::FETCH_ASSOC, $params);
var_dump($params); // array(2) { [0]=> string(4) "6609" [1]=> string(4) "6664" }

It's crazy, even if I do array_values I end up with the same shit:

$adapter->fetchAll($sql, Db::FETCH_ASSOC, array_values($params));

Can we not use references in such calls? When we use references in C, does it actually help with anything or are they treated by zend compiler in the same way they are in PHP? Because if they do – it might be completely pointless. This might be a really dumb question, sorry, I'm taking the shot.

[phalcon]

PDOStatement::bindParam passes values as references so it could eventually transform the passed values.

http://www.php.net/manual/en/pdostatement.bindparam.php

[iby]

Crap. Ok, so it's nothing to do with Phalcon then…

[iby]

As others say – http://www.php.net/manual/en/pdostatement.bindparam.php#94711

[iby]

Actually, can we not close this. I've looked into this further and it seems to be a very painful experience laid out here – http://stackoverflow.com/q/22084499/458356

Can we somewhere along those lines https://github.com/phalcon/cphalcon/blob/1.3.0/ext/db/adapter/pdo.c#L430 copy the value and pass that copy as a reference to retain values in the original array from changing? Or at least limit that change to the supplied array, not arrays it's been copied from?

Phalcon documentation doesn't imply that the value is being passed as reference + I don't see any sensible reason why one would want to get such results. But it might be just agonised myself…

🍻

[iby]

Or well, as Mike suggests: Use bindValue instead of bindParam and then the original variables won't be changed.

[iby]

Works like a charm! Can we keep it mommy??? :)

[kowach]

If you install php-mysqlnd instead if php-mysql, bindValue will not convert variable from string to int. Additional, result set from database will return correct variable types (if it's integer it will be returned integer). Php-mysql converts all variables to string.

[iby]

It appears to be the default driver? Where did you get this information?

[iby]

Andres, regarding your comment in #2101, I'm not sure what you meant by 'there are no applications' or how it relates to this issue. Is there any reason why bindParam used over bindValue? In the nutshell:

• bindValue() binds just a value, it's like a a hard copy.
• bindParam() binds a variable, and when a variable got changed, the binded value will be changed as well.

All values in Phalcon are binded at the time of executing the query (correct if I'm wrong). This means that even if you'd want to change referenced values, that will have no affect on the result (query will be already sent by the time you can do that). In other words the functionality of bindParam is not utilised, but creates problems as per my situation.

[kowach]

This works with mysqlnd driver and these two attributes set to false:

$dbh->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$parm1 = 2;
var_dump($parm1);
//  int(2)

$sth = $dbh->prepare('SELECT *  FROM test.table1  WHERE id = ?');
$sth->bindParam(1, $parm1, PDO::PARAM_INT);
$sth->execute();
$data = $sth->fetchAll(PDO::FETCH_ASSOC);
var_dump($parm1);
// int(2)

var_dump($data);

/* you get nice and clean result:
array(1) {
  [0]=>
  array(4) {
    ["id"]=>  int(2)
    ["name"]=>  string(3) "XYZ"
    ["someint"]=>  int(543)
    ["somefloat"]=>   float(1000.0001220703)
  }
}

And table is:
CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `someint` int(8) DEFAULT NULL,
  `somefloat` float(10,5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

*/

[iby]

@kowach, I tried $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); in the past, that didn't work. I just tried it with $dbh->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false); – also didn't help. Can you provide the actual Phalcon example, not the bare bones one? This is what's going on in my DI.

$this->setShared('db', function () use ($config) {                                               
    $mySql = new Mysql([                                                                         
        'host'     => $config->database->host,                                                   
        'dbname'   => $config->database->dbname,                                                 
        'username' => $config->database->username,                                               
        'password' => $config->database->password                                                
    ]);                                                                                          

    $mySql->getInternalHandler()->setAttribute(\PDO::ATTR_STRINGIFY_FETCHES, false);             
    $mySql->getInternalHandler()->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);              

    return $mySql;                                                                               
});                                                                                              

You do $sth->bindParam(1, $parm1, PDO::PARAM_INT); to explicitly set the type. The whole idea of "automatic type casting" is not to explicitly specify the types, otherwise it's not really automatic… Try binding params without the type, see what happens?

When fetching rows I don't want to configure every query params types manually – that's the whole point. Currently to keep the parameters unmodified you can do this with each query (exactly what you did in your example).

$paramTypes = [];                                                                 

foreach ($params as $param) {                                                     
    if (is_int($param)) {                                                         
        $paramTypes[] = \PDO::PARAM_INT;                                          
    } else {                                                                      
        $paramTypes[] = null;                                                     
    }                                                                             
}                                                                                 

$rows = $db->fetchAll($sql, Db::FETCH_ASSOC, $params, $paramTypes);    

$paramTypes argument is not documented, but works. In anyway, this is an overkill and should be fixed in the framework not in a workaround like this.

[iby]

Also, the example above works fine without ATTR_EMULATE_PREPARES or ATTR_STRINGIFY_FETCHES.

[ryomo]

This works for me.

Install php5-mysqlnd

sudo apt-get install php5-mysqlnd

OK php5-mysqlnd
NG php5-mysql

Edit services.php

$di->set('db', function () use ($config) {

    $connection = new MysqlAdapter([
        'host'          => $config->database->host,
        'username'      => $config->database->username,
        'password'      => $config->database->password,
        'dbname'        => $config->database->dbname,
        'charset'       => $config->database->charset,
        'options'       => [
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_STRINGIFY_FETCHES => false,
        ]
    ]);

    return $connection;
});

Test

$robot = Robot::findFirst();
var_dump($robot->id);

shows int(1) :smile

[peynman]

@ryomo's answer worked
than you sir