Msphpsql: Money, Decimal datatypes

[genyslt]

Hello,

Issue

I have several fields on sql table:

CREATE TABLE MyBalance
(
BalanceId INT IDENTITY PRIMARY KEY,
Total MONEY,
Percents DECIMAL(5,2)
)

go

INSERT INTO MyBalance (Total, Percents) VALUES (100, 20)

go

PHP Code:

$query = 'SELECT Total, Percents FROM MyBalance';
$stmt = sqlsrv_query($conn, $query);

$res_array = array();
    while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC ) ) {
        $res_array[] = $row;
    }
    sqlsrv_free_stmt($stmt);
print_r($res_array);
die();

The result on $res_array have some issue:

1. The money fields returns 4 digits after point:

100.0000

1. The decimal fields don't returns 0 at the start:

.00

Question

1. The decimal field must return with 0 at start: 0.00
2. The money field must return two digits after point (IF modulus operand == 0)

Try it select on sql server management studio:

SELECT *
FROM MyBalance

At the management studio the result is good.

The result i see on the manager and on $res_array must be same.

System info

PHP version: 7.1.5
Linux version: Ubuntu 16.04.2 LTS
sqlsrv_client_info:
[DriverName] => libmsodbcsql-13.1.so.8.0
[DriverODBCVer] => 03.52
[DriverVer] => 13.01.0008
[ExtensionVer] => 4.0.8.0
sqlsrv_server_info:
[SQLServerVersion] => 12.00.2000

[yukiwongky]

@genyslt ,

The answer to both questions is that PHP does not support decimal type. All numbers that are not integers are represented as float in PHP. To preserve the precision of the decimal or money types, the values our driver return is a stringified number of exact precision that is specified when you create your table (money has a scale of 4, and your decimal type has a scale of 2).

[genyslt]

It's not normal way to get ".00" number. The prices on the market also going scale of 2.

On http://php.net/manual/en/book.mssql.php driver - every works fine. I trying to migrate from php 5.6 and mssql driver to php 7.1.5 and sqlsrv driver - and get some missunderstandings.

Can it be a configuration setting?

Full internet writing about this problem, every programmer repairs results to show to the user , so i think this is very actual problem and driver must solve this problem.

P.S. I see SQLSRV_ATTR_FETCHES_NUMERIC_TYPE configuration on PDO::SQLSRV, on SQLSRV this configuration option didn't work.

[yukiwongky]

@genyslt,

From here you can see that the money data type in MS SQL Server has a scale of 4. I understanding a lot of people have problem with numbers being stored as strings in PHP, but since PHP does not support decimal type, our best approach to preserve the precision of a decimal number is by returning them as strings of exact precisions. If we truncate it to two decimal places to make it look "nice", then the actual precision of money would be gone.

As for SQLSRV_ATTR_FETCHES_NUMERIC_TYPE in the PDO_SQLSRV driver, it only works for integer types (i.e., whole number) and float types (inexact numbers), but it doesn't work for decimal types. This is again due to the need of preserving the precision of decimal numbers.

If you really wish to make your number look as your desire, you can try to use the PHP function floatval(). But be careful if you do a lot of arithmetic operations on inexact numbers (especially when working with money), it could lead to a loss in precision.

Thanks.

[genyslt]

It's developer problems since PHP does not support decimal type?

I think you are driver developer, and you must solve problems to WW (World wide) developers, that developers would be easier and easier to deal with such situations.

You "understanding a lot of people have problem with numbers being stored as strings in PHP" but not doing anything to help peoples save time..

Let's count:

1000 peoples use your driver.
1000 peoples looking solutions (hours of time)
1000 peoples writing lines of codes

How you think time you waste of these peoples?

My dirty solution to workaround this by every query, each row (EVERY QUERY!!! EACH ROW!!!):

function mssql_row_repair_decimal($row, $meta, $assoc = false){
    if ($assoc == true){
        foreach ($meta as $key => $value){
            switch($value[Type]){
                case 4: //int
                    break;
                case 6: //float
                    break;
                case 3: //money
                    if ($value[Precision] == 19){ //money
                        $row[$value[Name]] = number_format($row[$value[Name]], 2, '.', ' ');
                    } 

                    if ($row[$value[Name]][0] == '.'){
                        $row[$value[Name]] = number_format($row[$value[Name]], 2, '.', ' ');
                    }
                    break;
                default:
                    continue;
            }
        }

        return $row;
    }
    foreach ($meta as $key => $value){
        switch($value[Type]){
            case 4: //int
                break;
            case 6: //float
                break;
            case 3: //money
                if ($value[Precision] == 19){ //money
                    $row[$key] = number_format($row[$key], 2, '.', ' ');
                } 

                if ($row[$key][0] == '.'){
                    $row[$key] = number_format($row[$key], 2, '.', ' ');
                }
                break;
            default:
                continue;
        }
    }

    return $row;
}

P.S. When i say You i mean Microsoft - nothink personally to You.

[yukiwongky]

It is our design decision preserve the four decimal places in money types, since again SQL Serve money types has a scale of 4. Although some users may wish to represent money as float or as a two decimal places stringified number, more users would rather preserve the precision of money type. I've explained the risk of not preserving the precision of decimal types here.

As for the leading 0 being dropped from '0.00' issue, this is the numeric literal syntax designed by the Microsoft ODBC driver (see here).

If you want to format your numbers as you want, your SQL command is wrong. You should format the result from SQL Server like this:
$stmt = sqlsrv_query( $conn, "SELECT FORMAT(Total, 'C'), FORMAT(Percents, 'N') FROM MyBalance" );

See here for more information on number formatting.

[genyslt]

Can you add configurable connection param on this?

[yitam]

Hello @genyslt
Since you can easily use FORMAT in t-sql to achieve various formatting, we will not implement a configurable connection param for this.
Closing this issue now.

[alexandre-tobia]

Same problem here, very annoying, need to use number_format() on each row containing ".00" results...

@yitam And what's about people don't have any control of the database ? i can't use FORMAT this easly.

[yitam]

@alexandre-tobia please clarify what you mean by having no control of the database or elaborate why you can't use FORMAT easily.

[lifzgreat]

An obvious scenario in defense of @alexandre-tobia: FORMAT was added in SQL Server 2012; not all projects/clients are using 2012+.

Dropping the leading 0 is an unfortunate breaking decision by the unixodbc driver development team. Anyone coming from mssql_* functions have been fine for over a decade as they relied on the FreeTDS driver that returned numeric/currency/etc data as expected. Now that PHP 7 has removed the Mssql extension, sqlsrv (sqlsrv_* functions) is an alternative but unfortunately relies on the unixodbc driver that has changed years of developer experience.

I know it's not this extension's issue specifically, but the driver upon which it's dependent causes a breaking change and forces developers to go through thousands of files and millions of lines of code built up over the years to add workarounds like number_format, CAST(), etc - such a shame.

[alexandre-tobia]

@yitam I'm external developper working for a big company. I essentialy use stored procedures for getting datas from their databases. I can't modifiy the stored procedure's queries, so use FORMAT is not a solution for my case.

i agree with @lifzgreat, need to use "number_format" in many places...

[yitam]

Hi @alexandre-tobia, please understand that not all users have the same preferences. While someone might want high accuracy with money data, others might prefer only two decimal places or even with the returned values rounded up/down.

For example, the user in #291 wanted decimals returned as numeric values instead of strings. We have valid reasons to preserve the precision / scale as mentioned by @v-kaywon earlier, and there exist methods like number_format() that do exactly what you want.

In your case, you might want to define some helper method(s) to take care of your formatting issues. Alternatively, you might want to follow the example in #291 to modify the drivers to suit your needs.

[lifzgreat]

Please understand that preferences aren't the point. The point is developers are now seeing a _changed default output_ after years of built up code. I think it all comes back to the unixodbc driver that the sqlsrv extension relies on. The Mssql extension used the FreeTDS driver and that provided the output we have become accustomed to and embedded in years of code. You must see the struggle. It's not new code as much as trying to fix decades of previously-working code.

Even worse, the unixodbc driver drops the leading 0 when a number is less than 1 and greater than 0. That's just a terrible breaking decision. Imagine if PHP said devs can no longer use echo() and we now need to use a new function called say(). Now imagine say() dropped the first letter of each string. Sure, there's sprintf() to format strings, but can you imagine the number of lines of code that would have to be changed?

[David-Engel]

Hi @lifzgreat , @alexandre-tobia ,

We get it. There is a large PHP code base written against the default behavior of the old mssql PHP driver. Moving to this driver becomes a lot more difficult when its default behavior is slightly different and affects the way data is presented to the application. Ideally, we would like to be able to provide a path that does not require such a high effort in migrating from the old driver.

I think the idea of PHP driver configuration options to modify the default behavior in these scenarios is worth considering. I'm going to tag this issue as a feature request for consideration in a future release.

Regards,
David

[yitam]

Hi @lifzgreat , @alexandre-tobia , @genyslt,

Just want to make sure I understand what you want to be configurable. To retain the zero to the left of the decimal dot and/or the number of decimals? Or do you want something similar to the built-in PHP function number_format()?

[lifzgreat]

Hi @yitam I had to read over this as it was from 6 months ago but retaining the leading 0 and having money display the standard 2 decimals is what we had expected at that time. Since then, we had to go through and rework existing code that dealt with those things. It seems to me most people would expect to see a leading 0 and 2 decimals for money, so it seems that'd be best as the default output. That said, if you provide the ability to configure default output, I think that's a step in the right direction.

[yitam]

Thanks for the reply @lifzgreat.

Yes we are looking into providing a configurable option (the default remains the same, however). In any case, the money / decimal output is still a string to keep precision / accuracy, with some rounding if necessary.

So leading zero and number of decimals are the desired options.

[lifzgreat]

Sounds great, thanks @yitam.

[mschop]

It's absolutely correct to return Currency etc. as strings. Tip for PHP-Developers: If you need to operate on such values, you should use libraries that provide abstraction on top of e.g. bcmath. See https://packagist.org/packages/litipk/php-bignumbers or https://packagist.org/?query=decimal

Side fact (please correct me, if I'm wrong): I'm building a json api. Json floats are unprecise, too. Additionally you don't know which language parses the json. JS and PHP would loose the precision even if JSON could handle it. So you should transfer Currency fields as strings in JSON, too.

[yitam]

Indeed, @mschop, so thank you. We are considering this feature request, and most likely we would only provide a configuration option to format the number of decimal digits (with leading zeroes if necessary) for decimals / numeric data types during fetching, and rounding is sometimes inevitable. The data itself will not be altered however.

[Jeeppler]

• Are we able to set the number format for the entire OS/application?
• How can we set the number format setting (e.g. php.ini, sqlsrv.ini etc.)?
• Are you (Microsoft) working together with Doctrine the PHP Object-relational mapping (ORM) framework?

[yitam]

@Jeeppler

I'm in the process of documenting how to use the new configuration options to format decimal / numeric numbers. Note that it is a statement level option and will not affect the default cases.

I'm not sure I understand your last question, but we do have pdo_sqlsrv, which conforms to PHP PDO standard.

[Jeeppler]

@yitam Do I understand this correct we will have to specify the format decimal/numeric for every statement? That would not be very helpful. I was hopping to be able to have a setting which allows me do set a format for the entire PHP installation (purpose compatibility with old mssql driver).

The goal of an Object-Relational Mapping (ORM) is to make the database queries agnostic to the underling SQL implementation/vendor (e.g. MySQL, PostgreSQL, MS SQL etc.). Most web application frameworks use an ORM. In case of PHP the more or less standard is Doctrine, which is bundled with Symfony. Doctrine advices users to not use the pdo_sqlsrv driver:

pdo_sqlsrv: A Microsoft SQL Server driver that uses pdo\_sqlsrv PDO Note that this driver caused problems in our tests. Prefer the sqlsrv driver if possible.

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

Based on your response: I assume, Microsoft is not working together with the Doctrine project/community. Is my assumption correct?

[yitam]

hi @Jeeppler

I re-read your issue #881 and figure that you're more concerned with leading zeroes, not the number of decimal digits, correct?

Yes, we can provide the configurable option in php.ini for both sqlsrv and pdo_sqlsrv.

About the quote re pdo_sqlsrv from Doctrine, it might be related to a bug that I fixed earlier (check this comment and the subsequent ones).

[David-Engel]

@Jeeppler Microsoft definitely supports the Doctrine project. I've asked over in their community what the details are behind that doc note. Hopefully we can resolve whatever issue it was based on. Thanks for bringing it to our attention.

[Jeeppler]

@yitam yes, I am concerned with the behavior of leading zeros. I would like to have 0.00 instead the current behavior of omitting the leading zeros .00.

It would be awesome to have the ability to configure this option in the php.ini.

@David-Engel it is good to read that Microsoft supports the Doctrine project and therefore indirectly the Symfony framework. Please continue working together with the PHP ecosystem. This makes it easier for developers to use SQL Server as part of the application stack.

[mschop]

I asked the doctrine team about two month ago regarding that part in the documentation:

pdo_sqlsrv: A Microsoft SQL Server driver that uses pdo\_sqlsrv PDO Note that this driver caused problems in our tests. Prefer the sqlsrv driver if possible.

The answer was the following:

Michael Moravec
@Majkl578
Sep. 21 17:17
@mschop pdo_sqlsrv should be working just fine, @morozov confirmed he doesn't know about any > test issues with that driver
(pdo_oci is different story)

@yitam Please do not add another ini setting. Just make it a parameter when creating the connection in PHP-Code. We do not need another inconsistency in PHP by having another .ini-setting.

[Jeeppler]

@mschop

Just make it a parameter when creating the connection in PHP-Code.

That would work for me as well and is probably easier to integrate with the Doctrine driver/db settings.

[yitam]

Thanks @mschop for providing more clarifications about that message in the Doctrine documentation.

@Jeeppler in this case, it will be a parameter in both pdo_sqlsrv and sqlsrv rather than relying on php.ini
Just want to make sure, do you want leading zeroes to numeric strings, including regular floats or decimal / money types?

[yitam]

Hi @lifzgreat , @alexandre-tobia , @genyslt,

We are working on this feature request. With second thoughts, we would restrict the formatting option to money/smallmoney types only, rather than all decimal / numeric types. Since the values might be rounded up, they are not accurate enough as inputs to any kind of calculations.

We are still open to feedback and/or suggestions, so please let us know.

[Jeeppler]

@yitam

Just want to make sure, do you want leading zeroes to numeric strings, including regular floats or decimal / money types?

Can you elaborate a little bit more? I did not understand the question.

[yitam]

Hello @Jeeppler , the original problem in this issue is about money and decimal / numeric types because they are always returned as strings.

However, for other numeric types like floats or real, they can be returned as numbers, not strings.

My question for you is whether you want leading zeros applied to all number strings, whether they are of float types or decimal types. I hope I have made myself clear.

[Jeeppler]

@yitam sorry for the confusion, I have this problem:

this issue is about money and decimal / numeric types because they are always returned as strings.

Float/double types are handled well by both PHP and JavaScript as well as PHP's json_encode function.

Here is an example:

$ php -a
Interactive mode enabled

php > $value = .00;
php > var_dump($value);
float(0)
php > $json_serialized = json_encode( $value );
php > var_dump($json_serialized);
string(1) "0"

Here is a JavaScript example:

parseFloat(".00");
0
parseInt(".00");
NaN

[yitam]

Yes, it's a design decision to return money, decimal or numeric types as strings to preserve accuracy. See the discussions above, for your interest.

In this case, @Jeeppler , you're also mainly concerned with leading zeros with money, decimal or numeric types, just like the others in this issue. Please correct me if I have misunderstood you.

[Jeeppler]

@yitam yes, exactly. I am concerned with the leading zeros with money, decimal or numeric types.

[Jeeppler]

@yitam

Will pull request #886 be available in the next preview version for 5.4?

From a users perspective, how do I configure the number of leading zeros? How do I configure the number of decimal places?

[yitam]

Yes @Jeeppler we plan to incorporate the pull request in the next preview. I'm also writing instructions on how to use the new configurable options. Please note that we have recently decided to limit configuring number of decimal places to only money / smallmoney fields.

[Jeeppler]

@yitam so, will this issue be addressed #881 with pull request: #886? Will I be able to specify the number of leading zeros for decimal types?

[yitam]

I'm not sure I understand your question, @Jeeppler. But once this pull request is merged, if the decimal data is shown as .00 as default, when the new option is set to true, the data will be 0.00 instead. Did I answer your question?

[Jeeppler]

@yitam yes, that answers my question.

Do you have a release date for driver version 5.4?

[yitam]

@Jeeppler, the ETA is some time this December, after PHP 7.3.0 is officially released. Please stay tuned.

[Jeeppler]

@yitam PHP 7.3.0 is officially released. When can I expect the release of driver version 5.4? Is the documentation ready for the money/decimal changes?

[yitam]

@Jeeppler yes we are working on this, and please check our blog for announcement

[Jeeppler]

@yitam thanks for the link. Please let me know, by adding to this issue, the moment you have the documentation for the configuration settings available.

Or is there a way for me to try out the changes before the official release?

[yitam]

@Jeeppler you can try building our drivers from the current dev branch. In the meantime, you can also browse the documentation that is recently updated.

Just so you know, we will not make last minute changes at this point, but we welcome feedback because this is a preview release.

[Jeeppler]

@yitam thanks, I really appreciate the link to the updated documentation. However, I saw starting with: 5.5.0-preview, is this a mistake? I thought we will have the FormatDecimals in driver version 5.4.

[yitam]

@Jeeppler no 5.4.0 was already out awhile ago, and this upcoming preview is 5.5.0

[Jeeppler]

@yitam perfect. When will 5.5.0 preview or 5.5.0 be available? When will 5.5.0 be released? Will it be released in 2018 or do we have to wait until somewhere in 2019? And if it is 2019, what will be the approx. release date?

[yitam]

We can't give you an exact date @Jeeppler but it will be 2019. We are planning to add yet another feature before we can release. Please subscribe to the blog if you like.

[yitam]

@Jeeppler fyi, 5.5.0-preview is published, and we welcome feedback. I'm going to close your related issue.

[Jeeppler]

Thanks. Installing 5.5.0-preview was easy. However, I do not know how to configure Docrine ORM to set the new FormatDecimals to true in Symfony 3. I tried the the URL version:

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

and

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

the connections work, but the decimal issues still persist. I do not know if I set the FormatDecimals parameter right.

[yitam]

hi @Jeeppler

They may not support a preview release. To prove that the new feature works, please run a quick test by using a simple php script below:

<?php
$options = array("Database"=>$database, "UID"=>$uid, "PWD"=>$pwd, "FormatDecimals"=>true);
$conn = sqlsrv_connect($server, $options);
if (!$conn) {
  var_dump(sqlsrv_errors());
}

$stmt = sqlsrv_query($conn, "SELECT CONVERT(decimal(5,3), '0.0016')");
$results = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
var_dump($results);

?>

The output should be

terminal$ php mytest.php 
array(1) {
  [0]=>
  string(5) "0.002"
}

[Jeeppler]

@yitam thanks for the code snippet. I can confirm that it works. I got the same result as you.

One issue I had with your code is the naming of variables. I was not 100% what $uid or $pwd refers to. I think, by using more descriptive variable names like $username and $password the parameters will be self explanatory. I saw that the documentation has the same issue: https://github.com/Microsoft/msphpsql/wiki/Features#formatDecimals

I modified your code snippet to illustrate my point:

<?php

$database = "myDatabase";
$username = "myUserName";
$password = "myPassword";
$server = "myhost.example"; //IP address should work as well, I guess

$options = array("Database"=>$database, "UID"=>$username, "PWD"=>$password, "FormatDecimals"=>true);
$conn = sqlsrv_connect($server, $options);
if (!$conn) {
  var_dump(sqlsrv_errors());
}

$stmt = sqlsrv_query($conn, "SELECT CONVERT(decimal(5,3), '0.0016')");
$results = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
var_dump($results);

?>

The other parameters which is not explained is the how to set a different port. Good documentation is always appreciated.

[Jeeppler]

I do not know if Doctrine supports preview driver. However, at some point in time they will likely add the configuration to some of the next releases. However, to be able to do the code changes the preview driver are beneficial and they have to be aware of the changes.

I went ahead and created an issue in the DoctrineBundle: https://github.com/doctrine/DoctrineBundle/issues/893

[yitam]

Good documentation is always appreciated.

Yes, I agree. Thanks for the feedback and the documentation already modified, @Jeeppler

[Jeeppler]

Driver version 5.6 is released officially: Production Release for the PHP drivers Version 5.6.0 for SQL Server. This release implements the decimal feature discussed here.

[yitam]

Thanks @Jeeppler
We are going to close this issue. Please feel free to reopen if you have any related problem.