| Subject: | Multi-result set bugs |
| Date: | Wed, 11 Oct 2006 18:40:59 +0100 |
| To: | bug-DBD-mysql [...] rt.cpan.org |
| From: | Jeroen van den Oever <jeroen [...] transactgroup.net> |
Hi,
I was trying to get multi-result sets working (patched as of 3.0007_1),
and I noticed it won't work unless (??) you put the stored procedure
definition in front of your multi-result sets retrieval loop.
Let me demonstrate; run this first (it will be successful, results as
you expect, multiple result sets):
--------snip
# Mostly cut & paste from DBD::mysql 3.0007_1
use DBI;
use strict;
use warnings;
my $connect_string = 'DBI:mysql:database=AWSM;host=localhost;port=3306';
my $username = 'root';
my $password = 'password';
my $attributes = {};
my $dbh = DBI->connect($connect_string, $username,
$password, $attributes) || die $DBI::errstr;
# Execute this AT LEAST once so the stored procedure someproc() exists
# at least
$dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
# Comment this out to reproduce the bug
$dbh->do("create procedure someproc() deterministic begin ".
"declare a,b,c,d int; set a=1; set b=2; set c=3; set d=4; ".
"select a, b, c, d; select d, c, b, a; select b, a, c, d; ".
"select c, b, d, a; end") or print $DBI::errstr;
my $sth=$dbh->prepare('call someproc()') || die $DBI::errstr;
$sth->execute || die $DBI::errstr;
my $i = 0;
do {
print "\nRowset ".++$i."\n---------------------------------------\n
\n";
foreach my $colno (0..$sth->{NUM_OF_FIELDS})
{
print $sth->{NAME}->[$colno]."\t" if
(defined($sth->{NAME}->[$colno]));
}
print "\n";
while (my @row= $sth->fetchrow_array())
{
foreach my $field (0..$#row)
{
print $row[$field]."\t";
}
print "\n";
}
} until (!$sth->more_results());
--------snip
Now, if you run the same script with a few comments added (commented out
the "drop procedure"), it will still work, albeit with a warning about
the stored procedure existing:
--------snip
# Mostly cut & paste from DBD::mysql 3.0007_1
use DBI;
use strict;
use warnings;
my $connect_string = 'DBI:mysql:database=AWSM;host=localhost;port=3306';
my $username = 'root';
my $password = 'password';
my $attributes = {};
my $dbh = DBI->connect($connect_string, $username,
$password, $attributes) || die $DBI::errstr;
# Execute this AT LEAST once so the stored procedure someproc() exists
# at least
#$dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
# Comment this out to reproduce the bug
$dbh->do("create procedure someproc() deterministic begin ".
"declare a,b,c,d int; set a=1; set b=2; set c=3; set d=4; ".
"select a, b, c, d; select d, c, b, a; select b, a, c, d; ".
"select c, b, d, a; end") or print $DBI::errstr;
my $sth=$dbh->prepare('call someproc()') || die $DBI::errstr;
$sth->execute || die $DBI::errstr;
my $i = 0;
do {
print "\nRowset ".++$i."\n---------------------------------------\n
\n";
foreach my $colno (0..$sth->{NUM_OF_FIELDS})
{
print $sth->{NAME}->[$colno]."\t" if
(defined($sth->{NAME}->[$colno]));
}
print "\n";
while (my @row= $sth->fetchrow_array())
{
foreach my $field (0..$#row)
{
print $row[$field]."\t";
}
print "\n";
}
} until (!$sth->more_results());
--------snip
HOWEVER, you now comment out the "create procedure", the whole thing
STOPS working:
--------snip
# Mostly cut & paste from DBD::mysql 3.0007_1
use DBI;
use strict;
use warnings;
my $connect_string = 'DBI:mysql:database=AWSM;host=localhost;port=3306';
my $username = 'root';
my $password = 'password';
my $attributes = {};
my $dbh = DBI->connect($connect_string, $username,
$password, $attributes) || die $DBI::errstr;
# Execute this AT LEAST once so the stored procedure someproc() exists
# at least
#$dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
# Comment this out to reproduce the bug
#$dbh->do("create procedure someproc() deterministic begin ".
# "declare a,b,c,d int; set a=1; set b=2; set c=3; set d=4; ".
# "select a, b, c, d; select d, c, b, a; select b, a, c, d; ".
# "select c, b, d, a; end") or print $DBI::errstr;
my $sth=$dbh->prepare('call someproc()') || die $DBI::errstr;
$sth->execute || die $DBI::errstr;
my $i = 0;
do {
print "\nRowset ".++$i."\n---------------------------------------\n
\n";
foreach my $colno (0..$sth->{NUM_OF_FIELDS})
{
print $sth->{NAME}->[$colno]."\t" if
(defined($sth->{NAME}->[$colno]));
}
print "\n";
while (my @row= $sth->fetchrow_array())
{
foreach my $field (0..$#row)
{
print $row[$field]."\t";
}
print "\n";
}
} until (!$sth->more_results());
--------snip
Guess that's a bug. And explains why multi-result sets don't work in my
code.
Minor stuff about your example:
"create procedure somproc"
Missing 'e', should be "someproc"
"...; $rowset=0;"
Variable '$rowset' is used, but later you use '$i' which is not declared
(I presume you mean ++$rowset later, not "++$i")
"foreach $colno"
fails with "use strict" (should be "foreach my $colno")
"while (@row=..."
fails with "use strict" (should be "while (my @row=...")
"foreach $field"
fails with "use strict" (should be "foreach my $field")
"print $sth->{NAME}->[$colno]."\t""
warns with "use warnings" (append " if
(defined($sth->{NAME}->[$colno]));")
My Environment:
Above is tested on:
Gentoo Linux 2.6.14 r5, SMP x86_64 Intel Xeon
Perl 5.8.8 x86_64 threaded
MySQL 5.0.18, DBD 3.0007_2
-- and --
SUSE Linux 9.0 2.6.13-15.8 SMP i686 Intel Pentium 4
Perl 5.8.7 i586 threaded
MySQL 5.0.24a, DBD 3.0007_2
both platforms produce the same results. Hope that helps
-- Jeroen van den Oever