SQL Server returning strings for all values even with attributes set

Question

I am working on migrating a MySQL database over to SQL Server (MSSQL) and am having an issue where all values returned from the database are coming back as strings. I have been through the documentation and everything I can find on Google and haven't been able to resolve this so far.

My setup is using the latest PHP PDO driver with Laravel 6.18.35. My database.php looks like this:

'migration' => [
        'driver' => 'sqlsrv',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST'),
        'port' => env('DB_PORT'),
        'database' => env('DB_DATABASE'),
        'username' => env('DB_MIGRATION_USERNAME'),
        'password' => env('DB_MIGRATION_PASSWORD'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'modes' => [
            'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION'
        ],
        'engine' => null,
        'options' => [
            PDO::ATTR_STRINGIFY_FETCHES => false,
            PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true,
        ],
    ],


'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST'),
            'port' => env('DB_PORT'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'options' => [
                PDO::ATTR_STRINGIFY_FETCHES => false,
                PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true,
            ],
        ],

The migration runs fine but as soon as my application requires an integer it gets a string. I also have issue with binary UUIDs but I believe that is an encoding issue so will deal with that separately. I have tried changing the charset, collation etc. I have tried removing the options and setting them on the PDO object with:

    $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES,false);
    $pdo->setAttribute(PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE,true);

None of these changes seem to make any difference and I still get strings back. If I do a get on the attributes:

    echo $pdo->getAttribute(PDO::ATTR_STRINGIFY_FETCHES),
    echo $pdo->getAttribute(PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE)

I don't see a response for the ATTR_STRINGIFY_FETCHES but can see SQLSRV_ATTR_FETCHES_NUMERIC_TYPE is set true.

I would really appreciate any suggestions for other things to try/check etc! Thanks in advance!

SQL Server returning strings for all values even with attributes set

0 Answers