469,890 Members | 1,448 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

stored procedures

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
0 1068

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Kent Lewandowski | last post: by
5 posts views Thread by Tim Marshall | last post: by
45 posts views Thread by John | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.