Search code examples
jdbcutf-8informixcharacter-set

Informix JDBC - fail to run sql update using JDBC, but ok when run using dbaccess

The value to update contains 1 Arabic-like character ie ݠ . Unicode point number is U+0760 (I get it from https://unicode.scarfboy.com/)

If the SQL is run using dbaccess the update works. If the character is replaced with different arabic character, the update works both in JDBC and dbaccess. DB_LOCALE is en_US.utf8 . Thank you in advance.

The table

create table tbl_demo_1
(
    id serial not null,
    address varchar(100),
    primary key(id)
);
insert into tbl_demo_1 values (1,'somevalue');

The SQL

update tbl_demo_1
set address2 = 'BT. 8 ݠ MAIN ROAD'
where id = 1;

OS, IDS, JDK, JDBC

  • OS : CentOS 8
  • IDS : IBM Informix Dynamic Server Version 14.10.FC4W1DE
  • JDK : 1.8.0_172
  • JDBC: 4.10.14, 4.50.7
  • SQL client: SQuirrel SQL client, SQL Workbench/J
Solution

  • When working with UTF-8 Informix databases is recommended to use the ICU libraries rather than the Informix GLS ones because ICU supports a more wider range. To enable ICU use make sure the GL_USEGLU environment variable is set to either 1 or 4 before the server is started and the database is created.

    More info here: https://informix.hcldoc.com/14.10/help/index.jsp?topic=%2Fcom.ibm.glsug.doc%2Fids_gug_063.htm

    Depending on your API (like JDBC or .NET) you may also need to include GL_USEGLU in the connection string for it to work properly.