How to create and call mysql stored procedure in perl? | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

How to create and call mysql stored procedure in perl?

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 01-21-2011
vanitham vanitham is offline
Registered User
 
Join Date: Sep 2007
Last Activity: 8 October 2011, 3:32 AM EDT
Posts: 135
Thanks: 0
Thanked 0 Times in 0 Posts
How to create and call mysql stored procedure in perl?

Hi,

I want to create MySQL stored procedure and call the stored procedure using perl.

I tried like this:


Code:
use DBI;
my $dbh = DBI->connect ("DBI:mysql:test",
                           "root", "ibab",
                           { RaiseError => 1, PrintError => 0});
$create_procedure = qq{
    CREATE PROCEDURE greeting()
	BEGIN
	select current_time() AS time;
	END
};
$dbh->do(qq{DROP PROCEDURE IF EXISTS greeting});
$dbh->do($create_procedure);
my $sth = $dbh->prepare(qq{CALL greeting()});
$sth->execute();

The procedure is getting created in Mysql but when i am trying to run i am getting this error.


Code:
C:\Perl\bin>perl try.pl
DBD::mysql::st execute failed: PROCEDURE test.greeting can't return a result set in the given context

I am not able to identify and debug what is this error?

Here is the result in MySQL.

Code:
mysql> SHOW CREATE PROCEDURE greeting\G
*************************** 1. row ***************************
       Procedure: greeting
        sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `greeting`()
BEGIN
        select current_time() AS time;
        END
1 row in set (0.01 sec)


mysql> call greeting();
+----------+
| time     |
+----------+
| 14:51:11 |
+----------+
1 row in set (0.03 sec)

I am not able to get the above output. The output should be current time displayed.


Code:
+----------+
| time     |
+----------+
| 14:51:11 |
+----------+

How can i do it in perl?


Code:
MySQL Version
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.27-community-nt |
+---------------------+
1 row in set (0.00 sec)

Perl Version:perl, v5.8.8

Help may be very much appreciated.

Regards
Vanitha
Sponsored Links
    #2  
Old 01-22-2011
m1xram m1xram is offline
Registered User
 
Join Date: Nov 2009
Last Activity: 31 August 2011, 10:26 PM EDT
Posts: 61
Thanks: 1
Thanked 9 Times in 9 Posts
MySQL Procedure

Looked at the MySQL Documentation for Create Procedure and did this as the "root" user...

Code:
mysql> delimiter //
mysql> create procedure greeting(out t varchar(30)) begin select current_time() into t; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call greeting(@a)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @a//
+----------+
| @a       |
+----------+
| 21:57:47 |
+----------+
1 row in set (0.00 sec)

It does work but it looks like the output is supposed to be put into a table. In this case a temp table. You'll have to fetch the row from the temp table "@a".

Also found that it would only work if I was the "root" user because the normal user didn't seem to have "execute" privilege.

Modify your PERL code to model what's above. Check the procedure it creates via mysql. Login as "root" again and then...

Code:
mysql> use test;
Database changed
mysql>show create procedure greeting;

The reason to verify the procedure is because of the delimiter included with the procedure. Did the semicolon get in there or did it cause an error?

After that all works as "root" you can attempt each step as a normal user and see what extra privileges need to be enabled.

Also have a look at CREATE FUNCTION, same doc as link above. The hello_world() function is run from a SELECT so you could probably do something like...

Code:
mysql> create function greeting2() returns varchar(30) deterministic return current_time();
Query OK, 0 rows affected (0.00 sec)

mysql> select greeting2() as time;
+----------+
| time     |
+----------+
| 22:36:48 |
+----------+
1 row in set (0.00 sec)

...and then fetch the row.
Sponsored Links
    #3  
Old 01-25-2011
vanitham vanitham is offline
Registered User
 
Join Date: Sep 2007
Last Activity: 8 October 2011, 3:32 AM EDT
Posts: 135
Thanks: 0
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by m1xram View Post
Looked at the MySQL Documentation for Create Procedure and did this as the "root" user...

Code:
mysql> delimiter //
mysql> create procedure greeting(out t varchar(30)) begin select current_time() into t; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call greeting(@a)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @a//
+----------+
| @a       |
+----------+
| 21:57:47 |
+----------+
1 row in set (0.00 sec)

It does work but it looks like the output is supposed to be put into a table. In this case a temp table. You'll have to fetch the row from the temp table "@a".

Also found that it would only work if I was the "root" user because the normal user didn't seem to have "execute" privilege.

Modify your PERL code to model what's above. Check the procedure it creates via mysql. Login as "root" again and then...

Code:
mysql> use test;
Database changed
mysql>show create procedure greeting;

The reason to verify the procedure is because of the delimiter included with the procedure. Did the semicolon get in there or did it cause an error?

After that all works as "root" you can attempt each step as a normal user and see what extra privileges need to be enabled.

Also have a look at CREATE FUNCTION, same doc as link above. The hello_world() function is run from a SELECT so you could probably do something like...

Code:
mysql> create function greeting2() returns varchar(30) deterministic return current_time();
Query OK, 0 rows affected (0.00 sec)

mysql> select greeting2() as time;
+----------+
| time     |
+----------+
| 22:36:48 |
+----------+
1 row in set (0.00 sec)

...and then fetch the row.
Hi,

Thanks for the reply,

I am a root user.

I tried for procedure like this:


Code:
use DBI;
my $create_procedure = qq{
   CREATE PROCEDURE simpleproc (OUT param1 INT)
   BEGIN
   SELECT COUNT(*) INTO param1 FROM data;
   END
};
$dbh->do($create_procedure);
my $sth = $dbh->prepare("CALL simpleproc(@a)");
$sth->execute();
//then fetch

But still i am unable to execute the procedure from Perl.
I am getting this error:

Code:
DBD::mysql::st execute failed: Incorrect number of arguments for PROCEDURE test simpleproc; expected 1, got 0 at try.pl line 23.

I am finding an difficulty in calling procedure.

How can i call procedure from Perl program?

In MySQL it works fine.

I want to create procedure through PERL.

Help is very much appreciated.

Regards
Vanitham
    #4  
Old 01-27-2011
m1xram m1xram is offline
Registered User
 
Join Date: Nov 2009
Last Activity: 31 August 2011, 10:26 PM EDT
Posts: 61
Thanks: 1
Thanked 9 Times in 9 Posts
Common MySQL Procedure problem

Did a lot of googling and found that many people have this problem. PHP people got rid of it with the PDO interface but I found an example of a simplified answer for PERL.

First, the stored procedure must be simplified.

Code:
mysql> delimiter //
mysql> create procedure greet() 
    -> begin
    ->   select current_time() as t;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

Second, we call it and fetch the row.

Code:
use DBI; 
$dbh = DBI->connect("dbi:mysql:database=test","root","password"); 
$q = $dbh->prepare("call greet()"); 
$q->execute(); 
@results = $q->fetchrow(); 
print $results[0], "\n";

There's no more IN or OUT variables, that PERL can't deal with, and we got rid of the temp table. That was a tough find.

Also found a procedure which works with a parameter.

Code:
mysql> delimiter //
mysql> create procedure Foo( x int) 
    -> begin
    ->   select x*2;
    -> end
    -> //
mysql> delimiter //
Query OK, 0 rows affected (0.00 sec)


Code:
use DBI; 
$dbh = DBI->connect("dbi:mysql:database=test", "root", "password"); 
$q = $dbh->prepare("call Foo(?)"); 
$q->execute(5); 
@results = $q->fetchrow(); 
print $results[0], "\n";

Sponsored Links
    #5  
Old 01-27-2011
k_manimuthu k_manimuthu is offline
Registered User
 
Join Date: Sep 2010
Last Activity: 18 June 2014, 2:59 AM EDT
Posts: 90
Thanks: 15
Thanked 18 Times in 17 Posts
Update your DBD::MySQL module.

Last edited by k_manimuthu; 01-27-2011 at 04:22 AM..
Sponsored Links
    #6  
Old 01-27-2011
m1xram m1xram is offline
Registered User
 
Join Date: Nov 2009
Last Activity: 31 August 2011, 10:26 PM EDT
Posts: 61
Thanks: 1
Thanked 9 Times in 9 Posts
IN OUT variables

@k_manimuthu

Don't see any examples of using IN OUT INOUT variables at the link you posted which was what the problem was. Once they are removed any stored procedure can be used with the current version of DBI::\DBD as long as it is reasonably recent.

I have found nothing to suggest that the above variables are supported in the latest version.

Last edited by m1xram; 01-27-2011 at 10:22 AM.. Reason: typo
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
how to call oracle stored procedure from unix shell barani75 Shell Programming and Scripting 6 02-26-2010 02:51 PM
How to call sqlloader from stored procedure!!! Advise Haque123 Programming 3 06-12-2009 10:59 AM
need to call 3 stored procedure at the same time mac4rfree Shell Programming and Scripting 1 03-26-2009 08:40 AM
Need to call stored procedure from unix script mac4rfree Shell Programming and Scripting 1 03-25-2009 02:44 AM
Calling MYSQL Stored Procedure? kshelluser Shell Programming and Scripting 3 12-19-2006 08:59 PM



All times are GMT -4. The time now is 09:35 PM.