Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

ESQL/C conversion error with CLVCHARPTRTYPE

  • 1.  ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 05:10 AM
    Edited by Sebastien FLAESCH Tue August 08, 2023 09:39 AM

    Hello,

    Once I reported an issue with ESQL/C charset conversion when using the CLVCHARPTRTYPE type:
    https://ibm-data-and-ai.ideas.ibm.com/ideas/INFX-I-400

    Sorry but I have no bug or feature ID, I am totally confused with HCL/Actian/IBM forums and support channels, so I don't know understand where I can ask for help.

    Normally the conversion issue has been addressed with IDS 14.10.FC10 + CSDK 4.50.FC10.
    According to https://www.iiug.org/wp-content/uploads/2023/04/Informix-VNext-New-Features.pdf

    :


    CSDK behavior change in codeset conversion for the CLVCHARPTRTYPE; starting 14.10.xC10, codeset conversion will be done if Client and DB Locale differ when the CLVCHARPTRTYPE is used. For more information, see the lvarchar pointer host variable.

    I had a chance to install IDS 14.10.FC10 + CSDK 4.50.FC10 to test the charset conversion for CLVCHARPTRTYPE.

    Bad news: I get now an error with these versions:

    SQL statement error number -23103.
    Code-set conversion function failed due to illegal sequence or invalid value.

    Happens when using:
    DB_LOCALE=en_us.utf8
    CLIENT_LOCALE=en_us.8859-15

    The database is obviously UTF-8, and table / data was created this way:

        CREATE TABLE tab1 ( pk INT, vc100 VARCHAR(100), lvc100 LVARCHAR(100) )

        INSERT INTO tab1 VALUES ( 101, 'Wörthstr', 'Wörthstr' )

    We let the Informix client allocate the required memory for the fetch buffer : ifx_var_flag(&lvarchar, 1)

    Before *.FC10, there was no conversion at all.



    ------------------------------
    Sebastien FLAESCH
    ------------------------------



  • 2.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 06:34 AM
    Hi,

    are you sure you have been getting the values in ISO8859-15 before in your application ?
    If data is stored as UTF-8 (which is indicated by the DB_LOCALE) then the conversion to ISO might fail
    because there is no mappable character for each source character.
    So it might depend on the content of your field if content is converted correctly or will throw an error.
    For a number of special chars, there is no conversion to ISO-8859-15 possible.
    Vice versa should work always, because every char in ISO-8859-15 has a twin in UTF-8.
    In case your client app is capable to work with UTF-8 charset, you should not do any conversion
    initiated by the environment variables.
    MARCUS HAARMANN




    Original Message


  • 3.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 08:28 AM

    Hi Marcus,

    I know all of this.

    The app was never getting the correct data, because no charset conversion occurred before IDS 14.10.FC10 / CSKD 4.50.FC10 when using CLVCHARPTRTYPE.

    Note that the data that produces the error has a unique non-ASCII char:   ö  , which is valid in both UTF-8 and ISO-8859-15 charsets.

         INSERT INTO tab1 VALUES ( 101, 'Wörthstr', 'Wörthstr' )

    The database was created with DB_LOCALE=en_us.utf8.

    The CREATE TABLE and INSERT statements have been executed with dbaccess, using same settings as my ESQL/C app (DB_LOCALE=en_us.utf8, CLIENT_LOCALE=en_us.8859-15)

    A SELECT statement works fine from dbaccess.

    My gnome terminal setting is defined with ISO-8859-15 charset.

    The conversion error only occurs when using a CLVCHARPTRTYPE type. 
    When using CVCHARTYPE or CCHARTYPE, there is no problem and expected chars are received in the fetch buffer (UTF-8 to ISO-8859-15 conversion works)

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 4.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 09:29 AM

    Hi Seb,

    so you're saying there is a change to the positive wrt. CVCHARTYPE and CCHARTYPE, but CLVCHARPTRTYPE type was forgotten?

    BR,

     Andreas



    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 5.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 09:38 AM
    Edited by Sebastien FLAESCH Tue August 08, 2023 09:40 AM

    Conversion has always worked when using CVCHARTYPE and CCHARTYPE.

    When using CLVCHARPTRTYPE:

    Before IDS 14.10.FC10 / CSKD 4.50.FC10, no conversion was done (and I think this was documented this way).

    Starting with IDS 14.10.FC10 / CSKD 4.50.FC10, conversion is supposed to work (and it's documented), but I get the conversion error.

    The lvarchar pointer host variable

    Starting 14.10.xC10, codeset conversion will be done when client locale differs from DB locale when CLVCHARPTRTYPE is used.

    See my initial post and the link to the presentation: It's a described as feature of IDS 14.10.FC10 / CSKD 4.50.FC10.

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 6.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 12:01 PM

    Ummmmm... Are you sure you have a 0xC3B6 on the database?  I ran a quick test with 4.50.FC10 and I could query a 'ö' (0xC3B6) lvarchar in a UTF-8 db using CLIENT_LOCALE=en_US.8859-15. It gets converted to the expect 0xF6 on the client side.

    D:\Infx\ids1410>oncheck -pp testutf8:t1 256
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
16:240           228188210 ed4b   1      1    DATA         29    4059  0        0
        slot ptr   len   flg
        1    24    5     0
slot   1:
    0:  0  3  0 c3 b6                                    ...C6...........

D:\Infx\ids1410>



d:\Infx\work\esql>chcp 28605
Active code page: 28605

d:\Infx\work\esql>esql -V
IBM Informix CSDK Version 4.50, IBM Informix-ESQL Version 4.50.FC10

d:\Infx\work\esql>set | grep LOCALE
CLIENT_LOCALE=en_US.8859-15
DB_LOCALE=en_US.utf8

d:\Infx\work\esql>lvarptr
ö
d:\Infx\work\esql>lvarptr | od -x
0000000000      00F6
0000000001

d:\Infx\work\esql>


    ------------------------------
    Javier Sagrera
    ------------------------------

    Original Message


  • 7.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 01:47 PM
    Edited by Sebastien FLAESCH Tue August 08, 2023 01:49 PM

    Thanks for your help... we make progress:

    I am on Linux Debian.

    $ dbaccess sysmaster -

    Database selected.

    > SELECT * FROM sysdbslocale WHERE dbs_dbsname = 'test1';

    dbs_dbsname  test1
    dbs_collate  en_US.57372

    1 row(s) retrieved.

    Does 57372 = UTF-8 ?

    Seems data has been correctly inserted:

    $ oncheck -pp test1:tab1 256
    addr             stamp    chksum nslots flag type         frptr frcnt next     prev
    1:91608          329364   6349   1      1    DATA         50    1990  0        0       
        slot ptr   len   flg
        1    24    26    0  
    slot   1:
        0:  0  0  0 65  9 57 c3 b6 72 74 68 73 74 72  0  a   ...e.WC6rthstr..
       16:  0 57 c3 b6 72 74 68 73 74 72                     .WC6rthstr......

    I have used dbaccess with correct CLIENT_LOCALE + DB_LOCALE to create the table and insert the row with:

    $ file table.sql 
    table.sql: ISO-8859 text


    $ cat table.sql 

    DROP TABLE tab1;

    CREATE TABLE tab1 ( pk INT, vc100 VARCHAR(100), lvc100 LVARCHAR(100) );

    INSERT INTO tab1 VALUES ( 101, 'Wörthstr', 'Wörthstr' );

    $ echo $CLIENT_LOCALE
    en_us.8859-15
    $ echo $DB_LOCALE
    en_us.utf8
    $ echo $SERVER_LOCALE
    en_us.8859-15

    What I am missing here?

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 8.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 01:57 PM

    Hi,

    Yeap, 57372 is 0xE01C which is the code for UTF-8 (You can find the whole list in the $INFORMIXDIR/gls/cm/registry file).

    Your oncheck output shows you have the correct value for the 'ö'  (0xC3B6) so something else must be going on here.
    I will try to run it on a Linux box but shouldn't really matter, in the meantime this is my esql test:

    // ------- lvarptr.ec -------
#include <stdio.h>

static void print_lvarchar_ptr(
    const char *tag,
    EXEC SQL BEGIN DECLARE SECTION;
    parameter lvarchar **lv
    EXEC SQL END DECLARE SECTION;
    )
    {
        char *data;

        data = ifx_var_getdata(lv);
        if (data == 0)
            data = "<<NO DATA>>";
        printf("%s", data);
    }

static void process_stmt(char *stmt)
{
	EXEC SQL BEGIN DECLARE SECTION;
	lvarchar *lv1;
	int seq;
	char *stmt1 = stmt;
	EXEC SQL END DECLARE SECTION;

	
	EXEC SQL WHENEVER ERROR STOP;
	EXEC SQL PREPARE p_collect FROM :stmt1;
	EXEC SQL DECLARE c_collect CURSOR FOR p_collect;
	EXEC SQL OPEN c_collect;

	ifx_var_flag(&lv1, 1);
	
	while (sqlca.sqlcode == 0)
	{
		EXEC SQL FETCH c_collect INTO :lv1;
		if (sqlca.sqlcode == 0)
		{
			print_lvarchar_ptr("LVARCHAR 1", &lv1);
			ifx_var_dealloc(&lv1);
		}
	}

	EXEC SQL CLOSE c_collect;
	EXEC SQL FREE c_collect;
	EXEC SQL FREE p_collect;
}

int main(int argc, char **argv)
{
	EXEC SQL BEGIN DECLARE SECTION;
	char *dbase = "testutf8";
	char *data;
	EXEC SQL END DECLARE SECTION;

	if (argc > 1)
		dbase = argv[1];
	EXEC SQL WHENEVER ERROR STOP;
	EXEC SQL CONNECT TO :dbase;
   // set CLIENT_LOCALE=en_US.utf8 to create/insert it or do with dbaccess
   // EXEC SQL DROP TABLE t1;
   // EXEC SQL CREATE TABLE t1(c1 lvarchar(10));
   // EXEC SQL INSERT INTO T1 VALUES ("ö");
	process_stmt("SELECT c1::lvarchar(10) from t1");

	return 0;
}
// ------- lvarptr.ec -------


    ------------------------------
    Javier Sagrera
    ------------------------------

    Original Message


  • 9.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue August 08, 2023 02:27 PM
    Just a guess: other Environment such as LANG, LC... 
    Should not have an effect normally, but can you check with a script which is defining a Set of Environment vars and execute with empty Environment (env -)

    Marcus 



    Original Message


  • 10.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Wed August 09, 2023 04:26 AM

    Marcus, here is my LANG/LC_ALL setting:

    sf@toro:~/genero/devel/fgl/tests/FGL-5532$ echo $LC_ALL
    en_US.iso885915
    sf@toro:~/genero/devel/fgl/tests/FGL-5532$ locale
    LANG=
    LANGUAGE=en_US:en
    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



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 11.  RE: ESQL/C conversion error with CLVCHARPTRTYPE
    Best Answer

    Posted Wed August 09, 2023 05:15 AM

    Hi,

    Pretty sure there is something broken in FC10.  

    /*
d:\Infx\work\esql>set | grep LOCALE
CLIENT_LOCALE=en_US.8859-15
DB_LOCALE=en_US.utf8
d:\Infx\work\esql>
d:\Infx\work\esql>setinfx 450fc8
setting INFORMIX to csdk450fc8
d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>
d:\Infx\work\esql>setinfx 450fc10
setting INFORMIX to csdk450fc10
d:\Infx\work\esql>f3
--test--
--test<--

d:\Infx\work\esql>f3
--test--
--testM--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test/--

d:\Infx\work\esql>f3
--test--

Program stopped by WHENEVER STOP
sqlca:
 sqlcode:-23103
 sqlerrm: ''
 sqlerrp: ''
 sqlerrd:
 sqlerrd(1)=1
        (2)=0
        (3)=1
        (4)=1
        (5)=0
        (6)=0
 sqlwarn.sqlwarn0:
        .sqlwarn1:
        .sqlwarn2:
        .sqlwarn3:
        .sqlwarn4:
        .sqlwarn5:
        .sqlwarn6:
        .sqlwarn7:


d:\Infx\work\esql>f3
--test--
--test=--

d:\Infx\work\esql>f3
--test--
--test  --

d:\Infx\work\esql>f3
--test--
--test`--

d:\Infx\work\esql>f3
--test--
--testY--

d:\Infx\work\esql>f3
--test--
--testN--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--testG--

d:\Infx\work\esql>f3
--test--

Program stopped by WHENEVER STOP
sqlca:
 sqlcode:-23103
 sqlerrm: ''
 sqlerrp: ''
 sqlerrd:
 sqlerrd(1)=1
        (2)=0
        (3)=1
        (4)=1
        (5)=0
        (6)=0
 sqlwarn.sqlwarn0:
        .sqlwarn1:
        .sqlwarn2:
        .sqlwarn3:
        .sqlwarn4:
        .sqlwarn5:
        .sqlwarn6:
        .sqlwarn7:


d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--

Program stopped by WHENEVER STOP
sqlca:
 sqlcode:-23103
 sqlerrm: ''
 sqlerrp: ''
 sqlerrd:
 sqlerrd(1)=1
        (2)=0
        (3)=1
        (4)=1
        (5)=0
        (6)=0
 sqlwarn.sqlwarn0:
        .sqlwarn1:
        .sqlwarn2:
        .sqlwarn3:
        .sqlwarn4:
        .sqlwarn5:
        .sqlwarn6:
        .sqlwarn7:


d:\Infx\work\esql>   
  
Works as expected if there is no conversion:

d:\Infx\work\esql>set CLIENT_LOCALE=en_US.utf8

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>f3
--test--
--test--

d:\Infx\work\esql>

  
*/


#include <stdio.h>
int main(int argc, char **argv)
{
	EXEC SQL BEGIN DECLARE SECTION;
	 char *dbase = "testutf8";	     
     lvarchar *lv1;
	 char *data; 
	 char stmt1[100];
     lvarchar lv2[100];
	EXEC SQL END DECLARE SECTION;

	if (argc > 1)
		dbase = argv[1];
        
	EXEC SQL WHENEVER ERROR STOP;
	EXEC SQL CONNECT TO :dbase;
    
    // WORKS
    EXEC SQL SELECT 'test'::lvarchar(100) into :lv2;
    printf("--%s--\n", lv2);
    
    sprintf(stmt1,"select 'test'::lvarchar(100)");
    EXEC SQL PREPARE p_collect FROM :stmt1;
	EXEC SQL DECLARE c_collect CURSOR FOR p_collect;
	EXEC SQL OPEN c_collect;
	ifx_var_flag(&lv1, 1);	
    
    // FAILS  
	EXEC SQL FETCH c_collect INTO :lv1;	    
    data = ifx_var_getdata(&lv1);  
    printf("--%s--\n", data);
	
	return 0;
}


    Looks like we are not using the correct length for the lvarchar data. The codeset conversion may fail with a 23103 depending of whatever 'leftover' are in that piece of memory.
    Platform is not a factor, I get the same result in both Linux and Windows.

    I already logged a new defect (idsdb00112743) but I suggest to open a support case, so you can keep track of the defect and support can work to get a fix asap.



    ------------------------------
    Javier Sagrera
    ------------------------------

    Original Message


  • 12.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Wed August 09, 2023 06:13 AM

    Thanks Javier for sharing your sample.

    I have tried it and I get "<NO DATA>", retesting with my Genero fglrun env and I also get an empty string now...

    I must double check again my environment, something has changed in between.

    sf@toro:~/genero/devel/fgl/tests/FGL-5532$ esql -o lvarptr.bin lvarptr.ec 

    sf@toro:~/genero/devel/fgl/tests/FGL-5532$ ./lvarptr.bin 
    <<NO DATA>>

    sf@toro:~/genero/devel/fgl/tests/FGL-5532$ dbaccess test1 -

    Database selected.

    > select * from t1;

    c1  ö 

    1 row(s) retrieved.



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 13.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Wed August 09, 2023 06:49 AM

    Add more than one character to the c1 row, like "aaöaa".
    Had not chance to look at the code yet, but I think the value used to calculate the string length for the conversion is wrong, it is not properly taken in count that the UTF8 'ö' (C3B6) will convert to just a single byte (F6) in 8859-15.
    Adding a `ifx_var_getlen(lv)` is clear the length is wrong:

    informix@DESKTOP-D8Q4PIM:~/CSDK$ CLIENT_LOCALE=en_US.utf8 ./a.out
length of data : 3
öinformix@DESKTOP-D8Q4PIM:~/CSDK$ CLIENT_LOCALE=en_US.8859-15 ./a.out
length of data : 4
<<NO DATA>>informix@DESKTOP-D8Q4PIM:~/CSDK$


    ------------------------------
    Javier Sagrera
    ------------------------------

    Original Message


  • 14.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Wed August 09, 2023 09:15 AM

    Good that you found the length issue!

    It appears that sometimes it converts (with some additional char at the end), and sometimes I get the charset conversion error...

    sf@toro:~/genero/devel/fgl/tests/FGL-5532$ ./lvarptr.bin 
    aaöaaq

    sf@toro:~/genero/devel/fgl/tests/FGL-5532$ ./lvarptr.bin 
    aaöaa| 

    sf@toro:~/genero/devel/fgl/tests/FGL-5532$ ./lvarptr.bin 

    Program stopped by WHENEVER STOP
    sqlca:
     sqlcode:-23103
     sqlerrm: ''
     sqlerrp: ''
     sqlerrd:
     sqlerrd(1)=1
            (2)=0
            (3)=1
            (4)=2
            (5)=0
            (6)=257
     sqlwarn.sqlwarn0: 
            .sqlwarn1: 
            .sqlwarn2: 
            .sqlwarn3: 
            .sqlwarn4: 
            .sqlwarn5: 
            .sqlwarn6: 
            .sqlwarn7: 

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 15.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Fri August 11, 2023 07:43 AM

    Fix for idsdb00112743 is in. Next CSDK (4.50.xC11) should have it.
    If you can't wait until then try getting a CSDK special build through tech support.



    ------------------------------
    Javier Sagrera
    ------------------------------

    Original Message


  • 16.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue January 14, 2025 12:16 PM

    Hello,

    Is bug idsdb00112743 fixed in 4.50.FC11W1 or 15.0.0.0?

    I have now CSDK 4.50.FC11W1 installed on my Debian 12, did not test with 15 yet.

    I get a segfault in the client with a similar case with CLIENT_LOCALE is UTF-8 and DB_LOCALE is ISO-8859-15.
    I DO NOT get an the crash when the DB_LOCALE is UTF-8 like CLIENT_LOCALE.

    This occurs with ASCII characters, but now I have much more data:

    My SQL column is an LVARCHAR(5000) and data is 4475 bytes (pure ASCII this time).

    I must investigate, just asking if idsdb00112743 has been fixed.

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 17.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue January 14, 2025 01:10 PM

    Yep, that's what it looks like.



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 18.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue January 14, 2025 02:40 PM

    Hello Andreas!

    Thanks for your answer.

    I will open another thread for the new issue one of a customer has detected.

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 19.  RE: ESQL/C conversion error with CLVCHARPTRTYPE

    Posted Tue January 14, 2025 02:54 PM

    FYI, the new thread:

    https://community.ibm.com/community/user/datamanagement/discussion/segfault-since-csdk-450fc10-with-lvarchar-and-client-locale-different-from-db-locale

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


Related Content