| Subject: | multiple execute and fetch in nested loop producing wrong values |
When attempting to loop over values returned from one statement to
execute against a statement twice for two different values, the
fetchrow_arrayref returns the same exact data and the memory address is
the same for each reference. I have prepared a test code that produces
the problem. I have found it to be broken on both a perl linux
distribution and a strawberry perl distribution both 5.8.x . The test
should be run first with the --create to generate the sqlite then with
the --load to populate the data. Finally run it with the --test to
generate the file report.csv. You will see the following results in the
file
1 51 Mr. 51 51 Main Street
1 51 Mr. 51 51 Main Street
2 52 Mr. 52 52 Main Street
2 52 Mr. 52 52 Main Street
But you should see the results as
1 1 Mr. 1 1 Main Street
1 51 Mr. 51 51 Main Street
2 2 Mr. 2 2 Main Street
2 52 Mr. 52 52 Main Street
| Subject: | sqlt.pl |
#!/usr/bin/perl
use DBI;
use IO::File;
use Getopt::Long;
my $args = {};
my $ok = GetOptions (
'create' => \$args->{CREATE},
'load' => \$args->{LOAD},
'test' => \$args->{TEST}
);
my $dbh = connection();
if(defined($args->{CREATE})) {
$dbh->do(qq{ CREATE TABLE IF NOT EXISTS applicants (
id INTEGER PRIMARY KEY NOT NULL,
title VARCHAR(80),
street1 VARCHAR(36)
)});
$dbh->do(qq{ CREATE TABLE IF NOT EXISTS matches (
id INTEGER PRIMARY KEY NOT NULL,
applicant_id1 INTEGER NOT NULL,
applicant_id2 INTEGER NOT NULL,
score NUMERIC(20,3)
)});
$dbh->do(qq{CREATE INDEX IF NOT EXISTS ma_app1_idx ON matches(applicant_id1)}) or die($dbh->errstr);
$dbh->do(qq{CREATE INDEX IF NOT EXISTS ma_app2_idx ON matches(applicant_id2)}) or die($dbh->errstr);
}
if(defined($args->{LOAD})) {
my $ith = $dbh->prepare(qq{INSERT INTO applicants (title,street1) VALUES(?,?)}) or die($dbh->errstr);
my $jth = $dbh->prepare(qq{INSERT INTO matches (applicant_id1,applicant_id2,score) VALUES(?,?,?)}) or die($dbh->errstr);
my $title = 'Mr. ';
my $street = ' Main Street';
my $ft = "";
my $fs = "";
foreach my $i (1..100) {
$ft = $title . $i;
$fs = $i . $street;
$ith->execute($ft,$fs);
}
$ith->finish() if $ith;
my $off = 50;
foreach my $i (1..50) {
$jth->execute($i,$off+$i,100);
}
$jth->finish() if $jth;
}
if(defined($args->{TEST})) {
print "REPORTING\n";
my $fh = IO::File->new('report.csv',">") or die($!);
my $mth = $dbh->prepare(qq{SELECT id,applicant_id1,applicant_id2 from matches where id < ? order by id}) or die($dbh->errstr);
my $sth = $dbh->prepare(qq{SELECT id,title,street1 from applicants where id=?}) or die($dbh->errstr);
my $ar = [];
my $br = [];
my $cr = [];
$mth->execute(3);
my ($a1,$a2);
while($ar = $mth->fetchrow_arrayref) {
$a1 = $ar->[1];
$a2 = $ar->[2];
$sth->execute($a1);
$br = $sth->fetchrow_arrayref;
$sth->execute($a2);
$cr = $sth->fetchrow_arrayref;
print {$fh} $ar->[0] . ','. join(',',@$br) . "\n";
print {$fh} $ar->[0] . ','. join(',',@$cr) . "\n";
}
$mth->finish() if $mth;
$sth->finish() if $sth;
$fh->close() if $fh;
print "DONE REPORTING\n";
}
sub connection {
my $dbh = DBI->connect("dbi:SQLite:dbname=test.sqlt","","") or die($!);
$dbh->do(qq{PRAGMA synchronous=OFF;}) or die($dbh->errstr);
$dbh->do(qq{PRAGMA temp_store=MEMORY;}) or die($dbh->errstr);
return $dbh;
}