Re: Potential problem with DBD::Oracle

On 13/01/11 17:21, Martin J. Evans wrote:
> On 18/11/10 20:24, Martin J. Evans wrote:
>> On 18/11/2010 20:15, Steve Baldwin wrote:
>>> Hi,
>>>
>>> I ran across an issue recently that appears to have existed for quite some
>>> time.
>>>
>>> Consider the following script ...
>>>
>>> #!/usr/bin/perl -w
>>> use strict;
>>> use warnings;
>>> use DBI;
>>>
>>> sub main {
>>>      my $dbh = DBI->connect(
>>>          'dbi:Oracle:',
>>>          'usr/pwd@conn',
>>>          '',
>>>          { PrintError =>  0, AutoCommit =>  0, RaiseError =>  1 },
>>>          );
>>>      my $sql =<<'END_SQL';
>>> SELECT  1 srt, 'AA' txt
>>> FROM    dual
>>> UNION
>>> SELECT  2, 'BBB'
>>> FROM    dual
>>> UNION
>>> SELECT  3, 'CCCC'
>>> FROM    dual
>>> ORDER
>>> BY      1
>>> END_SQL
>>>      my $sth = $dbh->prepare($sql);
>>>      $sth->execute;
>>>      my ($srt, $txt);
>>>      $sth->bind_columns(\($srt, $txt));
>>>      while ($sth->fetch) {
>>>          print "[$srt][$txt] len=" . (length($txt)) . "\n";
>>>      }
>>>      $dbh->disconnect;
>>>      return 0;
>>> }
>>>
>>> exit main();
>>>
>>> Running it produces the following output :
>>>
>>> au-stb-101-144:dev stbaldwin$ ./sb2.plx
>>> [1][AA] len=2
>>> [2][BBB] len=2
>>> [3][CCCC] len=2
>>>
>>> As you can see, even though the data in $txt looks correct, perl thinks the
>>> internal length of the variable is whatever it was after fetching the first
>>> row.  This screws up things like sprintf.
>>>
>>> I'm pretty sure this behaviour is a DBD::Oracle thing rather than a DBI
>>> thing.  I tried an equivalent script with DBD::SQLite and DBD::mysql and
>>> they both returned the length correctly (imo).
>>>
>>> This behaviour exists in 1.23 and 1.26.  I haven't tested any other
>>> versions.
>>>
>>> Thanks,
>>>
>>> Steve
>>
>> I vaguely remember hitting this issue myself some time ago. Perhaps a search of dbi-users or dbi-dev will find something. I'll try and dig out a reference tomorrow.
>>
>> Martin
>>
> 
> I suddenly remembered what is probably causing this as I hit a similar problem in DBD::ODBC. It was failing to set magic on the sv for the bound column. This should probably be reported in the rt system for DBD::Oracle. I don't have time right now to look into it and will probably forget again unless it is rt'ed.
> 
> Martin

Appollogies, it appears John already got a patch to fix this and it has already been committed.

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