stored procedure calling from perl
am 09.01.2006 03:27:42 von shirazkUpdated 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 is
>>CREATE 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