Doctrinebundle: MSSQL driver support `FormatDecimal` parameter

[Jeeppler]

By default the official Microsoft MSSQL driver omits leading zeros for decimal and money types. That means, MSSQL returns .00 instead of 0.00.

There is already an issue, which discusses this behaviour: https://github.com/Microsoft/msphpsql/issues/415 and more importantly there is already a preview release v5.5.0-preview which allows to change this behaviour.

Microsoft added several configuration parameters to the MSSQL driver, which allow to influence the behaviour on per query and a general connection level.

On connection level the behaviour can be changed by setting FormatDecimal to true. I tried to set the value in a Symfony 3 application using a connection string:

doctrine:
    dbal:
      url: "sqlsrv://user:password@host:port/database_name?FormatDecimals=true"

and by using the option section:

doctrine:
    dbal:
        driver: 'sqlsrv'
        host: "%database.host%"
        dbname: "%database.name%"
        user: "%database.user%"
        password: "%database.password%"
        port: "%database.port%"
        options:
          FormatDecimals: true

However, non of the options worked. Either, I did a mistake or the FormatDecimals option has to be added to the DoctrineBundle and maybe even to Doctrine ORM in general.

[kimhemsoe]

Looks like it need to be added to the drivers.

Try to take a look at DoctrineDBALDriverSQLSrvDriver and DoctrineDBALDriverPDOSqlsrvDriver

[morozov]

@Jeeppler which version of DBAL are you using? It should be possible to pass arbitrary driver options to sqlsrv and pdo_sqlsrv connections as of v2.8.0 (https://github.com/doctrine/dbal/pull/3033). Not sure if the bundle supports it though.

[Jeeppler]

@morozov according to my composer.lock file I am using DBAL v2.9.0:

~
"name": "doctrine/dbal",
"version": "v2.9.0",
"source": {
"type": "git",
"url": "https://github.com/doctrine/dbal.git",
"reference": "21fdabe2fc01e004e1966f200d900554876bc63c"
},
~

According to the DoctrineBundle documentation, the bundle supports an options section, but I do not how to use it.

Here is the same configuration option in the DoctrineBundle: https://github.com/doctrine/DoctrineBundle/blob/master/DependencyInjection/Configuration.php#L151

If I try to use it like this, it does not work:

~
dbal:
connections:
default:
driver: 'sqlsrv'
host: "%database.host%"
dbname: "%database.name%"
user: "%database.user%"
password: "%database.password%"
port: "%database.port%"
options:
FormatDecimals: true
~

I am not sure if the options parameter is even used while creating the connection. It does not throw an error, but it does not work either.

Maybe, I am not understanding the purpose of the options section.

[Jeeppler]

@kimhemsoe is there anything I can do to help?

[Jeeppler]

Any update on this issue? I would really like to see this issue addressed.

[stof]

The PDO option is PDO::SQLSRV_ATTR_FORMAT_DECIMALS according to the doc you linked. A this constant is probably an integer as value, and not the FormatDecimals string.

[Jeeppler]

@stof thanks for the response. However, I still do not understand how to configure the PDO::SQLSRV_ATTR_FORMAT_DECIMALS in the DoctrineBundle. I am more than happy to give any suggestion a try.

[Jeeppler]

Microsoft released driver version 5.6 to production: Production Release for the PHP drivers Version 5.6.0 for SQL Server. The new driver supports the decimal parameter.

[alcaeus]

Sorry for the delay here. Options need to use the corresponding PDO constant as key. In your case, the value of the constant is 1007, thus your config should look like this:

    dbal:
      connections:
        default:
          driver: 'sqlsrv'
          host: "%database.host%"
          dbname: "%database.name%"
          user: "%database.user%"
          password: "%database.password%"
          port: "%database.port%"
          options:
            1007: true

Please let me know if that works for you.

[bert126]

I am fighting with the same issue and neither 1007 nor FormatDecimals seems to work.

I am already using SqlSrv 5.6 and DBAL 2.9.x

Is ‘options’ the correct name for passing this parameter? I thought it was ‘driverOptions’ as described in the documentation here :

https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/configuration.html

Thank you for your help.

[Jeeppler]

@bert126 have you tried using driverOptions instead of options?

[alcaeus]

@Jeeppler did it work for you using driverOptions or options?

[Jeeppler]

@alcaeus I never tried any solution. I am unable to tell you if it works or not.

At the end, the app I was working on did not use Doctrine for the parts where the DecimalFormatting is needed.

[bert126]

I just tested it right now and I get the solution.

It's working perfectly with driverOptions (but not with options). And you should use the constant 1007 (and not FormatDecimals).

I can now have decimal fields declared under Ms-Sql Server, and I declare them properly as decimal in Doctrine annotations.

This piece of code is then working (PHP).

        $connectionOptions = array(
            'driver' => 'pdo_sqlsrv',
            'user' => 'xxx',
            'password' => 'xxx',
            'host' => 'xxx',
            'dbname' => 'xxx',
            'driverOptions' => array(
                '1007'=>true
            )
        );

[alcaeus]

Thanks for confirming, @bert126! Closing as solved.

[Jeeppler]

I tried it today with a yaml configuration. The following code works:

# Doctrine Configuration
doctrine:
    dbal:
        driver: 'pdo_sqlsrv'
        host: "%database.host%"
        dbname: "%database.name%"
        user: "%database.user%"
        password: "%database.password%"
        port: "%database.port%"
        options:
          1007: true  # Format decimal values with leading zeros

Different to @bert126, I had to use options instead of driverOptions. The first time I tried driverOptions as a result I received the following error:

Unrecognized option "driverOptions" under "doctrine.dbal.connections.default" 

[alcaeus]

Silly me - this is always options in the bundle; driverOptions is for DBAL only IIRC. Thanks for confirming.