SQLSRV_ATTR_FETCHES_NUMERIC_TYPE flag does not work

[kinagiyuki]

PHP Driver version or file name

5.9.0

SQL Server version

2017

Client operating system

Microsoft Windows 10

PHP version

7.4.20

Laravel version

5.8

Microsoft ODBC Driver version

2017.177.02.01

Issue

I have searched a lot in the internet as my PDO returns string when the column field type is integer. I have set

PDO::ATTR_STRINGIFY_FETCHES to FALSE PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE to TRUE

I have also checked those flags were set as intended in run time via debugger. But with no luck, the PDO is still returning string but not integer on any integer type column.

Is it a bug?

[yitam]

@kinagiyuki please provide a php script and your table schema that reproduce your issue.

[kinagiyuki]

@yitam

My table schema is something like below id: bigint (primary key) a_id: bigint (foreign key)

My database.php :

'sqlsrv' => [
    'driver' => 'sqlsrv',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '1433'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'options' => array(
        PDO::ATTR_STRINGIFY_FETCHES => false,
        PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true
    ),
    'strict' => false,
    ],

And I am using Laravel Eloquent Model to retrieve database data. It is supposed that I should get id: int and a_id: int. But I always get id: int and a_id: string. I have checked those two flag are set as they are in run time.

I have also tried to use $CAST in Laravel Eloquent Model class and it returned id (int) and a_id (int). But that is not what I want as PDO::ATTR_STRINGIFY_FETCHES and PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE are supposed to work.

[yitam]

Hi @kinagiyuki, are you sure both id and a_id are of type bigint?

By design, bigints are represented as strings because they can go out of range of integers, as explained in this comparison

Hence, as you can see in the documentation for PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE , bigint is not listed as a numeric SQL type.

[kinagiyuki]

@yitam Big thanks to you. Since I was using MySQL before and made a data migration to Microsoft SQL by SSMA. The SSMA "helped" me to convert unsigned int to bigint so the issue happened.