Hello Martin,
Martin J. 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 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
