CODEWITHSUNDEEP
phpsql-serverlaravel
derdida
derdida

Reputation: 14904

Laravel PDO Settings?

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?

Upvotes: 12

Views: 23423

Answers (3)

Steve Bauman
Steve Bauman

Reputation: 8688

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

Upvotes: 0

Code Wookiee
Code Wookiee

Reputation: 211

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.

Upvotes: 21

Wader
Wader

Reputation: 9868

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

Upvotes: 4

Related Questions