Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 22036
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: jeroen [...] transactgroup.net
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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>
Download (untitled) / with headers
text/plain 5.4k
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
Download (untitled) / with headers
text/plain 442b
This has been fixed in the latest release, 4.00. The big part of the problem was not toggling to emulated prepared properly when using stored procedures. Prepared statements have been turned off by default in the latest DBD::mysql, as other MySQL connectors have been done until issues in the prepared statement API have been resolved. By using emulated mode, you should not have any problems using stored procedures. Thanks for the report!


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.