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

Denis BUCHER wrote:
Hello everyone,

Martin Evans a écrit :
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


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 ?

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 LongTrun***
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 LongTrun***, I added this line to my perl script :
$dbh->{LongTrun***}=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.
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



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.

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