Sponsored Content
Top Forums Shell Programming and Scripting How to create and call mysql stored procedure in perl? Post 302489644 by vanitham on Friday 21st of January 2011 04:30:33 AM
Old 01-21-2011
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
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calling MYSQL Stored Procedure?

Hi, Can anyone help me with the correct syntax to call a MYSQL stored procedure from a shell script. I have tried the following, (no input params): /usr/bin/mysql -uadmin -ppassword call TL_CLENSEDATA(); resulting in error: /home/hosting/data/scripts/dbclense.sh: line 12: syntax error... (3 Replies)
Discussion started by: kshelluser
3 Replies

2. Shell Programming and Scripting

Need to call stored procedure from unix script

Hi Guys, I have a stored procedure which has 5 out parameters. I need to call the stored procedure from the script. When i use the following in my script, db2 "CALL FCFM.PART_MASTER_TMP($Return_code,$Message,$Message1,$SQL,$Load_count)" >> $LOG_FILE I am getting an error.. Please... (1 Reply)
Discussion started by: mac4rfree
1 Replies

3. Shell Programming and Scripting

need to call 3 stored procedure at the same time

Hi GUYS, I need to trigger 3 stored procedure at the same time.. I know how to trigger the stored procedure. If anybody can tell me how to trigger 3 different process at the same time parallelly.. that would be helpful.. Thanks for your help in advance, Magesh (1 Reply)
Discussion started by: mac4rfree
1 Replies

4. Programming

How to call sqlloader from stored procedure!!! Advise

Hi , I have a dellimited .dat file and a sqlloader. I want to call the sqlloader from a oracle stored procedure. The procedure should return the result of sqlloader's execution. (3 Replies)
Discussion started by: Haque123
3 Replies

5. Shell Programming and Scripting

Unique constraint violated within stored procedure executed from Perl

Hi! I got an strange trouble executing a stored procedures that goes inserting line by line on a table. I mus integrate it with perl for an specific task... the hole process is controlled by e Perl script that: Load a text file calling sqlldr. Call a stored procedure that process the... (2 Replies)
Discussion started by: jparra
2 Replies

6. Shell Programming and Scripting

how to call oracle stored procedure from unix shell

Hi i want to call a oracle stored procedure from unix (using bash shell). consider this is my oracle stored procedure with parameter create procedure testproc(name IN varchar, age IN Number, id OUT Number ) AS begin id=1; dbms_output.put.line('successfull validation') end;... (6 Replies)
Discussion started by: barani75
6 Replies

7. Shell Programming and Scripting

Call and redirect output of Oracle stored procedure from unix script

Hi, Can you assist me in how to redirect the output of oracle stored procedure from unix script? Something similar to what i did for sybase isql -U$MYDBLOG -D$MYDBNAME -S$MYDBSVR -P$MYDBPWD -o$MYFILE<< %% proc_my_test 8 go %% Thanks in advance - jak (0 Replies)
Discussion started by: jakSun8
0 Replies

8. Shell Programming and Scripting

How to call a stored procedure from shell program?

How to call a stored procedure from shell program (1 Reply)
Discussion started by: noorm
1 Replies

9. Shell Programming and Scripting

How to call stored procedure with CLOB out parameter from shell script?

I have written a stored procedure in oracle database, which is having a CLOB OUT parameter. How can i call this stored procedure from shell script and get the CLOB object in shell script variable? (0 Replies)
Discussion started by: vel4ever
0 Replies

10. Shell Programming and Scripting

How to call a stored procedure inside a loop?

How to call a stored procedure inside a loop export PATH=$ORACLE_HOME/bin:$PATH ORACLE_SID=xxxx; export ORACLE_SID ORAENV_ASK=NO TODAY_DATE=$(date +"%Y%m%d") LOGFILE=/var/opt/gogd/cust/scripts/${TODAY_DATE}_sap_cust_rel.log echo 'Connected' PARCUST=sap_cust_rel.par sqlldr... (1 Reply)
Discussion started by: ashwanth
1 Replies
Apache::Session::Store::MySQL(3pm)			User Contributed Perl Documentation			Apache::Session::Store::MySQL(3pm)

NAME
Apache::Session::Store::MySQL - Store persistent data in a MySQL database SYNOPSIS
use Apache::Session::Store::MySQL; my $store = new Apache::Session::Store::MySQL; $store->insert($ref); $store->update($ref); $store->materialize($ref); $store->remove($ref); DESCRIPTION
Apache::Session::Store::MySQL fulfills the storage interface of Apache::Session. Session data is stored in a MySQL database. SCHEMA
To use this module, you will need at least these columns in a table called 'sessions', or another table name if you provide the TableName argument: id char(32) # or however long your session IDs are. a_session text To create this schema, you can execute this command using the mysql program: CREATE TABLE sessions ( id char(32) not null primary key, a_session text ); If you use some other command, ensure that there is a unique index on the table's id column. CONFIGURATION
The module must know what datasource, username, and password to use when connecting to the database. These values can be set using the options hash (see Apache::Session documentation). The options are: DataSource UserName Password TableName Handle Example: tie %hash, 'Apache::Session::MySQL', $id, { DataSource => 'dbi:mysql:database', UserName => 'database_user', Password => 'K00l', TableName => 'sessions' }; Instead, you may pass in an already-opened DBI handle to your database. tie %hash, 'Apache::Session::MySQL', $id, { Handle => $dbh }; AUTHOR
This modules was written by Jeffrey William Baker <jwbaker@acm.org> SEE ALSO
Apache::Session perl v5.10.1 2010-10-18 Apache::Session::Store::MySQL(3pm)
All times are GMT -4. The time now is 02:59 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy