Denis BUCHER wrote:
> 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
>
>
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 :-)
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
