Informix "Database locale information mismatch"

Question

I have informix 11.5 running in my Win-2003 box and few databases running in it.
System databases have locale en_us.819
My custom databases have locale en_us.57372 (UTF8).

There is also application deployed to JBoss 4.0.2 which has few datasources configured for those custom databases.

<local-tx-datasource>
<jndi-name>InformixDS</jndi-name>
<connection-url>jdbc:informix-sqli://@database.server@:@database.port@/tcs_catalog:[email protected]@</connection-url>
<driver-class>com.informix.jdbc.IfxDriver</driver-class>
<user-name>@database.username@</user-name>
<password>@database.password@</password>
    <new-connection-sql>set lock mode to wait 5</new-connection-sql>
    <check-valid-connection-sql>select '1' from dual</check-valid-connection-sql>
  <metadata>
     <type-mapping>InformixDB</type-mapping>
  </metadata>

I'm logged in as Administrator and when I start JBoss the following error is shown

Caused by: java.sql.SQLException: Database locale information mismatch.
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
    at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3208)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269)
    at com.informix.jdbc.IfxSqli.executeOpenDatabase(IfxSqli.java:1786)
    at com.informix.jdbc.IfxSqliConnect.<init>(IfxSqliConnect.java:1327)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:501)
    at com.informix.jdbc.IfxDriver.connect(IfxDriver.java:254)
    at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:151)
    ... 160 more
Caused by: java.sql.SQLException
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523)
    ... 170 more

DB_LOCALE and CLIENT_LOCALE are set to en_us.utf8 for Administrator.

When I set in Server Studio DB_LOCALE and CLIENT_LOCALE to en_us.utf8 I can connect my databases.

Where should I set DB_LOCALE and CLIENT_LOCALE to avoid this Database locale information mismatch error?

Thanks.

Solution 1

As per comment responses #5 and #11 by Guy Bowerman at his blog

...you'll need to set it in your environment prior to executing the oninit command to start the server (on UNIX). If the server is on Windows, you can also set the variable in the registry under: HKEY_LOCAL_MACHINE\Software\Informix\Online\%INFORMIXSERVER%\Environment

The fix is to change both CLIENT_LOCALE from the default "EN_US.CP1252" and DB_LOCALE from the default "EN_US.8859-1" to "EN_US.UTF8" in the registry.

Solution 2

You can set DB_LOCALE and CLIENT_LOCALE in JDBC database url. Have a look at my Jython code at: https://stackoverflow.com/questions/464567/how-to-get-trailing-spaces-from-varchar-column-in-informix-using-odbc . There for Polish locale I use URL like:

jdbc:informix-sqli://127:0:0:1:9088/test_td:informixserver=ol_mn;DB_LOCALE=pl_PL.CP1250;CLIENT_LOCALE=pl_PL.CP1250;charSet=CP1250

Comments

[lmmortal]

It appeared I used some old Informix driver. Driver upgrade to v3.5 and setting locales in connection string helped.

[Michał Niklas]

Nice to hear that it works. You can accept my answer so it will not be listed as unanswered.

[lmmortal]

That helped. Thanks! I didn't try to set in ...Online\%INFORMIXSERVER%\Environment