Invalid data types returned by Phalcon\Db

Question

Invalid data types returned by Phalcon\Db

temuri416 Sep '13

Created Sep '13	Last Reply Sep '17	Replies 4	Views 1619	Votes 0

temuri416 51.3k

Sep '13

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

temuri416
51.3k

Accepted
answer

Sep '13

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.

Phalcon · Answer 1 · 2013-09-09T08:16:35-07:00

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

ren · Answer 2 · 2014-06-03T00:19:09-07:00

@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)

Alexandre Cavaco · Answer 3 · 2015-07-24T03:06:13-07:00

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.

Oscar · Answer 4 · 2017-09-18T07:09:15-07:00

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