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 FuntionCREATE 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
