Bug #23266 Inability to execute statements with numeric parameters in a german locale
Submitted: 13 Oct 2006 16:35 Modified: 30 May 2013 7:07
Reporter: Dominic Smith Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.x OS:Any (all)
Assigned to: CPU Architecture:Any
Tags: locale

[13 Oct 2006 16:35] Dominic Smith
If I bind a statement with data of types SQL_C_DOUBLE, SQL_C_NUMERIC, etc. The connector seems to be using some manner of 'sprintf()' formatting to insert the data into the statement actually issued to the RDBMs. 

This has very unfortunate side-effects if the locale being operated in uses ',' as the decimal seperator. For example, we issue the following statement (this is with unixODBC 2.11, same issue applies with iODBC 3.x): 

UPDATE spktoko_t SET erloese_dm  =  ? , 
                     rueckst_erloese_dm  =  ? , 
                     einkauf_dm  =  ? , 
                     rueckst_einkauf_dm  =  ? , 
                     saldo_dm  =  ? , 
                     differenz  =  ? , 
                     abweichung_proz  =  ?  
  WHERE spktoko_t.fk_eakopf_posnr  =  ? 

The parameters in this case are bound as SQL_C_DOUBLE, which results in the following error: 

[MySQL][ODBC 3.51 Driver][mysqld-5.0.24a-standard-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00000000000000000e+00 , rueckst_erloese_dm  =  0,00000000000000000e+00 , einkauf' at line 1

Note the decimal ',' in the generated SQL is naturally being confused for an list seperator in the SQL statement. 

Equally problems exist with the SQL_C_NUMERIC datatype. 

How to repeat:
Create a program which executes a simple statement with bound SQL_C_DOUBLE/SQL_C_NUMERIC types (this shows up best in either insert/update statements). 

When running the program, set the process locale to de_DE, and you should find the similar errors are thrown, where the process executes fine in an en_GB based locale. 

This happens independently of the locale under which the RDBMs is operating (largely because the SQL statement ultimately being thrown is not valid).
[22 Oct 2006 15:19] Tonci Grgin
Dominic, thanks for your problem report.
I'm affraid I don't quite follow you here. What *exactly* is the problem and how am I supposed to recreate it?
In the meantime, try using Option 256, "Don't Use Set Locale" and infor me of results.

Explanation: This is because the value returned is based on current locale settings. In order to use comma as decimal separator you need to set the option 'Dont use setlocale' (256) in your DSN options dialog or simply add OPTION=256 to your connection string.
[23 Nov 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 May 2013 7:07] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.