Page 1 of 1

Numerics converted to a string with PHP and PDO to MYSQL

Posted: Tue Feb 09, 2016 4:52 am
by onecloudprop
Hi

Iam using PHP (version 5.6) to act as the middle tier between a MySQL database (5.5.40) and returning the results in JSON format.

We are using the PHP extension (PDO) to do the database interaction, but have noticed that all numeric values get returned as a string. The database itself is running on a Linux installation, but during development, both the client and middle tier are running on Windows.

I set the connection parameter "ATTR_STRINGIFY_FETCHES" to false but that made no difference. e.g. $dbConnection->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

This is the PHP function that I am using to retrieve data and it is tied to the RESTful interface.

Code: Select all

function getAllObjects() 
{
    $sql = "SELECT * FROM Tbl_Objects ORDER BY 1";
    try {
	$db = getDB();
	$stmt = $db->query($sql);  
	$objects = $stmt->fetchAll(PDO::FETCH_OBJ);
	$db = null;
	echo json_encode($objects);
    } catch(PDOException $e) {
    echo '{"error":{"text":'. $e->getMessage() .'}}'; 
    }
}
I also tried "mysqli" but I get the same results in that the numeric values are all converted to Strings. If I use the "cast_query_results($rs)" function on the net, I do get numeric converted correctly, but it seems to me that this is rather a fudge, rather than a solution.

Any ideas on how to get this to work natively please?

Re: Numerics converted to a string with PHP and PDO to MYSQL

Posted: Tue Feb 09, 2016 7:34 am
by Celauran
Have you tried turning off ATTR_EMULATE_PREPARES?

Re: Numerics converted to a string with PHP and PDO to MYSQL

Posted: Tue Feb 09, 2016 7:46 am
by onecloudprop
Nope that's a new one on me and seems to work. Any downside in terms of performance?

Re: Numerics converted to a string with PHP and PDO to MYSQL

Posted: Tue Feb 09, 2016 7:50 am
by Celauran
I haven't actually measured it, but it's certainly going to be faster to have the compiled C code do the conversion (if there even is one to be done) than to do it in userland.

Re: Numerics converted to a string with PHP and PDO to MYSQL

Posted: Tue Feb 09, 2016 11:34 am
by onecloudprop
Thank-you for turning that one up. Very much appreciated.

Re: Numerics converted to a string with PHP and PDO to MYSQL

Posted: Tue Feb 09, 2016 7:26 pm
by Weirdan
onecloudprop wrote:Nope that's a new one on me and seems to work. Any downside in terms of performance?
There's additional roundtrip to server when emulate_prepares is off. Basically it sends the query (without parameters) to server first, obtains statement parsing result (either success or fail) and if prepare was successful client library proceeds with sending statement arguments. Whereas when emulate_prepares is on, arguments are interpolated into the query string on the client side and then entire query is sent to db server.

Either flag value shouldn't be a problem unless you have very high traffic or high ping times from webserver to db server.

Re: Numerics converted to a string with PHP and PDO to MYSQL

Posted: Wed Feb 10, 2016 5:11 am
by onecloudprop
Ok, well if it resolves the issue for now that would be good. Many thanks.