Perl + procedures problem

[JackTheRussel]

Hi.

I started learn procedures (MySQL) and I have following problem:

I create procedure in mysql prompt:

CREATE PROCEDURE department_list()
   SELECT department_name,location FROM departments;

Now my perl program call's procedure and try to print results, but it's not working:

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect( "DBI:mysql:database:localhost:3306",
    "root", "password", { AutoCommit => 0, PrintError => 0, RaiseError => 0 } )
  || die "Connection error: ".$DBI::errstr;

    my $sth = $dbh->prepare('call department_list()') || die $DBI::errstr;
    $sth->execute || die $DBI::errstr;
        while ( my @row = $sth->fetchrow_array ) {
    print join("\t",@row),"\n";
    }
    $sth->finish;
$dbh->disconnect;

It should work, but I got errors:

PROCEDURE procedures.department_list can't return a result set in the given context at ./test.pl line 15.

Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

What should I do ?
any ideas ?

 

[elsenorjose]

Jack, sounds like it's a Perl issue and not a MySQL issue? Have you tried posting in the Perl forum, forum219?

 

[JackTheRussel]

Yes I have.

They told me to send my question here.

 

[hvass]

Need a bit more to go on.

Can you test the procedure on its own using mysql client... then

PROCEDURE procedures.department_list can't return a result set in the given context at ./test.pl line 15.
.. which line of code is line 15

My guess this is nothing to do with this code snippet
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

 

[JackTheRussel]

Hi hvass.

Everything is working fine when I run these procedures in mysql prompt. So the problem is on the perl side.

You are right.
This isn't the real problem:

issuing rollback() for database handle being DESTROY'd without explicit disconnect().