DATABASE vs CONNECT TO: Invalid characters error?

Hello, Configuration: Platforms: Linux Debian 10.7 Informix server: IDS IDS-14.10.FC5 Informix client: CSDK 4.50.FC5 DB_LOCALE = en_us.utf8 CLIENT_LOCALE = en_us.8859-15 Can someone from IBM explain this? Depending on the usage of DATABASE or CONNECT TO, a PREPARE + EXECUTE produces error -202 with CONNECT: #include <stdlib.h> #include <stdio.h> #include <signal.h> #include <string.h> #include <locale.h> void check_sqlcode(const char *msg) { printf(">> [%s]: sqlcode=%d\\n", msg, sqlca.sqlcode); if (sqlca.sqlcode != 0 && sqlca.sqlcode != 100) exit(1); } int main(int argc, char ** argv) { /* Not related but that's what we do in our C program */ if (setlocale(LC_ALL,NULL)==NULL) { fprintf(stderr,">> Could not set the locale env...\\n"); exit(1); } #ifdef USE_DATABASE EXEC SQL DATABASE test2; check_sqlcode("DATABASE test2"); #else EXEC SQL CONNECT TO "test2"; check_sqlcode("CONNECT TO test2"); #endif EXEC SQL DROP TABLE tab1; EXEC SQL CREATE TABLE tab1 ( pk INT, vc100 VARCHAR(100) ); check_sqlcode("CREATE TABLE tab1"); EXEC SQL PREPARE s1 FROM "INSERT INTO tab1 VALUES ( 101, 'Wörthstr' )"; check_sqlcode("PREPARE s1"); EXEC SQL EXECUTE s1; check_sqlcode("EXECUTE s1"); } sf@toro:~/genero/devel/fgl/tests$ esql -o esql-8859-15.bin esql-8859-15.ec && ./esql-8859-15.bin >> [CONNECT TO test2]: sqlcode=0 >> [CREATE TABLE tab1]: sqlcode=0 >> [PREPARE s1]: sqlcode=-202 sf@toro:~/genero/devel/fgl/tests$ esql -DUSE_DATABASE -o esql-8859-15.bin esql-8859-15.ec && ./esql-8859-15.bin >> [DATABASE test2]: sqlcode=0 >> [CREATE TABLE tab1]: sqlcode=0 >> [PREPARE s1]: sqlcode=0 >> [EXECUTE s1]: sqlcode=0 sf@toro:~/genero/devel/fgl/tests$ file esql-8859-15.ec esql-8859-15.ec: C source, ISO-8859 text sf@toro:~/genero/devel/fgl/tests$ locale LANG= LANGUAGE= LC_CTYPE="en_US.iso885915" LC_NUMERIC="en_US.iso885915" LC_TIME="en_US.iso885915" LC_COLLATE="en_US.iso885915" LC_MONETARY="en_US.iso885915" LC_MESSAGES="en_US.iso885915" LC_PAPER="en_US.iso885915" LC_NAME="en_US.iso885915" LC_ADDRESS="en_US.iso885915" LC_TELEPHONE="en_US.iso885915" LC_MEASUREMENT="en_US.iso885915" LC_IDENTIFICATION="en_US.iso885915" LC_ALL=en_US.iso885915 sf@toro:~/genero/devel/fgl/tests$ dbaccess test2 - Database selected. > select * from tab1;pk 101 vc100 Wörthstr 1 row(s) retrieved. ------------------------------ Sebastien FLAESCH ------------------------------ #Informix

Interesting. Doesn't appear to occur with dbaccess. And doesn't appear to go wrong on the server (since trapping errno 202 doesn't catch it). -> something to be looked at from esql/c perspective ------------------------------ Andreas Legner ------------------------------

Hi Sebastien, Both scenario works for me i.e. "DATABASE" and "CONNECT TO". I set the DB_LOCALE to en_US.UTF8 and CLIENT_LOCALE to en_US.8859-15. I could see the data correctly inserted using dbaccess including the special character "ö". The only difference is locales set in the system, in my system its UTF-8 and in your system its 8859-15. sh-3.2$ locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL= sh-3.2$ Thanks -Shesh ------------------------------ Sheshnarayan Agrawal ------------------------------

Hi Shesh, thanks for looking at this! Do you mean that it works for you with ESQL/C or with dbaccess or both? I think it's an ESQL/C specific issue, I could also insert the ö char as ISO-8859-15 client with dbaccess (with LC_ALL=en_US.iso885915) I assume dbaccess uses a different connection API and my ESQL/C CONNECT TO. IMPORTANT: Your C lib (setlocale) settings with LANG/LC_ALL should match the Informix CLIENT_LOCALE character encoding! It makes no sense for an ESQL/C (or dbaccess) program to run in UTF-8, but act as an ISO-8859-15 client for Informix. Except if you consider to convert all ISO-8859-15 strings to/from UTF-8 back and forth in the C code, which would be stupid since you could just set CLIENT_LOCALE=en_us.utf8 ------------------------------ Sebastien FLAESCH ------------------------------

Hi Sebastien, It works for me both in ESQL/C and dbaccess. dbaccess I just used to view the inserted data from ESQL/C test. Yes, here certainly "system locales" are making difference. I have asked our GLS/I18N expert to look into the same as well. I will update as I hear more. I could see in the ESQL/C source code that its returning -202 error when it finds "invalid character" (in this case character in question is ö) while processing. However its surprising to see that it works for you in one scenario (CONNECT TO) and not in other (DATABASE)! You may also want to open defect in CSDK/ESQLC for official response. Thanks -Shesh ------------------------------ Sheshnarayan Agrawal ------------------------------

I can reproduce the problem. I think there is a different in how the first GLS environment gets created from a "database .." to a "connect to". I guess the "connect to" is not initializing the environment properly so, when the ESQL code parses the statement with the <o-diaresis> (0xf6), it raises the 202 error. You won't see anything on the database as the error is generated on the client side (_sqs_pre() function) A copy and paste may not produce a "real" 8859-1/15 <o-diaresis>. I guess that's why Shesh couldn't reproduce it in his environment. 99.999% this is a defect. ------------------------------ Javier Sagrera ------------------------------ Hi Shesh, thanks for looking at this! Do you mean that it works for you with ESQL/C or with dbaccess or both? I think it's an ESQL/C specific issue, I could also insert the ö char as ISO-8859-15 client with dbaccess (with LC_ALL=en_US.iso885915) I assume dbaccess uses a different connection API and my ESQL/C CONNECT TO. IMPORTANT: Your C lib (setlocale) settings with LANG/LC_ALL should match the Informix CLIENT_LOCALE character encoding! It makes no sense for an ESQL/C (or dbaccess) program to run in UTF-8, but act as an ISO-8859-15 client for Informix. Except if you consider to convert all ISO-8859-15 strings to/from UTF-8 back and forth in the C code, which would be stupid since you could just set CLIENT_LOCALE=en_us.utf8 ------------------------------ Sebastien FLAESCH ------------------------------ Original Message: Sent: Wed February 10, 2021 06:29 AM From: Sheshnarayan Agrawal Subject: DATABASE vs CONNECT TO: Invalid characters error? Hi Sebastien, Both scenario works for me i.e. "DATABASE" and "CONNECT TO". I set the DB_LOCALE to en_US.UTF8 and CLIENT_LOCALE to en_US.8859-15. I could see the data correctly inserted using dbaccess including the special character "ö". The only difference is locales set in the system, in my system its UTF-8 and in your system its 8859-15. sh-3.2$ locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL= sh-3.2$ Thanks -Shesh ------------------------------ Sheshnarayan Agrawal

Glad to see that you reproduce! https://www.ibm.com/mysupport/s/case/5003p00002VRlsVAAT/connect-to-insert-produces-invalid-characters-error-202?openCase=true ------------------------------ Sebastien FLAESCH ------------------------------

Hi Sebastien, The defect has been fixed and it will be available in next release of CSDK. However if you are looking for immediate fix/patch, kindly contact IBM tech support and ask fix for idsdb00107718 Thanks & Regards -Shesh ------------------------------ Sheshnarayan Agrawal ------------------------------

Thanks for the message! We (FOURJS) do not need a fix right now as we can wait for next release. I can't tell for our customers in production with Informix and having such config. Seb ------------------------------ Sebastien FLAESCH ------------------------------