I have a web spring application that uses ibatis. This application can be installed in different servers and different DBs. The problem comes when I setup a server, for example, in English (DOT as decimal separator) and a DB in Italian (COMMA as decimal separator). Consequently the decimal separators are different. I have one transformation depending on the DB I'm using in each case, not a big deal in iBatis. Some examples of the where condition are (Other DBs are possible a part from those ones):
Oracle: DECIMAL_COLUMN = TO_NUMBER(REPLACE(#STRING_VALUE#, '.', ','))
SQLServer: DECIMAL_COLUMN = REPLACE(#STRING_VALUE#, '.', ',')
For that example I've got a "ORA-01722: invalid number". My question is: Is there any way to get the decimal separator for each type of DB in iBatis? So I can replace that to something like:
DECIMAL_COLUMN = TO_NUMBER(REPLACE(#STRING_VALUE#, #SERVER_SIDE_DECIMAL_SEPARATOR#, #DATABASE_DECIMAL_SEPARATOR#))
What ever other solution or work around is more than welcome.
Thanks
Although didn't include his comment as a possible answer, I consider it a good possible solution. Here with a little bit of code
In Oracle case, altering the session to ,.
or .,
:
alter session set NLS_NUMERIC_CHARACTERS=',.';
depending on the server's locale. Simply using DecimalFormatSymbols to get those values:
String decimalSeparator = String.valueOf(new DecimalFormatSymbols().getDecimalSeparator());
String groupingSeparator = String.valueOf(new DecimalFormatSymbols().getGroupingSeparator());
In SQL Server, setting up the session language to the same as the server side:
SET LANGUAGE the_language;
and getting the language using Locale:
String language = Locale.getDefault().getDisplayLanguage("en_US");