Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Perl + procedures problem

Status
Not open for further replies.

JackTheRussel

Programmer
Aug 22, 2006
110
FI
Hi.

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

I create procedure in mysql prompt:

Code:
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:

Code:
#!/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:

Code:
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 ?
 
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.. [orientalbow] & [anakin]

Want great answers to your Tek-Tips questions? Have a look at faq219-2884
 
Is calling procedures this way faster than writing SQL queries in Perl?

Corwin
 
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 ;-)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top