Customize database session language

Hello all, my team and I work in a financial group where Sql Srv aren't configurable on dev side. We are given with connection credentials (mostly DB owners) and a default session language set to fr_french.

This setting involve updating Eloquent models for dates format (writting as Y-m-d and reading as d-m-Y). It's a pain.....

Can someone help me with configuration array to set session lang as us_english. A can't find any documentation about customizing connections array, especially on sqlsrv.

Any help would be appreciated :)

Braunson

10 months ago

Level 18

You can use the InitialCommand key to set the language.. In your config/database.php file, add the options array to your sqlsrv connection:

'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' => '',
    'prefix_indexes' => true,
    'options' => [
        'CommandTimeout' => 60,
        'ConnectionPooling' => true,
        'LoginTimeout' => 60,
        'InitialCommand' => 'SET LANGUAGE us_english' // We set the language here..
    ]
],

Alternatively if your using the PDO options approach:

'sqlsrv' => [
    // .. your other config
    'options' => [
        PDO::SQLSRV_ATTR_QUERY_TIMEOUT => 60,
        PDO::ATTR_TIMEOUT => 60,
        PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8,
        'InitialCommand' => 'SET LANGUAGE us_english; SET DATEFORMAT ymd;'
    ]
],

AUGUSTINNoha

9 months ago

Level 11

Lot of thanks @Braunson unfortunately it doesn't seem to work. Looking at the SQL connection class from laravel, I can't find where the "options" key is sent to server. As a consequence, I dont think this param is passed at all. But anyway thant you really much for your reply... I will let you know if I find any working solution.

Please or to participate in this conversation.