[IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001

[joel.b...@mutualofomaha.com]

We have a VB6 application using ADO version 2.5 and I am receiving a
"[IBM][CLI Driver] CLI0109E String data right truncation.
SQLSTATE=22001" error when I execute the rs.UpdateBatch method. I have
determined that the error is caused by an extended ascii character 164
(ñ). Has anyone else encountered this problem and found a solution.

TIA

Joel Brewster
Mutual Of Omaha

[Visu]

Check to make sure you are not inserting a value bigger than the field
can hold.

You might get this error when u try to insert anything more than 10
chars into a field which is varchar(10)

[Rhino]

Considering the letter that is being processed when you encounter the
error, you may be having a code page problem of some kind. Unfortunately,
I've never had much to do with code pages and encoding so I can't really
suggest how to resolve the problem, other than to use the Search Engine in
the DB2 Info Center to look up "codepage" and/or "encoding".

But I suspect that Visu is right when he suggests that the string may simply
be too long for the column in which you are trying to put it; that's
certainly what the error message implies. You could probably confirm that
fairly easily by changing the data for the problem row to contain only
standard ASCII characters and then seeing if the same error occurs. If it
does, it's probably a genuine truncation error; if it doesn't the truncation
error was probably masking the *real* problem, which was an invalid
character in the data.

Rhino

<joel.b...@mutualofomaha.com> wrote in message
news:1111096675....@f14g2000cwb.googlegroups.com...

We have a VB6 application using ADO version 2.5 and I am receiving a
"[IBM][CLI Driver] CLI0109E String data right truncation.
SQLSTATE=22001" error when I execute the rs.UpdateBatch method. I have
determined that the error is caused by an extended ascii character 164

(ń). Has anyone else encountered this problem and found a solution.

[Mark Yudkin]

We insert german diacriticals from VB6 over ADO all the time (Windows in
English, DB2 code page for Switzerland (German)). Your error message is odd:
"...[CLI Driver]...". Post the exact definition of your ADO configurationa
and connection string, as well as the codepage configurations of both your
Windows and your DB2 database (i.e. is your ñ supported?). Also - especially
if you're on DB2 V7.x or earlier - try using the MS ADO over ODBC driver in
conjunction with the IBM ODBC driver.

<joel.b...@mutualofomaha.com> wrote in message
news:1111096675....@f14g2000cwb.googlegroups.com...

[RdR]

Hi Joel,

Two things I can suggest to look for among others suggested by others in
this newsgroup:

1) I saw some issues when the System code page is different from the
Database code page. Are you using double byte characters (Chinese, Japanese,
etc.)? I saw a similar situation when Windows has an English code page but
DB2 is using Traditional Chinese (DBCS).

2) I do not know what the applicaiton does but if it writes a DB2 Timestamp
to a SQL Server Datetime field, the DB2 Timestamp is longer than the SQL
Server Datetime field.

Hope this helps,

RdR

<joel.b...@mutualofomaha.com> wrote in message
news:1111096675....@f14g2000cwb.googlegroups.com...

We have a VB6 application using ADO version 2.5 and I am receiving a
"[IBM][CLI Driver] CLI0109E String data right truncation.
SQLSTATE=22001" error when I execute the rs.UpdateBatch method. I have
determined that the error is caused by an extended ascii character 164

(ń). Has anyone else encountered this problem and found a solution.

[ClimberBear]

Hi,

I've the same error, but I don't have solution yet.

I'm using ADO 2.5, and OLE DB for ODBC and IBM ODBC driver, attached to a
DB2 7 on OS/390

I have a clue (maybe) for you. If I connect to a local or remote UDB
database (V7 too), it works fine, but when I connect to MVS DB2 (EBCDIC) it
fails. Please, if you find a solution, let me know.

I have another strange symptom. If I do un update to a field that PREVIOUSLY
had a 'Ñ', even if I remove the 'Ñ', it fails (yes, it's true, we have
checked several times).

P.S: Really, we are not using rs.UpdateBatch. We are using a DataGrid
directly.

Jose Manuel

"Mark Yudkin" <myudkinATcom...@boing.org> wrote in message
news:423c7a98$0$1151$5402...@news.sunrise.ch...

[joel.b...@mutualofomaha.com]

All,

I still do not have a solution, but I looking into some of the
suggestions that have been made. Thank you very much for your
responses. Here is some additional information that I did not include
originally:
1. The error message that I posted is exactly what came from the
driver, but I do think that this error is not the *real* problem.
2. I don't think it is the size of the field causing that problem
because I change the ñ to an n and everything works fine (This is
assuming that the translated values for each of these characters are
the same size).
3. I am able to write a SQL statement that will insert the ñ value
into the field via a pass through query, thus making me think it is not
a DB2 issue, but I will check with a DBA to see what code pages are
supported.
4. We are using DB2 V7.1 OS/390, ADO 2.5, and IBM DB2 ODBC Driver V
7.01.00.65.
5. The driver is set up to do timestamps as char(26) and this is
functioning correctly.
6. We are using a DSN connection that looks like the following:
"DSN=DBT1;UID=XXX;PWD=XXX"

[Mark Yudkin]

What are the Host and the Windows codepages? The character mapping should be
handled by the DRDA protocol in your DB2 Connect setup.

BTW, your best bet is to open a PMR with IBM rather than to ask here. That
way, you get proper attention.

(P.S. If you'd said OS/390 from the beginning, I wouldn't have assumed you
were talking pure UDB on Windows)

<joel.b...@mutualofomaha.com> wrote in message
news:1111429094.0...@z14g2000cwz.googlegroups.com...

[joel.b...@mutualofomaha.com]

Mark,

Thanks for the response. You are correct, I should have included more
information to begin with, sorry. The windows code page= 437 and the
DBA sent me the following for the DB2 code pages

SCCSID=37, EBCDIC CODED CHAR SET
MCCSID=65534, EBCDIC CODED CHAR SET
GCCSID=65534, EBCDIC CODED CHAR SET
ASCCSID=1252, ASCII CODED CHAR SET
AMCCSID=65534, ASCII CODED CHAR SET
AGCCSID=65534, ASCII CODED CHAR SET

I have run a couple of additional tests since my last post though that
lead me to think that the problem lies within the application that
creates the dbf files. Here are the tests that I ran.
1. I was able to successfully insert a record into the DB2 table via a
standard ODBC call. (eg. - "INSERT INTO <table> (eename) VALUES
(''Ordoñez');
2. I was able to successfully insert a record into the DB2 table via a
stored procedure call.

It appears as though the Foxpro application that creates this file may
have some issues because when I look at the field via the user
interface, it displays a strange character in place of the ñ. I will
have to do some more research.

Thanks
Joel

[joel.b...@mutualofomaha.com]

All,

We found a solution if you are interested. We upgraded our driver
version from DB2 V7.1.0.65 to DB2 V8.1.6.705. Hopefully this will help
anyone else that has a similar problem. Thank you all for your
responses.

Joel

[Mark Yudkin]

Weel you got there.
<joel.b...@mutualofomaha.com> wrote in message
news:1111690430....@l41g2000cwc.googlegroups.com...