12

I would like to return INT as integers from my database. At the moment all values are loaded as Strings. Database is MSSQL 2012 and I use the PDO driver (for v5.6).

Trying to set the properties here (as shown here on fideloper.com, but I don't know if that's still possible):

   'sqlsrv' => [
        'driver'   => 'sqlsrv',
        'charset'  => 'utf8',
        'prefix'   => '',
         ......
        'options'   => array(
            PDO::ATTR_STRINGIFY_FETCHES => false,
            PDO::ATTR_EMULATE_PREPARES => false,
        ),
    ],

But always getting an error:

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

How can I set any settings for the PDO Driver to return INT as Integers and not as Strings.

This is still not working:

 $pdo = DB::connection()->getPdo();
 $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
 .. do ORM Query

Bringing the same error.

Maybe anyone can help me?

SaschaM78
  • 4,376
  • 4
  • 33
  • 42
derdida
  • 14,784
  • 16
  • 90
  • 139
  • Does it work when you don't set the attributes? You may consider opening up an issue on the git repo – Chibueze Opata Jul 22 '15 at 13:49
  • Everything works fine, ill only get ALL numeric values as strings back from laravel ORM. Ill use this as a RESTful Webservice, and its stupid to convert each value from IOs to the correct data-type ;) - so it would be great if i can get the values as numeric. – derdida Jul 22 '15 at 13:51
  • Another thing to consider is turning of the `EMULATE_PREPARES`. – Chibueze Opata Jul 22 '15 at 14:01

3 Answers3

21

I know this is an old thread, but I found a global solution to this issue for MSSQL drivers and PHP 7 (single change that affects all tables / models). Hopefully this will help others that are struggling with the same.

  1. Get the latest version of the drivers from the Git Repository (Microsoft/msphpsql). The current version released on the Microsoft Downloads page is an older version and won't work (as of 9/13/2016).
  2. Copy the appropriate DLLs into your php/ext folder (replacing/deleting the older version). You'll probably need to stop/start your web server (definitely, if IIS) to free the original files up for replacement/deletion. If the filenames changed from the previous version you had installed, update your php.ini file.

  3. Update the driver configuration to include the new PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE parameter:

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

For explanation of the solution, I found it by working my way through the source code on the Git Repository. Of course it would have been a lot easier if I had read the Announcements section of the README file first:

July 28, 2016 (4.1.0): Thanks to the community's input, this release expands drivers functionalities and also includes some bug fixes:

  • SQLSRV_ATTR_FETCHES_NUMERIC_TYPE connection attribute flag is added to PDO_SQLSRV driver to handle numeric fetches from columns with numeric Sql types (only bit, integer, smallint, tinyint, float and real). This flag can be turned on by setting its value in PDO::setAttribute to true, For example, $conn->setAttribute(PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE,true); If SQLSRV_ATTR_FETCHES_NUMERIC_TYPE is set to true the results from an integer column will be represented as an int, likewise, Sql types float and real will be represented as float. Note for exceptions:
    • When connection option flag ATTR_STRINGIFY_FETCHES is on, even when SQLSRV_ATTR_FETCHES_NUMERIC_TYPE is on, the return value will still be string.
    • When the returned PDO type in bind column is PDO_PARAM_INT, the return value from a integer column will be int even if SQLSRV_ATTR_FETCHES_NUMERIC_TYPE is off.
Code Wookiee
  • 211
  • 2
  • 6
4

I believe this issue is related to the PDO driver used (thats installed with PHP, not laravel configuration).

Not quite what you're looking for but could potentially solve your problems. Since laravel 5 theres been a casts feature on eloquent where your columns are automatically cast to your pre-defined types. See http://laravel.com/docs/5.0/eloquent#attribute-casting

// Eloquent Model
protected $casts = [
    'int_column'   => 'int',
];

Your int_column would then automatically be cast to an int when the model is retrieved from the database

Wader
  • 9,427
  • 1
  • 34
  • 38
  • Thanks - if ill cast it manually it works fine - but sometimes ill need to join from different tables, so i am unable to cast every value (or?) - because its not stored in my model. I will check for the default PDO configuration too - but this is definitely a good workaround. – derdida Jul 22 '15 at 13:56
  • We also found this issue, it seems the MSSQL PDO driver returns all data as strings. We are using ZF and add a custom statement class using PDOStatement::getColumnMeta to automatically cast the data to the correct type. Maybe Lavalel has similar functionality. – bstoney Sep 13 '16 at 22:02
  • @Wader Maybe you cn help me. Look at this : https://stackoverflow.com/questions/51838922/how-can-i-convert-many-statement-mysql-to-laravel-eloquent – moses toh Aug 15 '18 at 02:37
0

If anyone is here looking on how to get the SQLSRV PDO driver to return decimal or money values as floats in PHP - unfortunately this isn't possible:

Formatting Decimal Strings and Money Values (PDO_SQLSRV Driver)

To preserve accuracy, decimal or numeric types are always fetched as strings with exact precisions and scales. If any value is less than 1, the leading zero is missing. It is the same with money and smallmoney fields as they are decimal fields with a fixed scale equal to 4.

https://learn.microsoft.com/en-us/sql/connect/php/formatting-decimals-pdo-sqlsrv-driver?view=sql-server-2017

Steve Bauman
  • 8,165
  • 7
  • 40
  • 56