473,380 Members | 1,225 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,380 software developers and data experts.

stored procedure calling from perl

Updated to the latest version of DBD-mysql using
perl -MCPAN -e "install DBD-mysql"
and now the calling mysql function r2() within perl work
$SQL_Text = "select r2() from dual " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
for reference here is the mysql Funtion
CREATE FUNCTION `r2`() RETURNS INT
DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a INT;
DECLARE duration INT;
DECLARE cur1 CURSOR FOR SELECT duration from TODAYSCDRS;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
set a=0;
OPEN cur1;
REPEAT
FETCH cur1 INTO duration;
IF NOT done THEN
set a = a +duration;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
return a;
END
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code isCREATE PROCEDURE `r3`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE duration INT;
DECLARE A INT;
DECLARE getCDRScur CURSOR FOR SELECT DURATION FROM TODAYSCDRS;
SET A=0;
OPEN getCDRScur;
REPEAT
IF NOT done THEN
FETCH getCDRScur INTO duration;
set A = A + duration;
END IF;
UNTIL done END REPEAT;
CLOSE getCDRScur;
select (A);
END

and the perl code is $SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }

but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks

Jan 9 '06 #1
3 4367

Shiraz,

I think you will find the error you are getting looks something like:

DBD::mysql::st execute failed: PROCEDURE [database].[procedure] can't
return a result set in the given context....

I have the same issue and see lots of references on the web to setting
the CLIENT_MULTI_STATEMENTS flag, but it does not seem to be in the
context of DBI, but using the mysql module directly.

Someone has to have done this or it would just be in the pod that you
can not run stored procedures using the DBD::mysql.

I hope someone will post the magic code to get this working.

Lloyd
--
lbutler
------------------------------------------------------------------------
lbutler's Profile: http://techiegroups.com/member.php?userid=48
View this thread: http://www.techiegroups.com/showthread.php?t=99047

Jan 18 '06 #2
thnaks... will keep an eye out for it

Jan 19 '06 #3

Shiraz,

It looks like we are sol at the moment on using dbd:mysql to call
stored procedures.

Have a look at this thread over at mysql.com

http://forums.mysql.com/read.php?51,...3960#msg-63960

I guess it is time to look for a work around as functions can only get
you so far.

Lloyd
--
lbutler
------------------------------------------------------------------------
lbutler's Profile: http://techiegroups.com/member.php?userid=48
View this thread: http://www.techiegroups.com/showthread.php?t=99047

Jan 20 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my...
0
by: Rhino | last post by:
I've written several Java stored procedures now (DB2 V7.2) and I'd like to write down a few "best practices" for reference so that I will have them handy for future development. Would the...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
8
by: Wonderinguy | last post by:
Hi everybody , I have been trying to execute a simple DB2 stored Procedure from perl. But it doesn't work. Could anybody please help me find out why this is happening : here is my perl script...
3
by: Samarth | last post by:
Folks, I am calling a DB2 stored procedure through Perl using the DBI:ODBC module. I am not sure if I can do this or not because I have been able to connect to and also issue select statements...
3
by: mdaetwyler | last post by:
Hi all I am trying to call a DB/2 v8.2 stored procedure from Perl DBI and am getting an error message telling me, that the routine could not be found in the library path. SQL0444N Routine...
0
by: Shiraz | last post by:
Updated to the latest version of DBD-mysql using perl -MCPAN -e "install DBD-mysql" and now the calling mysql function r2() within perl work > $SQL_Text = "select r2() from dual " ; >...
0
by: Shiraz | last post by:
Updated to the latest version of DBD-mysql using perl -MCPAN -e "install DBD-mysql" and now the calling mysql function r2() within perl work > $SQL_Text = "select r2() from dual " ; >...
1
by: rajpar | last post by:
Environment: Solaris (client + server) db2 version 7.2 latest fixpak (DB2 v7.1.0.111", "s050516" and "U803330") Compiler: gcc Here is my SP code executed on the client: CREATE PROCEDURE...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.