Le vendredi 02 février 2007 à 18:29 -0500, David N Murray a écrit :
> Greetings,
>
> For years, I've used the following construct for indexed, single-row
> retrievals:
>
> $sh = $dbh->prepare("select col from tbl where key = ?");
> $sh->execute();
> @ar = $sh->fetchrow_array();
> if ($#ar > -1) {...
>
> I got bit today by a problem either in ADO.pm (2.96) or ADO itself.
> Very small sample program to reproduce problem:
>
[SNIP]
> my $wbh = DBI->connect("dbi:ADO:Provider=SQLOLEDB;Data
> Source=localhost;Initial Catalog=db", 'user', 'pass', { RaiseError => 0,
> PrintError => 0 });
> die "connect failed: " . DBI::errstr . "\n" if ! $wbh;
>
> my $sh = $wbh->prepare("select int_prod_num, prod_num, whseloc from
> ProductXRef where prod_num = ? and whseloc = ?");
> $sh->trace(9);
> print "\nexec 7861700\n\n";
> my $r = $sh->execute('7861700','7'); # row does not exist
> print "\n\nfetch 7861700\n\n";
> my @ar = $sh->fetchrow_array();
> print "\n\nexec 720021448\n\n";
> $r = $sh->execute('720021448','8'); # row does not exist
> # however 7200214,8 DOES exist
> print "\n\nfetch 720021448\n\n";
> my @br = $sh->fetchrow_array();
> print "\nfailed\n\n" if ($br[1] && $br[1] ne '720021448');
> print "\n\ndisco\n";
> $wbh->disconnect();
>
> My problem is, the second fetch returns the wrong row
> (prod_num=7200214). In the program that does the meaningful work, I
> have a check to see if the row doesn't exist (by looking at $#br), but
> that succeeds, with the wrong data. A subsequent update statement (on
> a different handle) then fails because the row is not in the table.
[SNIP]
David,
I have been bitten by this. It is, if I remember correctly, cause by
a bug somewhere within ADO. Searching in the list archives should give
you further info.
What is happening, when you use parametric queries, is that the "length"
of the parameters becomes "stuck" to the length of the first parameters
executed. This results in the subsequent parameters being truncated
during calls... and giving you "incorrect" results.
You have noticed this already.
What I do, is that I force a first execute using parameters with lengths
equal to the maximum size of the column, i.e from your example & schema:
$sh->execute( '0' x 50 , '0' x50 );
Note that this issue is hinted in the CAVEATS, ADO Providers part of
DBD::ADO
Cheers,
Olivier
