Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to create and call mysql stored procedure in perl?
Top Forums Shell Programming and Scripting How to create and call mysql stored procedure in perl? Post 302491193 by m1xram on Thursday 27th of January 2011 01:23:36 AM
Old 01-27-2011
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";

 

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

LEARN ABOUT PHP

db2_procedures

DB2_PROCEDURES(3)							 1							 DB2_PROCEDURES(3)

db2_procedures - Returns a result set listing the stored procedures registered in a database

SYNOPSIS

       resource db2_procedures (resource  $connection, string  $qualifier, string  $schema, string  $procedure)

DESCRIPTION

	Returns a result set listing the stored procedures registered in a database.

PARAMETERS

	      o $connection
		- A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

	      o $qualifier
		- A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

	      o $schema
		- The schema which contains the procedures. This parameter accepts a search pattern containing _ and % as wildcards.

	      o $procedure
		- The name of the procedure. This parameter accepts a search pattern containing _ and % as wildcards.

RETURN VALUES

	Returns  a  statement  resource  with a result set containing rows describing the stored procedures matching the specified parameters. The
       rows are composed of the following columns:

       +------------------+---------------------------------------------------+
       |   Column name	  |						      |
       |		  |						      |
       |		  |		       Description		      |
       |		  |						      |
       +------------------+---------------------------------------------------+
       |  PROCEDURE_CAT   |						      |
       |		  |						      |
       |		  | The catalog  that  contains  the  procedure.  The |
       |		  | value  is  NULL if this table does not have cata- |
       |		  | logs.					      |
       |		  |						      |
       | PROCEDURE_SCHEM  |						      |
       |		  |						      |
       |		  | Name of the schema that contains the stored  pro- |
       |		  | cedure.					      |
       |		  |						      |
       | PROCEDURE_NAME   |						      |
       |		  |						      |
       |		  |		 Name of the procedure. 	      |
       |		  |						      |
       |NUM_INPUT_PARAMS  |						      |
       |		  |						      |
       |		  | Number  of	input  (IN) parameters for the stored |
       |		  | procedure.					      |
       |		  |						      |
       |NUM_OUTPUT_PARAMS |						      |
       |		  |						      |
       |		  | Number of output (OUT) parameters for the  stored |
       |		  | procedure.					      |
       |		  |						      |
       | NUM_RESULT_SETS  |						      |
       |		  |						      |
       |		  | Number of result sets returned by the stored pro- |
       |		  | cedure.					      |
       |		  |						      |
       |     REMARKS	  |						      |
       |		  |						      |
       |		  |	Any comments about the stored procedure.      |
       |		  |						      |
       | PROCEDURE_TYPE   |						      |
       |		  |						      |
       |		  | Always returns 1, indicating that the stored pro- |
       |		  | cedure does not return a return value.	      |
       |		  |						      |
       +------------------+---------------------------------------------------+
SEE ALSO

       db2_column_privileges(3),   db2_columns(3),  db2_foreign_keys(3),  db2_primary_keys(3),	db2_procedure_columns(3),  db2_special_columns(3),
       db2_statistics(3), db2_table_privileges(3), db2_tables(3).

PHP Documentation Group 													 DB2_PROCEDURES(3)
All times are GMT -4. The time now is 01:29 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy