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;

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 ?

 

[MikeLacey]

Hi there,

Try calling $dbh->disconnect after the finish()

I'd have a look here forum436 for MySQL questions.

Mike

The options are: fast, cheap and right - pick any two.. &

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[Corwinsw]

Is calling procedures this way faster than writing SQL queries in Perl?

Corwin

 

[PaulTEG]

It depends a lot on the parameterisation you need, and what sort of functionality you want to deliver. There is a minimum version of MySQL required to support procedures

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[JackTheRussel]

Ok. Thanks.