asked on

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

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

Nem Schlecht

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?

ASKER CERTIFIED SOLUTION

Nem Schlecht

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MGDFresh

ASKER

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.

MGDFresh

ASKER

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