How do I return a value from a MySQL stored procedure in DBD::mysql?

MGDFresh
MGDFresh used Ask the Experts™
on
Hi,

I need to return a value from a stored procedure to use elsewhere in my script.  I have tried all sorts of things including binding, but I am not getting the results.  I have a feeling this is a simple thing, but I would really appreciate your help.

Below is my code.  Thank you.


use DBD::mysql;
use strict;

my $database = 'db'; 
my $dbUsername = 'root'; 
my $dbPassword = ''; 
my $dbHostname = 'hostname'; 
my $db = DBI->connect("DBI:mysql:$database:$dbHostname", $dbUsername, $dbPassword) or die "Could not connect to database: " . DBI->errstr;


						my $q1 = $db->prepare(qw{CALL stored_procedure(1,2,3,'cat','dog',112,'sum','avg',\@RETURNVALUE);	});			
						$q2->execute or $db->disconnect() . die "SQL Error: $DBI::errstr\n";
						my $SPvalue = $q2->fetchrow(); # need to return the @RETURNVALUE from SP!!!
						
						print "$SPvalue \n";

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
This should work - there's nothing special about using stored procs & DBD::mysql *unless* your stored procedure is returning multiple result sets (in that case, you just need to call $sth->more_results.

In your code above, you're statement handle is assigned to $q1, but then you are calling execute() and fechrow() on $q2.

I'm assuming that is not actually the problem, though, correct?
IT Supervisor
Top Expert 2009
Commented:
Ah, sorry - needed to scroll to the right a little more.  I'm assuming that this stored proc. doesn't actually return anything (by doing any open SELECT()s), but rather just assigns its output to @RETURNVALUE.  However, (again, guessing) you're never *asking* for @RETURNVALUE.

So, you could either modify your stored proc to do just a "SELECT @RETURNVALUE;" right before it exits (your fetrchow() will then actually get a result), or if that is unacceptable, you just need to create another statement handle to go and get it.

$q1->execute() or $db->disconnect() . die "SQL Error: $DBI::errstr\n";
$q2=$db->prepare("SELECT @RETURNVALUE");
$q2->execute();
$SPvalue = $q2->fetchrow();
$q2->finish();

Author

Commented:
Ahh yes, the $q1 / $q2 is a typo in this only.  That is not the issue.  I will edit it as soon as I figure out how.  thanks for point that out.  

Author

Commented:
Thank you for the quick response.  Your example was accurate and solved my problem.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial