469,619 Members | 1,963 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,619 developers. It's quick & easy.

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 4160

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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Rhino | last post: by
8 posts views Thread by Wonderinguy | last post: by
3 posts views Thread by Samarth | last post: by
3 posts views Thread by mdaetwyler | last post: by
reply views Thread by Shiraz | last post: by
reply views Thread by Shiraz | last post: by
1 post views Thread by rajpar | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.