tl;dr: It is not the job of a database system (SQLite, MySQL, …) to cast queried data into each column’s type. A database will return data as string, even for integer columns. So if your query returns you any other type than string, then it is typically tanks to the database driver or the ORM/Model.
With PHP/PDO/MySQL, it is actually the Mysqlnd driver that do this cast (at least for string to integer). Just call phpinfo() to make sure the pdo_mysql driver actually uses the Mysqlnd driver. Using PDO, you also need to make sure that its PDO::ATTR_EMULATE_PREPARES option is set to false and not use PDO::query().
The little story of this discovery in three acts
1/
When querying a database, I always took for granted to get data from integer columns as actual integer. At the same time, I hadn’t much data point since until now I never queried anything else than MySQL from PHP with PDO…
I am currently working on tests for my project MiniCMS-OSV. It was a good occasion for me to use an in-memory SQLite database instead of MySQL because it is faster, so why not.
I quickly found out that a query to SQLite would only return strings, apparently ignoring the type of each column.
“Well whatever, I will just cast manually whenever I fetch data for the tests” I tell myself. Except that I also try to be a good PHP programmer and I use the strict equel/not-equal operators (=== !==) pretty much everywhere in the code of the CMS, so a lot of it would just not work properly during the tests…
So I set out to search why SQLite is behaving like this and if possible how to tell it to behave like I want. Nothing StackOverflow and some good google-fu can’t fix, right ?
Right. Turns out SQLite don’t really care about type. You can put any kind of data in any kind of column, SQLite won’t complain, and it will always return data as string, unless I expressively cast it during or after the request.
Due to the nature of the project and the absence (by design) of anything resembling a model or an abstraction layer, I cannot implement any of these two solutions easily.
The obvious solution at this point is to use MySQL for the tests, too. After all, it is pertinent to have a test environment as close as the “production” one. At this time I only had the tests for the register and login pages, so there is not much to do to switch to MySQL.
2/
Ok the fix is in, let’s punch Shift+F10 one more time ! Still doesn’t work… Data from integer columns are still returned as string, even though i am sure to use MySQL this time…
A significant amount of time, and a good dozen of tabs open on StackOverflow later, I indeed learn that this is not any database’s job to return data in anything other than string.
In the case of PHP I have to make sure that the pdo_mysql driver uses in turn the Mysqlnd driver. I double check: this is the case for me.
More time, more tabs, more infos, sometimes contradictory… Apparently this is now PDO’s fault because it emulates prepared statements at its level instead of letting the actual database driver do it. All I should have to do is set the PDO::ATTR_EMULATE_PREPARES option to false when creating the PDO instance.
3/
So let’s do that … and get the apparently popular General error: 2014 Cannot execute queries… fatal error on an innocent line:
$testDb->query("use `$testConfig[test_db]`");
So I try various things from the StackOverflow posts, including what is suggested in the error message itself, with no avail until I try (“just because why not”) replacing PDO::query() by PDO::exec(). They do the same thing, but differently enough so that the error goes away.
And at last, I can finally prevent PDO from emulating prepared statement, which makes it return data from integer fields as integer instead of string, and only because it uses one particular database driver !
I’am pretty confident that I am not the last to encounter such issue, so I hope this post will be able to save some productivity in the future…