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

Invalid data types returned by Phalcon\Db

Hi,

In short: I have a MySQL table containing "type" column that is defined as TINYINT. When I pull that column using Phalcon\Db, it is cast as string in resulting PHP array.

Here is the code:

$sql = 'SELECT type FROM table WHERE account_id=:id';
$result = $db->query($sql, ['id' => $id], ['type' => \Phalcon\Db\Column::TYPE_INTEGER]);

Regardless of what PDO is doing, I expected that the third parameter would cast value of "type" key as integer PHP type.

What am I doing wrong here?

Thanks, Temuri



98.9k

Integers are returned as strings in PDO, \Phalcon\Db\Column::TYPE_INTEGER is not a cast, it tells PDO the type of the parameter passed in the placeholder with the same name:

$sql = 'SELECT type FROM table WHERE account_id=:id';
$result = $db->query($sql, ['id' => $id], ['id' => \Phalcon\Db\Column::TYPE_INTEGER]);


51.3k
Accepted
answer

Alright, thanks. The documentation does not even mention that third argument.

Here's how to fix:

return new Phalcon\Db\Adapter\Pdo\Mysql([
    'host' => ,
    'username' => ,
    'password' => ,
    'dbname' => ,
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_STRINGIFY_FETCHES => false,
    ]
]);

Cheers.



8.1k
edited Jun '14

@temuri416 setting ATTR_EMULATE_PREPARES to false actually works for you?

I get SQLSTATE[HY093]: Invalid parameter number errors when setting it to false. (Those get triggered e.g. when calling save on a Phalcon\Mvc\Model.)

Searching for it, I did found this: https://stackoverflow.com/a/14294393 (so perhaps the errors have to do with the way Phalcon uses variable placeholders?)

edit: I'm using MariaDB 10 (but AFAIK that should also support the attribute)

Hello,

Although this thread is marked as solved, I'm having the same problem. Integers in the database show up as strings in my model classes.

When using the @temuri416 solution, models correctly return integers on those related fields, but I start to get a lot of SQLSTATE errors just like @renskii.

Shouldn't PDO::ATTR_STRINGIFY_FETCHES=false be enough to get integers out of the models? But it's the setting PDO::ATTR_EMULATE_PREPARES=false that creates the errors.

Has anybody be able to find a solution for this?

I'm using MariaDB (10.0.20) with mysqlnd driver (php5-mysqlnd 5.6.4), in Ubuntu.



144

Hello, I was not able to use the options set as above (using phalcon 3.2.0)

In case it happens to you the same, you can you use the following:


$pdoHandler = $connection->getInternalHandler();
$pdoHandler->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdoHandler->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);