0

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
user3737906
  • 89
  • 1
  • 9
  • Can you please make the table schema and the UPDATE statement self-consistent? The column names are not the same. Do you happen to have the Unicode code point number (U+XXXX) for the troublesome character? Is it a recent addition to Unicode, do you know? – Jonathan Leffler Nov 20 '21 at 17:49
  • Unicode point number is U+0760. I'm not sure if it is a recent addition, how do i check? – user3737906 Nov 20 '21 at 19:11
  • I think the UTF8 database has to be created with GLU set (env variable GL_USEGLU=1) for the server to accept that codepoint. 'U+0760' corresponds to 0xDDA0 in UTF-8 and I don't see that included in the GLS UTF-8 encoding file. You mentioned that it works with 'dbaccess'. Is that when having CLIENT_LOCALE set to en_US.UTF8 or without it?. I expect a -202 'Illegal character' error if CLIENT_LOCALE was set. I ran a quick test with an UTF8 database created with GLU and had no errors updating or selecting that character with JDBC. An 'oncheck -pp' shows the correct 0xDDA0 in the table. – jsagrera Nov 21 '21 at 11:21
  • @jsagrera: Set env variable GL_USEGLU=1 solved the problem. Can you post it as an answer so that I can accept it? – user3737906 Nov 22 '21 at 11:41
  • @jsagrera: if you don't mind to have a look at my other Informix JDBC question at https://stackoverflow.com/questions/69927319/how-to-pass-array-of-integer-to-informix-stored-procedure. Thank you in advance – user3737906 Nov 22 '21 at 11:48

1 Answers1

2

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.

jsagrera
  • 1,978
  • 6
  • 8