Topic
  • 7 replies
  • Latest Post - ‏2009-01-28T23:36:56Z by SystemAdmin
SystemAdmin
SystemAdmin
262 Posts

Pinned topic Invalid string or buffer length.

‏2008-06-10T16:19:59Z |
We receive the following error message when we attempt to run queries that contain foreign characters.

S1090unixODBCIBMiSeries Access ODBC DriverInvalid string or buffer length.
ISQLERROR: Could not SQLPrepare

An example query with a foreign character appears below.

select example from example where example = 'ë'

The same problem happens for many foreign characters.

How can we fix this problem?

Thanks
Updated on 2009-01-28T23:36:56Z at 2009-01-28T23:36:56Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    262 Posts

    Re: Invalid string or buffer length.

    ‏2008-06-10T16:54:14Z  
    Hello Chrisa

    This is most likely a bug in your code :)

    When using "foreign" characters, it is possible to edit the script file/program file in one encoding and run the program in other.

    Which encoding are you using in your process and which encoding are you using in your code file?
  • SystemAdmin
    SystemAdmin
    262 Posts

    Re: Invalid string or buffer length.

    ‏2008-06-10T17:10:11Z  
    Hello Chrisa

    This is most likely a bug in your code :)

    When using "foreign" characters, it is possible to edit the script file/program file in one encoding and run the program in other.

    Which encoding are you using in your process and which encoding are you using in your code file?
    The same error message appears for both of the situations below.

    1) Running isql via SSH via PuTTY. I paste the foreign character from a text file into PuTTY.

    2) Running a PHP script that gets the foreign character from a MySQL database and then attempts to run a query with the foreign character via ODBC.

    I am not sure how to answer your question. Can you explain further, now that you know how the foreign characters get in the query?

    Thanks

    Message was edited by: chrisa2
    Updated on 2008-06-10T17:10:11Z at 2008-06-10T17:10:11Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    262 Posts

    Re: Invalid string or buffer length.

    ‏2008-06-10T17:39:33Z  
    The same error message appears for both of the situations below.

    1) Running isql via SSH via PuTTY. I paste the foreign character from a text file into PuTTY.

    2) Running a PHP script that gets the foreign character from a MySQL database and then attempts to run a query with the foreign character via ODBC.

    I am not sure how to answer your question. Can you explain further, now that you know how the foreign characters get in the query?

    Thanks

    Message was edited by: chrisa2
    Characters are nothing but single byes or sequences of bytes. When you see a character like the one you want to use, the representation in bytes of such character may be different depending on the encoding of the program where you are seeing it.

    When you copy the character, you get those bytes from one program (you did not specify where you copy from) and then paste them in another (PuTTY). PuTTY may or may not be converting to a local encoding. Your linux terminal is working in some other encoding. You need to execute the command "locale" to determine the locale of your terminal. the locale determines how characters are processed.

    Try changing your terminal locale.

    1. export LANG=en_US

    that will set an ISO encoding instead of UTF-8

    If you really need UTF-8, isql may not be the best option (tho, I could be wrong, but I know they had issues with UTF-8)
  • SystemAdmin
    SystemAdmin
    262 Posts

    Re: Invalid string or buffer length.

    ‏2008-06-10T18:35:35Z  
    Characters are nothing but single byes or sequences of bytes. When you see a character like the one you want to use, the representation in bytes of such character may be different depending on the encoding of the program where you are seeing it.

    When you copy the character, you get those bytes from one program (you did not specify where you copy from) and then paste them in another (PuTTY). PuTTY may or may not be converting to a local encoding. Your linux terminal is working in some other encoding. You need to execute the command "locale" to determine the locale of your terminal. the locale determines how characters are processed.

    Try changing your terminal locale.

    1. export LANG=en_US

    that will set an ISO encoding instead of UTF-8

    If you really need UTF-8, isql may not be the best option (tho, I could be wrong, but I know they had issues with UTF-8)
    I was able to get the isql working by changing the PuTTY settings to UTF-8. However, I still have the original problem in PHP. I am using PHP to query a MySQL database and then send that data to a DB2 server via unixODBC and ISeries Access for Linux. Some of the data that PHP gets from the MySQL database contains foreign characters. I believe the foreign characters are stored correctly in MySQL, because I can see them correctly. When I output the query that is sent to the DB2 server from PHP for debugging purposes, I can even get the characters to appear correctly by adding a meta content-type UTF-8 HTML tag in the head of the HTML. So, that would tell me that the characters are being sent correctly from PHP to unixODBC and ISeries Access for Linux. The error in PHP appears below.

    Warning: odbc_exec() http://function.odbc-exec: SQL error: unixODBCIBMiSeries Access ODBC DriverInvalid string or buffer length., SQL state S1090 in SQLExecDirect in /var/www/vhosts/example.com/httpdocs/example.php on line 18

    query: select example from example where example = 'ë'

    Thanks for the help.
  • SystemAdmin
    SystemAdmin
    262 Posts

    Re: Invalid string or buffer length.

    ‏2008-06-11T02:22:59Z  
    I was able to get the isql working by changing the PuTTY settings to UTF-8. However, I still have the original problem in PHP. I am using PHP to query a MySQL database and then send that data to a DB2 server via unixODBC and ISeries Access for Linux. Some of the data that PHP gets from the MySQL database contains foreign characters. I believe the foreign characters are stored correctly in MySQL, because I can see them correctly. When I output the query that is sent to the DB2 server from PHP for debugging purposes, I can even get the characters to appear correctly by adding a meta content-type UTF-8 HTML tag in the head of the HTML. So, that would tell me that the characters are being sent correctly from PHP to unixODBC and ISeries Access for Linux. The error in PHP appears below.

    Warning: odbc_exec() http://function.odbc-exec: SQL error: unixODBCIBMiSeries Access ODBC DriverInvalid string or buffer length., SQL state S1090 in SQLExecDirect in /var/www/vhosts/example.com/httpdocs/example.php on line 18

    query: select example from example where example = 'ë'

    Thanks for the help.
    I need the code page being used in the MySQL table and fields and the same for the DB2 tables.
  • SystemAdmin
    SystemAdmin
    262 Posts

    Re: Invalid string or buffer length.

    ‏2009-01-21T11:39:02Z  
    We have this exact same problem. Did you ever figure this one out? We have a PHP application serving web pages (LAMP stack: Ubuntu Hardy, PHP5) where we collect user information to store in db2 on an as/400 (or ISeries as I believe it is renamed to now). We too communicate with the db2 via unixODBC and ISeries Access for Linux. We're using odbc_exec to run the queries.

    The problem is similar, occationally the data we collect contains Norwegian characters. When trying to run an insert with æøå
    the same error message is recieved:

    unixODBCIBMiSeries Access ODBC DriverInvalid string or buffer length., SQL state S1090 in SQLExecDirect

    We have tried using iconv to convert the sql insert statement to utf-8, iso-8859-1, iso-8859-15, even Windows-1252, ebcdic-dk-no and ebcdic-dk-no-a! None of which works. Utf-8 works flawlessly without the foreign characters.

    The db2 already contains information about users which we use stored procedure calls to retreive just fine, even the ones with Norwegian letters. They seem to be in iso-8859-1.

    We've searched the entire redbook "Linux Integration with IBM i5/OS", and the only character encoding trouble it mentions is for socket programming.

    Input, help and/or suggestions are very welcome.

    Thanks
  • SystemAdmin
    SystemAdmin
    262 Posts

    Re: Invalid string or buffer length.

    ‏2009-01-28T23:36:56Z  
    We have this exact same problem. Did you ever figure this one out? We have a PHP application serving web pages (LAMP stack: Ubuntu Hardy, PHP5) where we collect user information to store in db2 on an as/400 (or ISeries as I believe it is renamed to now). We too communicate with the db2 via unixODBC and ISeries Access for Linux. We're using odbc_exec to run the queries.

    The problem is similar, occationally the data we collect contains Norwegian characters. When trying to run an insert with æøå
    the same error message is recieved:

    unixODBCIBMiSeries Access ODBC DriverInvalid string or buffer length., SQL state S1090 in SQLExecDirect

    We have tried using iconv to convert the sql insert statement to utf-8, iso-8859-1, iso-8859-15, even Windows-1252, ebcdic-dk-no and ebcdic-dk-no-a! None of which works. Utf-8 works flawlessly without the foreign characters.

    The db2 already contains information about users which we use stored procedure calls to retreive just fine, even the ones with Norwegian letters. They seem to be in iso-8859-1.

    We've searched the entire redbook "Linux Integration with IBM i5/OS", and the only character encoding trouble it mentions is for socket programming.

    Input, help and/or suggestions are very welcome.

    Thanks
    Hello,
    I have written a document describing this issue. Please see below. The fix will be a part of iSeries Access for Linux eventually. I have attached the temporary test patches.

    Document Title: CWB0111 when using iSeries Access for Linux to SELECT variant characters.
    Abstract

    Document Description:
    PHP applications that use the iSeries Access for Linux ODBC driver to SELECT CHAR data containing characters outside the invariant character set (for example: "ü"), and using a UTF-8 locale, may receive extra garbage data at the end of the character string.
    This is caused by PHP passing an insufficient target length on the call to SQLBindCol(). PHP is setting the incorrect buffer length by using SQLColAttributes to return the SQL_COLUMN_DISPLAY_SIZE.

    PHP Bug # 47133: php-odbc SQLBindCol() not used correctly
    ...has been submitted.

    IBM support has seen this behavior in PHP versions 5.1 and 5.2.6-2
    This problem also exists in PHP for Windows.

    iSeries Access for Linux versions 5.4 and 6.1 are effected

    The ODBC driver correctly returns error message :
    CWB0111 - A buffer passed to a system call is too small to hold return data

    The ODBC driver has implemented a workaround to not return the extra garbage data at the end of the CHAR string when using a connection string keyword option.
    PHP applications could then ignore the CWB0111 error message.

    For PHP applications using the UTF8 locale to use this workaround and prevent the extra garbage data, the odbc connection must pass in the following connection string keyword:
    DEBUG = 65536
    This can be added to an ODBC DSN in an odbc.ini file.

    Additional options:
    PHP does not calculate all buffer lengths using SQL_COLUMN_DISPLAY SIZE, so, PHP developers could use other SQL column types such as:
    SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY or SQL_LONGVARCHAR. No binding occurs with those data types and SQLGetData() is used to retrieve the value.
    For long data it may be neccessary to call PHP ODBC API odbc_longread() (http://us3.php.net/odbc_longreadlen) , because that is the length to be used for the buffer. If less data is available, PHP shrinks the buffer, but if more data is available, truncation will occur.

    Apar SE33393 describes this defect as encountered using PHP for Windows.