Informix

nested-group-icon.png

DB2

Expand all | Collapse all

DATABASE vs CONNECT TO: Invalid characters error?

  • 1.  DATABASE vs CONNECT TO: Invalid characters error?

    Posted 24 days ago
    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
    ------------------------------


  • 2.  RE: DATABASE vs CONNECT TO: Invalid characters error?

    Posted 24 days ago
    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
    ------------------------------



  • 3.  RE: DATABASE vs CONNECT TO: Invalid characters error?

    Posted 23 days ago
    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
    ------------------------------



  • 4.  RE: DATABASE vs CONNECT TO: Invalid characters error?

    Posted 23 days ago

    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
    ------------------------------



  • 5.  RE: DATABASE vs CONNECT TO: Invalid characters error?

    Posted 23 days ago
    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
    ------------------------------



  • 6.  RE: DATABASE vs CONNECT TO: Invalid characters error?

    Posted 23 days ago

    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
    ------------------------------



  • 7.  RE: DATABASE vs CONNECT TO: Invalid characters error?

    Posted 23 days ago
    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
    ------------------------------



  • 8.  RE: DATABASE vs CONNECT TO: Invalid characters error?

    Posted 23 hours ago
    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
    ------------------------------



  • 9.  RE: DATABASE vs CONNECT TO: Invalid characters error?

    Posted 20 hours ago
    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
    ------------------------------