Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: value is too long in an INSERT

    Hi,
    I'm working with DB2 10.1 on Windows.
    My application remotely connects to a SQL Server database and reads a VARCHAR(7936) field with a simple select, then writes this string into a local DB2 database, in a VARCHAR(7936) field.
    The local INSERT statement fails with the message

    [IBM][CLI Driver][DB2/NT64] SQL0433N Value "<beginning of the string>" is too long. SQLSTATE=22001

    If I alter the target table to hold a VARCHAR(8000) field, the INSERT works and il I run
    SELECT LENGTH(Field) from MYTABLE
    I see the last record inserted has a length of 7994.

    How is that possible?

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I would confirm the length of the string from the SQL Server Database. Then check to see if the application is doing anything to it (e.g. appending data). Then I would check for code page conversion issues.

    Andy

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    Thanks Andy,
    I checked the first 2 points, the data length on the source is 7936 and the application doesn't append anything.

    About code pages, DB2 is installed with the default one which is UTF-8, I don't know SQL Server, but I guess that db was created using the local codepage.
    Could this be the issue?
    If it is, how can make sure this problem doesn't occurr any more?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I cannot say anything about SQL Server, but for UTF-8, it can take several bytes to contain 1 character. This maybe your problem. Can you list the SQL Server and DB2 data in hex to see where the difference is?

    Andy

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ARWinner View Post
    I cannot say anything about SQL Server, but for UTF-8, it can take several bytes to contain 1 character. This maybe your problem. Can you list the SQL Server and DB2 data in hex to see where the difference is?

    Andy
    Most DBMS declare the number of characters, but db2 instead declares the number of bytes. If the string contains characters that cannot be represented by one char in UTF-8 there will be a mismatch in how many characters that will fit into a VARCHAR(x) variable.
    --
    Lennart

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by lelle12 View Post
    If the string contains characters that cannot be represented by one char in UTF-8 there will be a mismatch in how many characters that will fit into a VARCHAR(x) variable.
    So try vargraphic. Explained here: The problem with VARGRAPHIC data type Philip Carrington
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  7. #7
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by dr_te_z View Post
    Beside beeing proprietary for DB2, UCS-2 will waste a lot of space (at least in the western hemisphere). From my understanding there is some work in progress to create a UTF-8 string type that better conforms to other vendors, but whether that will result in anything and if so when, is unknown to me.
    --
    Lennart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •