Unixodbc: Decimal Formatting Problem

Asked

Viewed 419 times

3

I built a server with Centos 7, SQL Server 2017, and Unixodbc

On tsql, the numbers are correct.

But when I see in isql, the decimals are separated by a comma (,) rather than a dot (.).

How can I fix this problem?

P.S.: What I really want is to use the number in PHP (you are complaining that the number is with formatting problem)

UPDATE: The error that appears in PHP is: Notice: A non well Formed Numeric value encountered in /usr/share/Nginx/html/loja3/info_vend/achapeca.php on line 789

I saw now that the error occurs when using php number_format

UPDATE 2:

I ran the var_dump and Windows returned: string(12) "3.5 million" No Centos: string(3) "3,5"

UPDATE 3:

It seems that the solution is to remove number_format. But I find it strange because it needs number_format in Windows and Centos not. Any additional verdict or tip?

php sql-server centos odbc
  • 1

    can show us an example ? where the commands are being executed ?

  • 1

    Note that SQL Server 2017 is not yet the final version. For now only CTP (Community Technology Preview) is available. It is for testing. See https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/sql-server-2017-ctp-2-1-now-available/

  • Yeah, I know the version is a preview. But as in sqlcmd and tsql is ok, I imagine the problem is some configuration that impacts odbc but does not impact sqlcmd, or tsql An example, in tsql, a number appears as 22.40 and in isql appears as 22.399999999999

  • 1

    @Claudioshigueowatanabe: How is declared in the database the column that displays value 22.40?

  • An example of such a field is decimal(15,2). From what I saw, the fields that are giving problem are all decimal

  • 1

    Make a var_dump just to see and please is very important that puts the complete error message that occurs in PHP here to see. Just for the record, you see the comma in the isql, but it doesn’t mean it’s real comma it’s just a way for the isql to make it easier to read, in the bank this normal.

  • The error that appears in PHP is: Notice: A non well Formed Numeric value encountered in /usr/share/Nginx/html/loja3/info_vend/achapeca.php on line 789

  • I saw now that the error occurs when using php number_format

Show 3 more comments

2 answers

3

It seems a matter of language definition (language) in the presentation. For example, in the English language the decimal separator is the point and in Portuguese is the comma. Evaluate the behavior of isql using the option -l (locale), defining the English language.

In ODBC.ini, check how LOCALE is configured. Also check the LC_NUMERIC configuration in Cent OS.

Regarding the value 22.40 appear as 23.99999..., this usually occurs with expressions of floating point; for example, when the column is declared as float in the database. Depending on how the value is rounded to be displayed, this difference may occur as described in the document Using decimal, float, and real Data: The IEEE 754 Specification provides four rounding modes: round to Nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are Accurate to the Guaranteed Precision but can result in Slightly Different floating-point values. Because the Binary representation of a floating-point number may use one of Many legal rounding schemes, it is Impossible to reliably quantify a floating-point value.

ENLARGING:
(1) In isql it is possible to define locale using the option -l. Details in isql man page.

(2) For information about the Microsoft ODBC driver for Centos (clone of RHEL): Installing the Microsoft ODBC Driver for SQL Server on Linux

(3) unixODBC installation instructions, to act as driver manager in conjunction with MS ODBC Driver: Installing the Driver Manager

(4) Locale definition documentation in PHP: setlocale

  • Thank you, Jose, thank you! No /etc/odbc.ini tried: Locale = en_US But it didn’t help But by typing in the shell: export LANG=en_US.UTF-8 That worked. Any idea how you could make it work in php? ?

  • In php, with setlocale(LC_ALL, 'en_US') worked. The question is: is there any better way?

  • 1

    @Claudioshigueowatanabe: In PHP did it work by keeping original settings of ODBC.ini and Centos? // When configuring locale in PHP, did the value display correctly? That is, it was 22.40? // I can’t give an opinion about PHP, because it’s not my beach, but that’s where the presentation takes place. // If possible change only LC_NUMERIC.

  • Typing in shell: export LANG=en_US.UTF-8, and testing isql the value is 22.399999999999

  • LC_NUMERIC also works. Thank you!

  • 1

    @Claudioshigueowatanabe: The advantage of using LC_NUMERIC is that it changes only the part of thousand separator and decimal place. There is also LC_MONETARY, for currency. // If you can handle only the presentation (isql, PHP or what you use at the end), better. // I expanded the answer, with links to documentation.

  • Thank you, Jose! And sorry... My last update was wrong and missed speaking in the comment here. In Windows it is working with number_format (php function for numerical formatting) but in Centos it works by taking number_format. I know php is not your thing but you have some opinion of what might be the problem?

  • @Claudioshigueowatanabe: About "In Windows is working with number_format (php function for numerical formatting) but in Centos works by taking number_format", Just as in Centos the LC_NUMERIC option defines the treatment for mile separator and decimal place, in Windows there is similar option in "Regional Settings". Make sure both (on Centos and Windows) are set up the same way.

Show 3 more comments

1

@ClaudioShigueoWatanabe

I believe it is the same problem that had a system I developed, using an ODBC drive for SQL Server. At some point in the ODBC program it does not perform value rounding, I did not find an acceptable explanation for such fact, but I noticed that it had a relationship with the data format in the database.

Whenever I used the programs to check the base directly the value was correct, when I used the system with the drive ODBC had the problem, well I had to solve, so I rounded up the cases.

Another solution was in all fields that contained decimal values, masks were placed to display.

To thicken the broth, when performing the Insert through the application, the value persisted in the base correctly, but in the recovery of the same screen appeared with decimal places that did not have.

I hope you find a solution, with me only changing to ADO, but I did not continue in the project to know if this was actually solved.
I developed on Delphi 5 and 7 for MS Sqlserver 7 to 2012, ODBC drive just knew it Mssqlodbc.

  • Our Luiz... I hope your problem has nothing to do with yours... Yours seems to be worse than mine.

  • The most interesting thing is that it wasn’t all values, it was in random values. <br/> All calculations were performed correctly, only the field visual was impaired in the system, solved with masks and changing the component type to display the value.

Browser other questions tagged php sql-server centos odbc

You are not signed in. Login or sign up in order to post.