stored procedure calling from perl

[Shiraz]

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_T ext);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array ( ) ) { print $tt; }
for reference here is the mysql Funtion

CREATE FUNCTION `r2`() RETURNS INT
DETERMINIST IC
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_T ext);
$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

[lbutler]

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_ST ATEMENTS 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

[Shiraz]

thnaks... will keep an eye out for it

[lbutler]

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