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

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

    Re: Invalid string or buffer length.

    ‏2008-06-10T16:54:14Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Invalid string or buffer length.

      ‏2008-06-10T17:10:11Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Invalid string or buffer length.

        ‏2008-06-10T17:39:33Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Invalid string or buffer length.

          ‏2008-06-10T18:35:35Z  in response to SystemAdmin
          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
            ACCEPTED ANSWER

            Re: Invalid string or buffer length.

            ‏2008-06-11T02:22:59Z  in response to SystemAdmin
            I need the code page being used in the MySQL table and fields and the same for the DB2 tables.
  • SystemAdmin
    SystemAdmin
    262 Posts
    ACCEPTED ANSWER

    Re: Invalid string or buffer length.

    ‏2009-01-21T11:39:02Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Invalid string or buffer length.

      ‏2009-01-28T23:36:56Z  in response to SystemAdmin
      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.