Framework: Integers are being casted as string in the collection when using SQL Server

[malhayek2014]

Hello -

I found an issue when I change my database from MySQL to SQL server. The problem occurs when I use eloquent to get data from a model. The problem is that all the attributes are casted to string (or probably not casted correctly).

To help you see the issue, I produced the same step on both MySQL and SQL Server.

Here is my code
$surveyPools = QuestionPool::where('status', 'Active')
->where('survey_id', $this->interview->survey_id)
->get()
->keyBy('id');

$pools = $surveyPools->pluck('id');

$questionToPoolRelation = QuestionToPool::whereIn('pool_id', $pools)->get();

Now, on MySQL the following code works like expected with no issues

$questionToPoolRelation->where('question_id', $question->id);

However, it fails on SQL Server because the data set returned by get() treats integers as string.

To work around the problem I had to change my code to this
$questionToPoolRelation->whereLoose('question_id', $question->id);

To give you visual of the problem have a look at the following 2 images

This is what I get with MySQL Server

This is what I get with MS SQL Server

To help you validate the schema here is a copied from my migration script

    Schema::create('survey_questions_to_pools', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('question_id')->unsigned()->index();
        $table->integer('pool_id')->unsigned()->index();
        $table->enum('status',['Active','Inactive'])->default('Active');
        $table->nullableTimestamps();
    });

[JoostK]

This is probably an issue with the PDO driver, which in the case of MySQL (using the mysqlnd driver) returns data as defined by the database schema, whereas the SQL Server driver seemingly returns strings in all cases.

Perhaps you can try some PDO settings to change this behaviour, check what flags may be set, although the link is for MySQL (I do not know how it would work with SQL Server).

[malhayek2014]

@JoostK thank you so much for this info.
Here are the options that are available for the SQL Server driver http://php.net/manual/en/ref.pdo-sqlsrv.php. any idea on which one that that would solve this problem?

[JoostK]

None listed there I guess, after taking only a quick glance. Did you try setting PDO::ATTR_EMULATE_PREPARES and PDO::ATTR_STRINGIFY_FETCHES as my earlier link suggests?

I have to add that I have no idea if there is any support at all for this behaviour in PDO sqlserv, so perhaps you won't get this to work using whatever attributes.

[malhayek2014]

@JoostK
I added this
PDO::ATTR_STRINGIFY_FETCHES => false,

but when adding this to the option I get an error
PDO::ATTR_EMULATE_PREPARES => false,

[PDOException]
SQLSTATE[IMSSP]: The given attribute is only supported on the PDOStatement object.

[progmars]

I stumbled upon the same issue using PostgreSQL. But for me the difference indeed seems to be coming from PDO or PHP itself because I'm using the same version of Laravel and PostgreSQL for the same web app and database on my local machine and server, but still ID fields are being read as integers on the server and as strings on local machine. I'm not sure what could we do to make it consistent and whether Laravel could do anything at all to help with the issue.

[spiderweb-solutions]

We are seeing the same issue but using MySQL on different servers. MySQL 5.5.46 and 5.7.10 on two Debian boxes, both running PHP 5.6.14-0+deb8u1 are both displaying ints as strings.

PHP 5.6.17 on a CentOS box running MySQL 5.5.44 is not displaying this issue.

Edit: Have found source of the issue above, key was the PDO. This issue only occured since upgrade from 5.1 to 5.2 by the way as we rolled back through git and found the issue went away. 5.2 must have an issue with libmysqlclient and perhaps the library for SQL Server too. apt-get install php5-mysqlnd fixed the issue for us.

[unitedworx]

I have this issue on different environments while both using mysql.

[barroca]

maybe we can add a function like whereInIntegerArray or add a string_escape parameter = false on whereIn clause.

BTW, I share the same problem here, with the string cast the query takes 1.9s, and without, 0.1s

[barroca]

Just figure out that this issue is affecting Not just on whereIn() queries, but also on with() queries.

[adriandmitroca]

+1

Happened to me with MySQL on different environments.

[spencerdeinum]

For the people using Linux you need to check to see if you are using the mysql driver or the mysqlnd driver as already stated in this thread.

The mysqlnd driver casts between the sql datatypes and php types in the driver itself.

[restoreddev]

Going back to the issue with SQL Server, I'm running Laravel 5.3 and any integers in the database are being returned as strings for my Eloquent models. It doesn't do it with the same table structure on MySQL. Let me know if you need to see any code for verification.

[restoreddev]

Nevermind, looks like Microsoft fixed this issue https://github.com/Microsoft/msphpsql. Loading the latest version of the SQL Server PDO driver allows you to add a PDO attribute for fetching integers as integers. Here is an SO thread about it as well: http://stackoverflow.com/questions/31563747/laravel-pdo-settings.

Looks like this issue can be closed.

[MichalKrakow]

Hi,
just run into it in mid 2018 with newest version of PDO drivers (sqlsrv) so i put my solution:
in your config/database.php add the following options:

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

Be sure to restart artisan:serve if in local dev,
and you're good to go.
cheers.

sources:
https://github.com/Microsoft/msphpsql/wiki/Recommendations-for-improving-the-performance-of-PDO_SQLSRV-and-SQLSRV

http://fideloper.com/laravel-pdo-connection-options