jdbc locale settings - numeric

[Andreas Leitgeb]

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.

[Arne Vajhøj]

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.

:-)

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.

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.

Maybe:
http://sourceforge.net/projects/p6spy/

Arne

[Andreas Leitgeb]

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)

[Arne Vajhøj]

On 2/26/2015 2:32 PM, Andreas Leitgeb wrote:
> Arne Vajhøj <ar...@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.

>> 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 know nothing about Informix.

>> 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,

It may be a lot less intrusive to do it in such a driver.

> 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:

Distinguishing between numbers and strings should not be that hard
a parse task.

With the driver approach you could let it catch that exception, fix
SQL, retry and save the expected separator.

Arne

[Wayne]

On 2/26/2015 9:44 AM, Andreas Leitgeb wrote:

Most databases support a SET LOCALE statement. I think
Informix does as well. A bit of work with Google found
some sample code for a different DBMS, but it may give you
the hints you need:

<http://my.vertica.com/docs/5.1.6/HTML/13631.htm>

--
Wayne

[Andreas Leitgeb]

That sounds good! I will report back, after I got to try it.

Thanks!

[Wayne]

More hints:
Postgresql uses this command instead of ``set locale'':

set lc_numeric = "POSIX";

--
Wayne

[Andreas Leitgeb]

Andreas Leitgeb <a...@auth.logic.tuwien.ac.at> wrote:
> Wayne <nos...@all.invalid> wrote:
>> On 2/26/2015 9:44 AM, Andreas Leitgeb wrote:
>>> 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.

>> Most databases support a SET LOCALE statement. I think
>> Informix does as well. A bit of work with Google found
>> some sample code for a different DBMS, but it may give you
>> the hints you need:
>> <http://my.vertica.com/docs/5.1.6/HTML/13631.htm>
> That sounds good! I will report back, after I got to try it.

Meanwhile things changed for worse and then for better:

- There are some of these predefined strings that are actually
written specifically for "their" DB's expected locale-setting.
(That means: setting the locale in my code would have broken
those.)

- Some of the predefined strings were actually changed to cut out the
quoting, (thus 3.14 instead of "3.14") so they now work across locales.

Thanks anyway for the answer, even though the problem was finally
fixed differently.


PS: I still wish there was a locale that would define "any of . or ,"
as decimal point and irrecoverably erase the harddisk of whoever uses
grouping chars. ;-)