How to call mysql stored procedure in perl?

[archana amith]

Hi,

I want to call MySQL stored procedure in perl. I have tried like this:

use DBI;
my $create_procedure = qq{
   CREATE PROCEDURE simpleproc ()
   BEGIN
   SELECT 'helloworld' As Messgae;
   END
};
$dbh->do($create_procedure);
$sql = "CALL simpleproc()";
my $sth = $dbh->prepare($sql);
$sth->execute();

I am getting an error which i am not able to debug. I am not sure if i need to specify delimiter.

DBD::mysql::st execute failed: PROCEDURE test.simpleproc can't return a result s
et in the given context at try.pl line 24.

The output should be like this:

+--------------+
| Message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.00 sec)

How can i get the above output in Perl?

Help is very much appreciated.

Thanks

[d5e5]

I don't see a connect statement in your script. The following works for me.

#!/usr/bin/perl
use strict;
use warnings;
use 5.012;

use DBI;
my ($dbh, $sql, $sth, $msg);

$dbh=DBI->connect('dbi:mysql:daniweb','david','dogfood') || 
   die "Error opening database: $DBI::errstr\n";
   
my $create_procedure = qq{
   CREATE PROCEDURE simpleproc ()
   BEGIN
   SELECT 'helloworld' As Messgae;
   END
};

$dbh->do($create_procedure);
$sql = "CALL simpleproc()";
$sth = $dbh->prepare($sql);
$sth->execute();

while (( $msg) = $sth ->fetchrow_array) {
   print "$msg \n";
}


$sth->finish();

$dbh->disconnect || die "Failed to disconnect\n";

Running the above in Perl gives only helloworld as output. However if you log into a MySQL client directly and call your procedure you will see it is indeed there:

mysql> use daniweb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> call simpleproc();
+------------+
| Messgae    |
+------------+
| helloworld | 
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[archana amith]

Hi,

Thanks for the reply.

The problem why it was not getting executed was the old version of DBD::MySQL i just updated and it just worked for me.

Regards
Archana Amith

[archana amith]

I don't see a connect statement in your script. The following works for me.

#!/usr/bin/perl
use strict;
use warnings;
use 5.012;

use DBI;
my ($dbh, $sql, $sth, $msg);

$dbh=DBI->connect('dbi:mysql:daniweb','david','dogfood') || 
   die "Error opening database: $DBI::errstr\n";
   
my $create_procedure = qq{
   CREATE PROCEDURE simpleproc ()
   BEGIN
   SELECT 'helloworld' As Messgae;
   END
};

$dbh->do($create_procedure);
$sql = "CALL simpleproc()";
$sth = $dbh->prepare($sql);
$sth->execute();

while (( $msg) = $sth ->fetchrow_array) {
   print "$msg \n";
}


$sth->finish();

$dbh->disconnect || die "Failed to disconnect\n";

Running the above in Perl gives only helloworld as output. However if you log into a MySQL client directly and call your procedure you will see it is indeed there:

mysql> use daniweb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> call simpleproc();
+------------+
| Messgae    |
+------------+
| helloworld | 
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Hi,

I am having one more problem. I have a database like this:

mysql> SELECT * FROM DATA;
+------+----------+-----------+
| name | category | price     |
+------+----------+-----------+
| aaa  | animal   | 120000    |
| bbb  | rat      | 129990000 |
| ccc  | bat      | 90000     |
+------+----------+-----------+
3 rows in set (0.00 sec)

I have written program like this:

use DBI;
$dbh = DBI->connect("DBI:mysql:test","root", "ibab",{ PrintError => 0}) || die $DBI::errstr;

my $create_procedure = qq{
   CREATE PROCEDURE simpleproc (OUT param1 INT)
   BEGIN
   SELECT count(*) INTO param1 FROM data;
   END
};
$dbh->do($create_procedure);
$spt = $dbh->prepare("CALL simpleproc(@a)") or die "cannot execute or call this procedure";
$spt->execute();

$sql="select @a";
$stmt=$dbh->do($sql);

while(@row=$stmt->fetchrow_array())
{
	print "\n @row";
}
while(@row=$spt->fetchrow_array())
{
	print "\n @row";
}

But i am not getting any result. where as in mysql when i do

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

I am getting the result.

How to get the above output in perl?

Since i am beginner in stored procedures i am finding difficulty. Help is very much appreciated.

Regards

[d5e5]

Creating the simpleproc procedure from Perl works OK for you, so assuming the simpleproc already exists on your database, you can call it and print the value of the OUT parameter doing something like this:

#!/usr/bin/perl
use strict;
use warnings;

use DBI;

my ($dbh, @a, $nbr, $sql);

$dbh=DBI->connect('dbi:mysql:daniweb','david','dogfood') || 
   die "Error opening database: $DBI::errstr\n";

$sql = "call simpleproc(\@a)";
$dbh->do($sql);
$nbr = $dbh->selectrow_array('SELECT @a');
print "Received nbr from simpleproc is $nbr\n";

$dbh->disconnect || die "Failed to disconnect\n";

I got this from the "workaround" posted at the end of this thread at StackOverflow.

You may also try substituting the following snippet to get the field name as well as the value:

$sql = "call simpleproc(\@a)";
$dbh->do($sql);
$hash_ref = $dbh->selectrow_hashref('SELECT @a');
my ($hdr) = keys %{$hash_ref};
$nbr = $hash_ref->{$hdr};
print "FieldName is $hdr\n";
print "Returned value is $nbr\n";
use Data::Dumper;
print Dumper($hash_ref);

[archana amith]

Hi,

Thanks for the mail.

I am again facing problem in INOUT parameter.

Here is the code:

use DBI;

$dbh = DBI->connect("DBI:mysql:test","root", "ibab",{ PrintError => 0}) || die $DBI::errstr;

my $create_procedure = qq{
		
	CREATE PROCEDURE oper(IN a FLOAT, INOUT b FLOAT, IN oper VARCHAR(1))
BEGIN

  CASE oper
  WHEN '+' THEN SET b := a + b;
  WHEN '-' THEN SET b := a - b;
  WHEN '*' THEN SET b := a * b;
  WHEN '/' THEN SET b := a / b;
  WHEN '%' THEN SET b := a % b;
  WHEN '^' THEN SET b := POW(a, b);
  ELSE SET b := a;
  END CASE;
  SELECT b;
  END 
};

Actually i have to set b to 5 i.e

SET @b:=5

But if i include this with in the procedure i am getting an error.

I am not getting how to include it.

If at all i try like this:

$dbh->do("select @b = 5");
$dbh->do($create_procedure) || die "cannot create procedure";
$spt = $dbh->prepare("CALL oper(15,\@b,'+')") or die "cannot execute or call this procedure";
$spt->execute();
$p_RecID = $dbh->selectrow_array('SELECT @b');
print "customer_count=", $result[0], "\n";
print "Received RecID = $p_RecID\n";

I am getting answer as 5 but the out put should be : 20 because we are passing 15 and operator as "+" so (15+5 = 20) but i am unable to get this out put.

I am finding very difficulty in understanding INOUT parameter.

How can i get the above output?

REGARDS

[d5e5]

The way you were setting the @b session variable somehow didn't work so the INOUT parameter @b probably contained NULL, and adding any number to NULL gives NULL. That's my guess, but I'm not sure. Anyway, the following works for me.

#!/usr/bin/perl
use strict;
use warnings;

use DBI;

my $dbh = DBI->connect("DBI:mysql:daniweb","david", "dogfood",{ PrintError => 0}) || die $DBI::errstr;

my $drop_if_exists = qq(DROP PROCEDURE IF EXISTS sp_oper;);
$dbh->do($drop_if_exists) || die "Error dropping procedure: $DBI::errstr\n";

my $create_procedure = qq{
	CREATE PROCEDURE sp_oper(IN a FLOAT, INOUT b FLOAT, IN oper VARCHAR(1))
BEGIN
#Too confusing to name procedure same as IN oper parameter
#so rename either procedure or parameter. I named procedure 'sp_oper'.
  CASE oper
  WHEN '+' THEN SET b := a + b;
  WHEN '-' THEN SET b := a - b;
  WHEN '*' THEN SET b := a * b;
  WHEN '/' THEN SET b := a / b;
  WHEN '%' THEN SET b := a % b;
  WHEN '^' THEN SET b := POW(a, b);
  ELSE SET b := a;
  END CASE;
  # SELECT b; #Not needed
  END 
};
$dbh->do($create_procedure) || die "Error creating procedure: $DBI::errstr\n";

#$dbh->do('SELECT @b = 5;') || die "$DBI::errstr\n"; #Does not work!

#Set session variable as follows:
my $param = 5;
$dbh->do(qq{SET \@b = ? }, undef, $param);

#Escape @b with backslash so Perl won't interpolate
$dbh->do("CALL oper(15, \@b, '+')") or die "cannot execute or call this procedure";
my ($value_from_sp) = $dbh->selectrow_array('SELECT @b');
print "Value from sp_oper is $value_from_sp\n";
$dbh->disconnect || die "Failed to disconnect\n";

This outputs: Value from sp_oper is 20

[archana amith]

Hi,

I tired the above code i am getting this error.

cannot execute or call this procedure at dbtest.pl line 26.
$dbh->do("CALL sp_oper(15, \@b, '+')") or die "cannot execute or call this procedure";

If i change the do to prepare statement and then execute it is giving the answer 5 but not 20.

How can i get the above answer?

Regards
Archana

[d5e5]

I don't understand why it doesn't work for you. It does for me. Make sure you are calling the procedure by the same name as the procedure name that you create. (I made that mistake in the script posted above, creating sp_oper but calling oper, but after dropping oper and fixing the statement to call sp_oper the above script still gives me the expected output of 20.)

The only thing I can suggest is to add $DBI::errstr to the statement that calls sp_oper so it will give you more specific error information. Change the statement as follows: $dbh->do("CALL sp_oper(15, \@b, '+')") or die "cannot execute or call this procedure: $DBI::errstr\n";