Problem: Regional Settings and decimal separator

Fischer · Post by **Fischer** » Thu 03 Apr 2008 12:01

Problem:
Numeric data stored in a MSSQL database is displayed incorrect on the client.

Description:
We use a database installed on a MS SQL-Server 2005 SP2 with collation sequence SQL_LATIN1_GENERAL_CP850_CI_AI.

The problem can be reproduced with the following example:

CREATE TABLE TestTable
TestID int IDENTITY (1, 1) NOT NULL ,
DesiredValue PROZENT NOT NULL

INSERT TestTable VALUES (50)

The server and the client are using different regional settings which is a requirement from our client.

Server:
English (USA)

Client:
English (USA)
Decimal Separator: , (comma)
Thousand Separator: . (point)

While the data is stored as 50 in the database it is displayed as
as 500000000 on the client.

After some research we tracked down the error to be part of the dbxsda driver. We currently use version 3.10.6.0 of dbexpsda.dll but the problem also occurs with the latest version of the driver. The problem dont appear if we use the dbExpress driver shipped with Delphi 7 but we cant use this driver because it contains some other bugs.

Antaeus · Post by **Antaeus** » Fri 04 Apr 2008 12:28

Thank you for information. Unfortunately I could not reproduce the problem.
Please send me a complete small sample at dbxsda*crlab*com to demonstrate it, including script to create the database and objects in it.

Fischer · Post by **Fischer** » Fri 04 Apr 2008 17:52

I have send you a mail with a little testprogram to reproduce the problem.

Fischer · Post by **Fischer** » Mon 07 Apr 2008 10:51

Some further information we gained from debugging the driver sourcecode:

It seems that the error only appears with English (USA), any other language setting works fine.

The error can also be reproduced on a single computer if the language is set to English (USA) and the decimal and thousand separator are overwritten.

If the wrong data converison leads to a problem with the data size (to many digits in front of the decimal separator) the call to

TOLEDBRecordSet.Fetch
...
IRowset.GetData
...

is returning DB_S_ERRORSOCCURED.

This is raising a EOLEDBError which is handeled but not displayed.

Antaeus · Post by **Antaeus** » Mon 07 Apr 2008 14:53

I have answered you by e-mail.

Fischer · Post by **Fischer** » Mon 21 Apr 2008 13:32

Thank you for your help.

It indeed is a problem with the OLEDB provider which only appears if the data is transfered as a float value and the regional settings are English (USA) with comma es decimal separator.

By changing the application to use TFMTBCDFields and setting EnableBCD=True in the dbxconnections.ini the problem could be avoided.

This is because the field values are transported as strings and converted to BCD fields by the driver.