Native data types from MySQL - should be possible

Out of the box CodeIgniter does not return the correct data types from MySQL databases, regardless of which database driver you use (mysql, mysqli or PDO). This can lead to problems when using json_encode() to send the results via an Ajax request.

Is there a simple solution to this? It should be possible using PDO. I know this because...

I'm occasionally using a third-party database library which is part of the Kendo UI Javascript library (you can find the source here: https://github.com/telerik/kendo-keynote...Result.php)

This makes it simple to support Kendo's required data format for filtering, sorting, inserting, updating etc. I've also made a small change so that it returns native data types by issuing:

PHP Code:

$this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 


I don't claim to know anything about the inner workings of CodeIgniter's database drivers, but if this third-party database library can return native data types, surely CodeIgniter can too?

[EDIT] Turns out it's simply a case of adding:

PHP Code:

$this->options[PDO::ATTR_EMULATE_PREPARES] = false; 


to the db_connect function in pdo_mysql_driver.php

Is there a way to do this without editing the system file?

CI is not going to change to do what you're suggesting, and database drivers are the only non-extendable components in CI, so I'm afraid you can't do that.

Not in a non-hacky way at least. The PDO object itself is exposed as the $conn_id property (given that you are using PDO of course), so you can manually do something like this:

Code:

$this->db->conn_id->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

If you want it to always be applied, you can create a MY_Loader extension instead and override the database() method in it ...

Thanks.

I won't bother asking why this can't be added to CI, but your suggestion works just fine. I'm loading my database connections in a MY_Controller, so I've just stuck your suggestion in there.

You had me going for a couple of minutes because I cut-and-pasted your line and it wasn't working. Then I noticed it was setting the ATTR_EMULATE_PREPARES to TRUE rather than FALSE. All good now.

Thanks again.

Is this just for boolean fields? If possible you can avoid this issue entirely by using tinyint 0/1 instead. A few years ago I had to transition an app from MSSQL to MySQL and with this setup queries work the same for both engines.

It's a little odd that this particular setting seems to have the effect you're looking for. The third-party library mentioned passes the data through specific functions for specific types, returning the data in a format which may be more consistent with conversion to JSON, but is not necessarily closer to the native types (maybe PDO is mangling the type information without this setting, I don't really know, but in that case MySQLi should have worked).

JSON has never specified a date format, so different JavaScript libraries may implement them in different ways (though more recent versions of JavaScript include a toJSON() method on Date, giving a format to prefer in libraries which are aware of this format). While JavaScript's Date.toJSON() returns dates in an ISO-8601-formatted string, MySQL does not use this format for DATETIME or TIMESTAMP values (the DATE format happens to conform to this standard, but that probably was coincidental).

Because of that coincidence, passing MySQL dates through the date() function in your select statement does tend to make them conform for many JavaScript libraries, though you lose your time on DATETIME values (and TIMESTAMP values usually have to be passed through a different function). Using date_format(date, {format_string}) is usually the best option for getting the correct format directly from the database when using MySQL.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide