Search code examples
phpsql-serverpdobigint

PDO MSSQL - wrong BIGINT value returned

I'm trying to query against a BIGINT primary key on a MSSQL database and some of the returned values turn up rounded, different than the ones stored in the database:

See screenshot here

9200000000000359 exists in the DB, as well as 9200000000000361. Querying against any of these two values will return 9200000000000360 (side note: this value does not exists in the database).

It seems that the values are first (wrongly) converted to binary and when converted back they result in different values than the original ones. But I'm using PHP 5.6 on a 64bit machine, not 32bit. Also this happens when using both dblib and odbc drivers.

Is there any setting I can make on the SQLServer or in the php.ini config to get the correct values?

Solution

  • It seems that Yii makes a conversion to float on the result. Even plain PHP 7 on a 64 bit machine returns the following output:

    php > echo (int)(float)9200000000000359;
9200000000000360

    Using plain PHP and PDO, the correct output is returned:

    $db = new PDO($dbc->connectionString, $dbc->username, $dbc->password);
$stmt = $db->query("...... WHERE id = 9200000000000359");
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)[0]['id']);

// output:
array(1) {
    [0] => string(16) "9200000000000359"
}