Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation]

215 views
Skip to first unread message

Denis BUCHER

unread,
Aug 3, 2009, 7:19:23 AM8/3/09
to dbi-...@perl.org
Hello,

I'm trying to simply do a SELECT from an ODBC source.
It works perfectly in PHP but not in perl !
Therefore there is no problem at source or at ODBC level, it seems to
reside at perl/DBI level...

What I do :
> use DBI;
> $dbh = DBI->connect('dbi:ODBC:' . $dsnname, $dbuser, $dbpwd) or...
> $sth = $dbh->prepare($sql) or die...
> do {
> my @row;
> my $line=1;
> # fetch each row in array
> while (@row = $sth->fetchrow_array())
> {
> print ($line + 1);
> print ". ";
> # print each field in a row
> for ($i=0;$i<$#row;$i++)
> {
> print $row[$i]
> };
> print "\n";
> $line++;
> }
> # see if there's more records to show
> } while ($sth->{odbc_more_results});


But even if my script is working, most of the time (not always but 80%
of time ???) I get this error :

DBD::ODBC::st fetchrow_array failed: [unixODBC][IBM][System i Access
ODBC Driver]Column 1: CWB0111 - A buffer passed to a system call is too
small to hold return data (SQL-22018) [state was 22018 now 01004]

[unixODBC][IBM][System i Access ODBC Driver]String data right
truncation. (SQL-01004) at ./odbcdemo-perl.pl line n.

I'm not an ODBC expert, not a DBI expert, therefore I'm maybe forgetting
something important to be done, but I don't find anything on the web
that helped me...

Last idea, could it be due to UTF8, which would create difference in
string sizes ?

I found a similar bug in PHP :
http://www-01.ibm.com/support/docview.wss?uid=nas1ac5658703ae5a78b862575440052cbda

And a thread about my problem but without solution :
http://www.ibm.com/developerworks/forums/thread.jspa?threadID=185874&tstart=45

But I don't understand DBI enough to understand what I should do ?

Thanks a lot for any help !

Denis

Denis BUCHER

unread,
Aug 3, 2009, 11:39:43 AM8/3/09
to dbi-...@perl.org
Hello everyone,

Some precisions to my previous email...

> I'm trying to simply do a SELECT from an ODBC source.
> It works perfectly in PHP but not in perl !
> Therefore there is no problem at source or at ODBC level, it seems to
> reside at perl/DBI level...
>
> What I do :
>> use DBI;
>> $dbh = DBI->connect('dbi:ODBC:' . $dsnname, $dbuser, $dbpwd) or...
>> $sth = $dbh->prepare($sql) or die...

>> $sth->execute or die...


>> do {
>> my @row;
>> my $line=1;
>> # fetch each row in array
>> while (@row = $sth->fetchrow_array())
>> {
>> print ($line + 1);
>> print ". ";
>> # print each field in a row
>> for ($i=0;$i<$#row;$i++)
>> {
>> print $row[$i]
>> };
>> print "\n";
>> $line++;
>> }
>> # see if there's more records to show
>> } while ($sth->{odbc_more_results});
>
>
> But even if my script is working, most of the time (not always but 80%
> of time ???) I get this error :
>
> DBD::ODBC::st fetchrow_array failed: [unixODBC][IBM][System i Access
> ODBC Driver]Column 1: CWB0111 - A buffer passed to a system call is too
> small to hold return data (SQL-22018) [state was 22018 now 01004]
>
> [unixODBC][IBM][System i Access ODBC Driver]String data right
> truncation. (SQL-01004) at ./odbcdemo-perl.pl line n.

I must add an important point, I received 47 rows out of the 126 expected

Martin Evans

unread,
Aug 3, 2009, 11:44:16 AM8/3/09
to dbi-...@perl.org

Take a look at the DBI attribute LongReadLen.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Denis BUCHER

unread,
Aug 3, 2009, 12:49:43 PM8/3/09
to Martin Evans, dbi-...@perl.org
Hello Martin,

Martin Evans a �crit :

Yes I already saw people saying this, but I don't understand where to
look and what to look for... (Already searched a lot for this
"LongReadLen" ;-)

Thanks a lot for your help

Denis

Martin J. Evans

unread,
Aug 3, 2009, 1:22:46 PM8/3/09
to dbi-...@perl.org
perldoc DBI then search for LongReadLen or go to
http://search.cpan.org/~timb/DBI-1.609/DBI.pm where you'll find an
online version.
Basically, you need to set LongTruncOk
http://search.cpan.org/~timb/DBI-1.609/DBI.pm#LongTruncOk_(boolean,_inherited)
to say you don't mind column data being truncated or you need to set
LongReadLen
http://search.cpan.org/~timb/DBI-1.609/DBI.pm#LongReadLen_(unsigned_integer,_inherited)
to a value bigger than your largest column data.
You can set these attributes on the connection handle ($dbh in your
example) or on a per statement case ($sth in your example after you call
prepare but before execute).

Martin

Denis BUCHER

unread,
Aug 3, 2009, 2:20:32 PM8/3/09
to Martin J. Evans, dbi-...@perl.org
Hello Martin,

Martin J. Evans a �crit :

>>> Take a look at the DBI attribute LongReadLen.
>>
>> Yes I already saw people saying this, but I don't understand where to
>> look and what to look for... (Already searched a lot for this
>> "LongReadLen" ;-)
>>

> perldoc DBI then search for LongReadLen or go to
> http://search.cpan.org/~timb/DBI-1.609/DBI.pm where you'll find an
> online version.
> Basically, you need to set LongTruncOk
> http://search.cpan.org/~timb/DBI-1.609/DBI.pm#LongTruncOk_(boolean,_inherited)
> to say you don't mind column data being truncated or you need to set
> LongReadLen
> http://search.cpan.org/~timb/DBI-1.609/DBI.pm#LongReadLen_(unsigned_integer,_inherited)
> to a value bigger than your largest column data.
> You can set these attributes on the connection handle ($dbh in your
> example) or on a per statement case ($sth in your example after you call
> prepare but before execute).

Ok, now I understood... Thanks a lot for your explanations, it was like
chinese to me, before... (Way of speaking, as I understand chinese, but
that's OT ;-))

a) First I tried LongTruncOk, I added this line to my perl script :
$dbh->{LongTruncOk}=true;

And there was no error anymore. Good, that's a good start...
But the problem is that I do care about data being truncated ;-)

b) Therefore I wanted to try LongReadLen... In the doc it is said that
default value is 80, therefore I tried many values, but it didn't solve
the problem. Strange because my biggest field is a varchar(30)...

I tried :
$dbh->{LongReadLen}=2000;

And got :


DBD::ODBC::st fetchrow_array failed: [unixODBC][IBM][System i Access
ODBC Driver]Column 1: CWB0111 - A buffer passed to a system call is too
small to hold return data (SQL-22018) [state was 22018 now 01004]
[unixODBC][IBM][System i Access ODBC Driver]String data right

truncation. (SQL-01004) at ./odbcdemo-perl3.pl line 39.

Do you understand what could be the problem ?

Denis

Martin Evans

unread,
Aug 4, 2009, 3:31:46 AM8/4/09
to dbi-...@perl.org

Could you send the following to me (not the list as the log will
probably be too long and removed):

version of unixODBC you are using - odbcinst -j tells you this.

verion and name of the ODBC driver you are using

edit /etc/odbcinst.ini (or wherever your odbcinst.ini is) and add:

[ODBC]
Trace = yes
TraceFile = /tmp/unixodbc.log

to the top of it.

Edit your perl script and add the following to the top of it:

use DBD::ODBC;
DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode'));

Now rerun your script with something like this:

export DBI_TRACE=15=x.log
./myscript.pl

Send me /tmp/unixodbc.log and x.log.

I'll see what I can see :-)

Denis BUCHER

unread,
Aug 5, 2009, 12:32:58 PM8/5/09
to Martin Evans, dbi-...@perl.org
Hello everyone,

Martin Evans a �crit :

> Could you send the following to me (not the list as the log will
> probably be too long and removed):
>
> version of unixODBC you are using - odbcinst -j tells you this.
>
> verion and name of the ODBC driver you are using
>
> edit /etc/odbcinst.ini (or wherever your odbcinst.ini is) and add:
>
> [ODBC]
> Trace = yes
> TraceFile = /tmp/unixodbc.log
> to the top of it.
> Edit your perl script and add the following to the top of it:
> use DBD::ODBC;
> DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode'));
> Now rerun your script with something like this:
> export DBI_TRACE=15=x.log
> ./myscript.pl
> Send me /tmp/unixodbc.log and x.log.

After a lot of help and interesting analysis from Martin, I will try to
explain what the problem is and the solution in very simple and basic
words for anyone that could have the same problem...

The problem is "half-solved", which means we found how to make it work,
but it is a bug in IBM iSeries driver.

I created a post on IBM forums with some more details about the driver
specific informations :
https://www.ibm.com/developerworks/forums/thread.jspa?threadID=271928&tstart=0

Globally the problem is that if you have a environment variable
configured to UTF8, then the driver is working in UTF8 even if it
shouldn't (don't ask me exactly), but in a word, if you get 30
characters containing special characters, it will return 31, 32 bytes
because of utf8 but will speak about 30 bytes and the driver bugs.
(Maybe Martin may explain better or correct me if I'm wrong)

The "solution" is therefore to change the environnement variable.

Example on my server :

# set | grep ^LC_CTYPE
LC_CTYPE=fr_FR.UTF-8

Therefore I did this before launching the script and it worked :
export LC_CTYPE=fr_FR

Hope it will help someone :-)

Denis

Martin Evans

unread,
Aug 6, 2009, 4:08:30 AM8/6/09
to dbi-...@perl.org

The iSeries ODBC driver obviously looks at your environment and decides
that if you have UTF-8 set it will encode data returned from the
database in UTF-8. Denis had UTF-8 set and his data contained some
accented characters. Also, Denis' data was held in char(30) columns so
trailing spaces are returned. When DBD::ODBC queries the driver about
the column it is told it is of size 30 and binds the buffer at size 31
allowing for the trailing NULL. When the data is returned it requires
more than 31 bytes now because UTF-8 encoding has increased the size in
bytes of some chrs. Now you are I know that the iSeries driver meant
that 30 to be 30 characters but ODBC defines it as bytes and even if
ODBC did not say this there are other places where returning UTF-8
encoded data in ODBC is not possible (e.g., SQLGetData provides a buffer
for the returned data and each subsequent call returns more of a columns
data SO LONG as the buffer on the previous call was filled. If you UTF-8
encode the data it may not be possible to put a full chr in it).

In Denis' case we can work around it to a point because he can rtrim the
column to take the spaces off and leave room for the encoding but this
only works because his dataset only contains a few accented chrs and
plenty of trailing spaces. Anyone doing this also needs to know the data
is UTF-8 encoded and decode it. The other alternative is to do what
Denis did and take UTF-8 out of your environment.

I'd hesitate to say this is an iSeries driver bug - more a feature, and
if you were writing your own ODBC code you can work with this scheme so
long as a) you know it is going to UTF-8 encode the data and b) you keep
away from a few difficult calls like SQLGetData. In my opinion,
DBD::ODBC is not one of those applications as it would extremely
difficult to support both schemes. However, if someone wants this badly
enough I'd not stand in the way of them looking at it.

Hope this helps clarify it.

Reply all
Reply to author
Forward
0 new messages