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:
#!/usr/bin/perl -w
use DBI;
use strict;
$|++;
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.
During testing, if I comment out the first fetch, the second fails
properly. My workaround is to change my coding style from
$sh->execute();
my @ar = $sh->fethcrow_array();
to
if ($sh->execute() != 0) {
my @ar = $sh->fetchrow_array();
}
But, I don't think the correct behavior is to return the wrong row.
It looks like (just a guess) that the size of bind_param 1 is getting
truncated during the failed first call, but the below trace doesn't
show that happening.
If I get any extra time (this took long enough to track down) I'll
play with { ado_size => 9 } as a bind_param.
Trace and more details follow.
TIA,
Dave
schema:
CREATE TABLE [dbo].[ProductXRef] (
[prod_num] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[whseloc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[int_prod_num] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [pk_intxref] ON
[dbo].[ProductXRef]([prod_num], [whseloc]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [ix_productxref] ON [dbo].[ProductXRef]([whseloc],
[int_prod_num]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Data: neither of the two requested rows exist in the table. The only
one that exists is the tuple ('7200214','8',NULL).
SQL Server Version 8.00.760 (SP3).
I don't know how to tell which version of ADO is installed on my
machines.
Duplicated on both XP-Pro SP3 and Windows 2003 SP1.
This is perl, v5.8.7 built for MSWin32-x86-multi-thread
(with 14 registered patches, see perl -V for more detail)
DBI::st=HASH(0x1b2e76c) trace level set to 0x0/9 (DBI @ 0x0/0) in DBI
1.53-ithread (pid 2928)
exec 7861700
-> execute for DBD::ADO::st (DBI::st=HASH(0x1b2e394)~0x1b2e76c '7861700'
'7') thr#2247b4
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1105 via at p1.pl line 12
1 <> FETCH= 'select int_prod_num, prod_num, whseloc from ProductXRef where
prod_num = ? and whseloc = ?' ('Statement' from cache) at
C:/perl/site/lib/DBD/ADO.pm line 1105 via at p1.pl line 12
>> bind_param DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima1 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1111 via at p1.pl line 12
1 -> bind_param for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 1 '7861700')
thr#2247b4
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/3 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1065
2 <> FETCH= 2 ('NUM_OF_PARAMS' from cache) at C:/perl/site/lib/DBD/ADO.pm
line 1065
>> trace_msg DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/3 @3 g0 ima5 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1091
2 -> trace_msg in DBD::_::common for DBD::ADO::st
(DBI::st=HASH(0x1b2e76c)~INNER ' -- Type : 200 7
' 5) thr#2247b4
-- Type : 200 7
2 <- trace_msg= 1 at C:/perl/site/lib/DBD/ADO.pm line 1091
1 <- bind_param= 1 at C:/perl/site/lib/DBD/ADO.pm line 1111 via at p1.pl
line 12
>> bind_param DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima1 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1111 via at p1.pl line 12
1 -> bind_param for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 2 '7')
thr#2247b4
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/3 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1065
2 <> FETCH= 2 ('NUM_OF_PARAMS' from cache) at C:/perl/site/lib/DBD/ADO.pm
line 1065
>> trace_msg DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/3 @3 g0 ima5 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1091
2 -> trace_msg for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER ' -- Type
: 200 1
' 5) thr#2247b4
-- Type : 200 1
2 <- trace_msg= 1 at C:/perl/site/lib/DBD/ADO.pm line 1091
1 <- bind_param= 1 at C:/perl/site/lib/DBD/ADO.pm line 1111 via at p1.pl
line 12
>> STORE DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima41c
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1186 via at p1.pl line 12
1 -> STORE for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'Statement'
'select int_prod_num, prod_num, whseloc from ProductXRef where prod_num = ? and
whseloc = ?') thr#2247b4
1 <- STORE= 'select int_prod_num, prod_num, whseloc from ProductXRef where
prod_num = ? and whseloc = ?' at C:/perl/site/lib/DBD/ADO.pm line 1186 via at
p1.pl line 12
>> STORE DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima41c
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1187 via at p1.pl line 12
1 -> STORE for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'NUM_OF_FIELDS' 3)
thr#2247b4
STORE DBI::st=HASH(0x1b2e76c) 'NUM_OF_FIELDS' => 3
1 <- STORE= 1 at C:/perl/site/lib/DBD/ADO.pm line 1187 via at p1.pl line 12
>> STORE DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima41c
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1188 via at p1.pl line 12
1 -> STORE for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'Active' 1)
thr#2247b4
STORE DBI::st=HASH(0x1b2e76c) 'Active' => 1
1 <- STORE= 1 at C:/perl/site/lib/DBD/ADO.pm line 1188 via at p1.pl line 12
<- execute= '0E0' at p1.pl line 12
fetch 7861700
-> fetchrow_array in DBD::_::st for DBD::ADO::st
(DBI::st=HASH(0x1b2e394)~0x1b2e76c) thr#2247b4
>> fetch DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/1 @1 g0 ima0 pid#2928)
at p1.pl line 14
1 -> fetch for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER) thr#2247b4
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1209 via at p1.pl line 14
2 -> FETCH for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'Active')
thr#2247b4
.. FETCH DBI::st=HASH(0x1b2e76c) 'Active' = 1
2 <- FETCH= 1 at C:/perl/site/lib/DBD/ADO.pm line 1209 via at p1.pl line 14
>> finish DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @1 g0 ima1 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1216 via at p1.pl line 14
2 -> finish for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER) thr#2247b4
2 <- finish= 1 at C:/perl/site/lib/DBD/ADO.pm line 1216 via at p1.pl line 14
1 <- fetch= undef row-1 at p1.pl line 14
<- fetchrow_array= ( ) [0 items] row-1 at p1.pl line 14
exec 720021448
-> execute for DBD::ADO::st (DBI::st=HASH(0x1b2e394)~0x1b2e76c '720021448'
'8') thr#2247b4
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1105 via at p1.pl line 16
1 <> FETCH= 'select int_prod_num, prod_num, whseloc from ProductXRef where
prod_num = ? and whseloc = ?' ('Statement' from cache) at
C:/perl/site/lib/DBD/ADO.pm line 1105 via at p1.pl line 16
>> bind_param DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima1 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1111 via at p1.pl line 16
1 -> bind_param for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 1
'720021448') thr#2247b4
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/3 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1065
2 <> FETCH= 2 ('NUM_OF_PARAMS' from cache) at C:/perl/site/lib/DBD/ADO.pm
line 1065
>> trace_msg DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/3 @3 g0 ima5 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1091
2 -> trace_msg for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER ' -- Type
: 200 9
' 5) thr#2247b4
-- Type : 200 9
2 <- trace_msg= 1 at C:/perl/site/lib/DBD/ADO.pm line 1091
1 <- bind_param= 1 at C:/perl/site/lib/DBD/ADO.pm line 1111 via at p1.pl
line 16
>> bind_param DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima1 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1111 via at p1.pl line 16
1 -> bind_param for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 2 '8')
thr#2247b4
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/3 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1065
2 <> FETCH= 2 ('NUM_OF_PARAMS' from cache) at C:/perl/site/lib/DBD/ADO.pm
line 1065
>> trace_msg DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/3 @3 g0 ima5 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1091
2 -> trace_msg for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER ' -- Type
: 200 1
' 5) thr#2247b4
-- Type : 200 1
2 <- trace_msg= 1 at C:/perl/site/lib/DBD/ADO.pm line 1091
1 <- bind_param= 1 at C:/perl/site/lib/DBD/ADO.pm line 1111 via at p1.pl
line 16
>> STORE DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima41c
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1186 via at p1.pl line 16
1 -> STORE for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'Statement'
'select int_prod_num, prod_num, whseloc from ProductXRef where prod_num = ? and
whseloc = ?') thr#2247b4
1 <- STORE= 'select int_prod_num, prod_num, whseloc from ProductXRef where
prod_num = ? and whseloc = ?' at C:/perl/site/lib/DBD/ADO.pm line 1186 via at
p1.pl line 16
>> STORE DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima41c
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1187 via at p1.pl line 16
1 -> STORE for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'NUM_OF_FIELDS' 3)
thr#2247b4
1 <- STORE= 3 at C:/perl/site/lib/DBD/ADO.pm line 1187 via at p1.pl line 16
>> STORE DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @3 g0 ima41c
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1188 via at p1.pl line 16
1 -> STORE for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'Active' 1)
thr#2247b4
STORE DBI::st=HASH(0x1b2e76c) 'Active' => 1
1 <- STORE= 1 at C:/perl/site/lib/DBD/ADO.pm line 1188 via at p1.pl line 16
<- execute= -1 at p1.pl line 16
fetch 720021448
-> fetchrow_array for DBD::ADO::st (DBI::st=HASH(0x1b2e394)~0x1b2e76c)
thr#2247b4
>> fetch DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/1 @1 g0 ima0 pid#2928)
at p1.pl line 18
1 -> fetch for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER) thr#2247b4
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1209 via at p1.pl line 18
2 -> FETCH for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'Active')
thr#2247b4
.. FETCH DBI::st=HASH(0x1b2e76c) 'Active' = 1
2 <- FETCH= 1 at C:/perl/site/lib/DBD/ADO.pm line 1209 via at p1.pl line 18
>> FETCH DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @2 g0 ima404
pid#2928) at C:/perl/site/lib/DBD/ADO.pm line 1251 via at p1.pl line 18
2 -> FETCH for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER 'ChopBlanks')
thr#2247b4
.. FETCH DBI::st=HASH(0x1b2e76c) 'ChopBlanks' = ''
2 <- FETCH= '' at C:/perl/site/lib/DBD/ADO.pm line 1251 via at p1.pl line 18
>> _set_fbav DISPATCH (DBI::st=HASH(0x1b2e76c) rc1/2 @2 g0 ima0 pid#2928)
at C:/perl/site/lib/DBD/ADO.pm line 1254 via at p1.pl line 18
2 -> _set_fbav in DBD::_::st for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER
ARRAY(0x1b2d290)) thr#2247b4
dbih_setup_fbav for 3 fields => 0x1b2efdc
2 <- _set_fbav= [ undef '7200214' '8' ] at C:/perl/site/lib/DBD/ADO.pm line
1254 via at p1.pl line 18
1 <- fetch= [ undef '7200214' '8' ] row1 at p1.pl line 18
<- fetchrow_array= ( undef '7200214' '8' ) [3 items] row1 at p1.pl line 18
failed
disco
-> DESTROY for DBD::ADO::st (DBI::st=HASH(0x1b2e76c)~INNER) thr#2247b4
<- DESTROY= undef
