Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #31223

Re: jdbc locale settings - numeric

Show all headers | View raw

Arne Vajhøj <arne@vajhoej.dk> wrote:
> On 2/26/2015 9:44 AM, Andreas Leitgeb wrote:
>> In my (Java) program, I need to open a database (using jdbc),
>> and send it predefined update statements. They are generated
>> elsewhere, and I cannot change them.
>>
>> Some of these predefined statements contain decimal numbers
>> enclosed in quotes using a "." for the decimal point.
>>
>> This works all fine for those databases that have appropriate
>> default settings for "char to numeric" conversion.
>>
>> Some other databases (on other servers/hosts), however, appear
>> to expect decimals as "3,1415" with a comma by default.  They
>> use German locale.
>>
>> I'm running my head against walls when trying to find out how
>> to tell the database (at connect time) to use decimal point,
>> not decimal comma.
>>
>> E.g.:
>> setting property "CLIENT_LOCALE" to "en_US" had no effect,
>> setting property "DB_LOCALE" to "en_US" caused the connect to
>>     fail with a message that GL_CTYPE and GL_COLLATE settings
>>     were incompatible.  (I've already googled the exact message,
>>     but none of the hits were helpful. If I had the exact wording
>>     at hand right now, I'd have posted it here)
>>
>> For date-values and currency values, there are properties to
>> declare to the server which format is used, but I haven't yet
>> found a property for number format.
> I assume there is no point in suggesting doing this the right way
> with prepared statements.
>:-)
> So now we know that we can not stop hitting ourselves in the head
> with a hammer and we just need to minimize how much it hurt.
>:-)
You hit the nail on its head with that :-)

Actually I do use PreparedStatements a lot, too, and they work like
a charm, but sometimes all I have is the complete Statement as a 
String with values quoted and embedded. :-/

> The ability to set such parameters is database specific
> and you did not specify which database, so difficult to say
> whether there is something you can set.

Oups, sorry, its an Informix database server (both, those with
decimal-point and those with decimal-comma)

I have done quite a lot of googling before asking here, but
IBM's pages leave much to hope for, and stackexchange had some
similar question with answers that didn't help me.
e.g.: http://stackoverflow.com/questions/2020608/
The answers only point to PreparedStatement or DBMONEY
(as the question was really about currency values)

> But I will suggest something more extreme to give you back
> control: use an intercepting pass-through JDBC driver and change
> that SQL as needed.

Well, if automatically changing the sql-string were feasible, I could
do it in my program, but how would I know if some pattern ..."3.14"...
is really a number to be unquoted, or some string value?  Even more,
I don't even know what format the database really wants, until it throws
back an sql-error to me... apropos, meanwhile I have that error at hand:

" -1213   A character to numeric conversion process failed.
" 
" A character value is being converted to numeric form for storage in a
" numeric column or variable. However, the character string cannot be
" interpreted as a number. It contains some characters other than white
" space, digits, a sign, a decimal, or the letter e; or the parts are in
" the wrong order, so the number cannot be deciphered.
" 
" If you are using NLS, the decimal character or thousands separator
" might be wrong for your locale.

It appears like the last paragraph applies to me, but it doesn't tell
me how NOT to use NLS... (and neither did my google searches so far)

Back to comp.lang.java.programmer | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

jdbc locale settings - numeric Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2015-02-26 14:44 +0000
  Re: jdbc locale settings - numeric Arne Vajhøj <arne@vajhoej.dk> - 2015-02-26 11:41 -0500
    Re: jdbc locale settings - numeric Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2015-02-26 19:32 +0000
      Re: jdbc locale settings - numeric Arne Vajhøj <arne@vajhoej.dk> - 2015-02-26 15:47 -0500
  Re: jdbc locale settings - numeric Wayne <nospam@all.invalid> - 2015-02-27 00:29 -0500
    Re: jdbc locale settings - numeric Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2015-02-27 10:59 +0000
      Re: jdbc locale settings - numeric Wayne <nospam@all.invalid> - 2015-02-27 19:38 -0500
      Re: jdbc locale settings - numeric Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2015-03-15 00:27 +0000

csiph-web