CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation]

 



magix
Novice

Aug 3, 2009, 8:30 AM

Post #1 of 9 (7762 views)
Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] Can't Post

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 :

- Masquer le texte des messages précédents -

Code
> 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


FishMonger
Veteran / Moderator

Aug 3, 2009, 9:10 AM

Post #2 of 9 (7759 views)
Re: [magix] Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] [In reply to] Can't Post

You didn't execute the sql statement.


Code
$sth = $dbh->prepare($sql) or die...  
$sth->execute;

do {



magix
Novice

Aug 3, 2009, 9:53 AM

Post #3 of 9 (7755 views)
Re: [FishMonger] Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] [In reply to] Can't Post

Hello FishMonger,

Sorry I forgot this line in my post, but in my script the line is present. Maybe I should have added that I get 27 rows of result out of 126 before the error happpens...

Thanks a lot for your help...

Denis


1arryb
User

Aug 4, 2009, 8:25 AM

Post #4 of 9 (7742 views)
Re: [magix] Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] [In reply to] Can't Post

Hi magix,

Try it w/o the outer loop, which I don't think is necessary.


Code
#!/usr/bin/perl 

use strict;
use warnings;

use DBI;

# Define $dsnname, $dbuser, $dbpwd;
...

my $dbh = DBI->connect('dbi:ODBC:' . $dsnname, $dbuser, $dbpwd) or ...
$sth = $dbh->prepare($sql) or die...
$sth->execute();
my $line = 1;
# fetch and print each row in array
while (my $row = $sth->fetchrow_arryref()) {
print $line++, ": ", join(', ', @$row), "\n";
}


Cheers,

Larry


magix
Novice

Aug 4, 2009, 9:56 AM

Post #5 of 9 (7739 views)
Re: [1arryb] Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] [In reply to] Can't Post

Hello,

Thank you for your reply, yes I tried this but not better.
In between someone helped me via private messages and debug logs, and in fact the problem seems to be a bug in the ODBC driver from IBM :

The driver gets 30 UTF8 characters and therefore receives more than 30 bytes, but it don't understand why...

Now, how to solve it I don't know...


FishMonger
Veteran / Moderator

Aug 4, 2009, 10:18 AM

Post #6 of 9 (7738 views)
Re: [magix] Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] [In reply to] Can't Post


Quote
problem seems to be a bug in the ODBC driver from IBM


If that's true, then don't use IBM's module. Download and compile/install the latest module from CPAN.


magix
Novice

Aug 4, 2009, 10:25 AM

Post #7 of 9 (7736 views)
Re: [FishMonger] Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] [In reply to] Can't Post

Do you mean there is a iSeries (AS 400) driver on CPAN ?


FishMonger
Veteran / Moderator

Aug 4, 2009, 11:13 AM

Post #8 of 9 (7732 views)
Re: [magix] Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] [In reply to] Can't Post

I've never done any work on IBM servers, but as I see it, you have a couple options.

1) download the source code from cpan and compile it on a dev box to verify that it works on an IBM server.

http://search.cpan.org/~mjevans/DBD-ODBC-1.22/ODBC.pm

2) contact the module author(s) and ask them about this issue.

You should also file a bug report by whatever means IBM provides.


magix
Novice

Aug 5, 2009, 9:36 AM

Post #9 of 9 (7718 views)
Re: [FishMonger] Problem with perl DBI ODBC driver (bug or misconfig ?) [String data right truncation] [In reply to] Can't Post

Hello everyone,

Finally I found a "solution". It was a bug in the IBM ODBC driver with environment language settings.

Please find all details on :
http://groups.google.ch/group/perl.dbi.users/browse_thread/thread/c885cb9d0c0fc780

Have a nice day, and thanks a lot for your help !

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives