stored procedures

Can't get em to work correctly

See example.

Which works fine as it is, but comment out 

$dbh->do('drop procedure if exists someproc') or die $DBI::errstr;

and

$dbh->do("$sql") or die $DBI::errstr;

After all we already created it on the first run, but it doesn't work
correctly with those lines commented out.

This is with DBD-mysql-3.0004_1.tar.gz, DBI 1.50, mysql
libs from v 5.0.20a

What am I doing wrong?  And a more complex example that actually selects
some data from a table (using a stored procedure) and returns it, only
returns the column headings, but for both (using ethereal) I can see the
data is returned from the server to the client (perl DBD), but I can't
seem to successfully retrieve it


Mark.


example
#!/usr/bin/perl -w

use DBI;

$db = 'db5';
$host = 'mas-data01';
$user = 'root';
$password = 'frednirk';

my ($rowset, $i);

$dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
                             $user, $password, {RaiseError => 1});

$dbh->do('drop procedure if exists someproc') or die $DBI::errstr;

$sql = q{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};

$dbh->do("$sql") or die $DBI::errstr;

$sth=$dbh->prepare('call someproc()') or die $DBI::err.":
".$DBI::errstr;
$sth->execute() or die DBI::err.": ".$DBI::errstr; $rowset=0;
do {
        print "\nRowset
".++$i."\n---------------------------------------\n\n";
        foreach $colno (0..$sth->{NUM_OF_FIELDS}-1) {
                print $sth->{NAME}[$colno]."\t";
        }
        print "\n";
        while (@row= $sth->fetchrow_array())  {
                foreach $field (0..$#row) {
                        print $row[$field]."\t";
                }
                print "\n";
        }
} until (!$sth->more_results);

$sth->finish();
$dbh->disconnect();
exit(0);
 
 
This e-mail message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information of Transaction Network Services.
Any unauthorized review, use, disclosure or distribution is prohibited.  If you 
are not the intended recipient, please contact the sender by reply e-mail and 
destroy all copies of the original message.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    
http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@xxxxxxxxxxx