Description:
Using the example database of the 'MySQL Stored Procedure Programming' O'Reilly book Book the following script and stored procedure results into the following error:
1 FERRIS LUCAS
DBD::mysql::st fetchrow_array failed: fetch() but fetch already done at test.pl line 24.
-- ENVIRONMENT ---
$ perl -V
Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
Platform:
osname=linux, osvers=2.6.16-1-vserver-amd64-k8, archname=x86_64-linux-gnu-thread-multi
uname='linux athlon 2.6.16-1-vserver-amd64-k8 #2 smp wed mar 29 05:33:03 utc 2006 x86_64 gnulinux '
config_args='-Dusethreads -Duselargefiles -Dccflags=-DDEBIAN -Dcccdlflags=-fPIC -Darchname=x86_64-linux-gnu -Dprefix=/usr -Dprivlib=/usr/share/perl/5.8 -Darchlib=/usr/lib/perl/5.8 -Dvendorprefix=/usr -Dvendorlib=/usr/share/perl5 -Dvendorarch=/usr/lib/perl5 -Dsiteprefix=/usr/local -Dsitelib=/usr/local/share/perl/5.8.8 -Dsitearch=/usr/local/lib/perl/5.8.8 -Dman1dir=/usr/share/man/man1 -Dman3dir=/usr/share/man/man3 -Dsiteman1dir=/usr/local/man/man1 -Dsiteman3dir=/usr/local/man/man3 -Dman1ext=1 -Dman3ext=3perl -Dpager=/usr/bin/sensible-pager -Uafs -Ud_csh -Uusesfio -Uusenm -Duseshrplib -Dlibperl=libperl.so.5.8.8 -Dd_dosuid -des'
hint=recommended, useposix=true, d_sigaction=define
usethreads=define use5005threads=undef useithreads=define usemultiplicity=define useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=define use64bitall=define uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBIAN -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64',
optimize='-O2',
cppflags='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBIAN -fno-strict-aliasing -pipe -I/usr/local/include'
ccversion='', gccversion='4.0.3 (Debian 4.0.3-1)', gccosandvers=''
intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=12345678
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='cc', ldflags =' -L/usr/local/lib'
libpth=/usr/local/lib /lib /usr/lib
libs=-lgdbm -lgdbm_compat -ldb -ldl -lm -lpthread -lc -lcrypt
perllibs=-ldl -lm -lpthread -lc -lcrypt
libc=/lib/libc-2.3.6.so, so=so, useshrplib=true, libperl=libperl.so.5.8.8
gnulibc_version='2.3.6'
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E'
cccdlflags='-fPIC', lddlflags='-shared -L/usr/local/lib'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT
PERL_MALLOC_WRAP THREADS_HAVE_PIDS USE_64_BIT_ALL
USE_64_BIT_INT USE_ITHREADS USE_LARGE_FILES
USE_PERLIO USE_REENTRANT_API
Built under linux
Compiled at Apr 6 2006 00:36:26
@INC:
/etc/perl
/usr/local/lib/perl/5.8.8
/usr/local/share/perl/5.8.8
/usr/lib/perl5
/usr/share/perl5
/usr/lib/perl/5.8
/usr/share/perl/5.8
/usr/local/lib/site_perl
/usr/local/lib/perl/5.8.7
.
mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.21, for pc-linux-gnu (x86_64) using readline 5.1
Connection id: 86778
Current database: test
Current user: thomas@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.21-Debian_3-log
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 6 days 7 hours 42 min 5 sec
Threads: 2 Questions: 579894 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 61 Queries per second avg: 1.062
---- DBD driver compile settings --------------------
/usr/bin/perl Makefile.PL INSTALLDIRS=vendor
I will use the following settings for compiling and testing:
cflags (mysql_config) = -I/usr/include/mysql -DBIG_JOINS=1
embedded (mysql_config) =
libs (mysql_config) = -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm
mysql_config (guessed ) = mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ps-protocol (default ) = 1
ssl (guessed ) = 0
testdb (default ) = test
testhost (default ) =
testpassword (default ) =
testsocket (default ) =
testuser (default ) =
--------------
How to repeat:
---- stored procedure ---
delimiter $$
drop procedure if exists test_it$$
create procedure test_it(in_sales_rep_id INT)
begin
select employee_id,surname,firstname from employees
where employee_id=in_sales_rep_id;
select employee_id,surname,firstname from employees
where employee_id=in_sales_rep_id;
end$$
delimiter ;
--------------------------
--- script ------------
#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use DBI;
my $dsn = "DBI:mysql:test";
my $dbh = DBI->connect( $dsn, $ARGV[0], $ARGV[1], { AutoCommit => 1,
PrintError => 0,
RaiseError => 1 } );
$dbh->trace(0);
my $sth = $dbh->prepare("call test_it(?)");
$sth->execute(1);
while (my @row = $sth->fetchrow_array){
print join(' ', @row), "\n";
}
$sth->more_results;
while (my @row = $sth->fetchrow_array){
print join(' ', @row), "\n";
}
$sth->finish;
$dbh->disconnect;
---------------------------------------------
Description: Using the example database of the 'MySQL Stored Procedure Programming' O'Reilly book Book the following script and stored procedure results into the following error: 1 FERRIS LUCAS DBD::mysql::st fetchrow_array failed: fetch() but fetch already done at test.pl line 24. -- ENVIRONMENT --- $ perl -V Summary of my perl5 (revision 5 version 8 subversion 8) configuration: Platform: osname=linux, osvers=2.6.16-1-vserver-amd64-k8, archname=x86_64-linux-gnu-thread-multi uname='linux athlon 2.6.16-1-vserver-amd64-k8 #2 smp wed mar 29 05:33:03 utc 2006 x86_64 gnulinux ' config_args='-Dusethreads -Duselargefiles -Dccflags=-DDEBIAN -Dcccdlflags=-fPIC -Darchname=x86_64-linux-gnu -Dprefix=/usr -Dprivlib=/usr/share/perl/5.8 -Darchlib=/usr/lib/perl/5.8 -Dvendorprefix=/usr -Dvendorlib=/usr/share/perl5 -Dvendorarch=/usr/lib/perl5 -Dsiteprefix=/usr/local -Dsitelib=/usr/local/share/perl/5.8.8 -Dsitearch=/usr/local/lib/perl/5.8.8 -Dman1dir=/usr/share/man/man1 -Dman3dir=/usr/share/man/man3 -Dsiteman1dir=/usr/local/man/man1 -Dsiteman3dir=/usr/local/man/man3 -Dman1ext=1 -Dman3ext=3perl -Dpager=/usr/bin/sensible-pager -Uafs -Ud_csh -Uusesfio -Uusenm -Duseshrplib -Dlibperl=libperl.so.5.8.8 -Dd_dosuid -des' hint=recommended, useposix=true, d_sigaction=define usethreads=define use5005threads=undef useithreads=define usemultiplicity=define useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=define use64bitall=define uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBIAN -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64', optimize='-O2', cppflags='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBIAN -fno-strict-aliasing -pipe -I/usr/local/include' ccversion='', gccversion='4.0.3 (Debian 4.0.3-1)', gccosandvers='' intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=12345678 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, prototype=define Linker and Libraries: ld='cc', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib libs=-lgdbm -lgdbm_compat -ldb -ldl -lm -lpthread -lc -lcrypt perllibs=-ldl -lm -lpthread -lc -lcrypt libc=/lib/libc-2.3.6.so, so=so, useshrplib=true, libperl=libperl.so.5.8.8 gnulibc_version='2.3.6' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E' cccdlflags='-fPIC', lddlflags='-shared -L/usr/local/lib' Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT PERL_MALLOC_WRAP THREADS_HAVE_PIDS USE_64_BIT_ALL USE_64_BIT_INT USE_ITHREADS USE_LARGE_FILES USE_PERLIO USE_REENTRANT_API Built under linux Compiled at Apr 6 2006 00:36:26 @INC: /etc/perl /usr/local/lib/perl/5.8.8 /usr/local/share/perl/5.8.8 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.8 /usr/share/perl/5.8 /usr/local/lib/site_perl /usr/local/lib/perl/5.8.7 . mysql> status -------------- mysql Ver 14.12 Distrib 5.0.21, for pc-linux-gnu (x86_64) using readline 5.1 Connection id: 86778 Current database: test Current user: thomas@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.21-Debian_3-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 6 days 7 hours 42 min 5 sec Threads: 2 Questions: 579894 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 61 Queries per second avg: 1.062 ---- DBD driver compile settings -------------------- /usr/bin/perl Makefile.PL INSTALLDIRS=vendor I will use the following settings for compiling and testing: cflags (mysql_config) = -I/usr/include/mysql -DBIG_JOINS=1 embedded (mysql_config) = libs (mysql_config) = -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm mysql_config (guessed ) = mysql_config nocatchstderr (default ) = 0 nofoundrows (default ) = 0 ps-protocol (default ) = 1 ssl (guessed ) = 0 testdb (default ) = test testhost (default ) = testpassword (default ) = testsocket (default ) = testuser (default ) = -------------- How to repeat: ---- stored procedure --- delimiter $$ drop procedure if exists test_it$$ create procedure test_it(in_sales_rep_id INT) begin select employee_id,surname,firstname from employees where employee_id=in_sales_rep_id; select employee_id,surname,firstname from employees where employee_id=in_sales_rep_id; end$$ delimiter ; -------------------------- --- script ------------ #!/usr/bin/perl use strict; use warnings; use Data::Dumper; use DBI; my $dsn = "DBI:mysql:test"; my $dbh = DBI->connect( $dsn, $ARGV[0], $ARGV[1], { AutoCommit => 1, PrintError => 0, RaiseError => 1 } ); $dbh->trace(0); my $sth = $dbh->prepare("call test_it(?)"); $sth->execute(1); while (my @row = $sth->fetchrow_array){ print join(' ', @row), "\n"; } $sth->more_results; while (my @row = $sth->fetchrow_array){ print join(' ', @row), "\n"; } $sth->finish; $dbh->disconnect; ---------------------------------------------