calling mysql stored procedures / functions

Discussion in 'Perl Misc' started by Shiraz, Jan 6, 2006.

  1. Shiraz

    Shiraz Guest

    I am trying to call functions within perl. i have confirmed that the
    function works in mysql but errors out in perl.
    I found a bug fix on CPAN (http://rt.cpan.org/NoAuth/Bug.html?id=12322)
    but that is for DBD-mysql-2.9006 and i have DBD::mysql -- 2.9007. Can
    anyone suggest what i should do?

    My installation details:
    perl, v5.8.0 built for i386-linux-thread-multi
    DBD::mysql -- 2.9007
    DBI -- 1.48

    Script:
    $SQL_Text = "select r2() from dual " ;
    $sth=$dbh->prepare($SQL_Text);
    $sth->execute();
    while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
     
    Shiraz, Jan 6, 2006
    #1
    1. Advertising

  2. Shiraz

    Shiraz Guest

    Updated to the latest version of DBD-mysql and now the functions work
    but dont get any output when i call procedures which have statements
    like "select 'hello' " in them.
    on the mysql console, this would result in a line 'hello' nothing
    happens in perl.

    Any suggestions ?
     
    Shiraz, Jan 6, 2006
    #2
    1. Advertising

  3. Shiraz

    Paul Lalli Guest

    Shiraz wrote:
    > Updated to the latest version of DBD-mysql and now the functions


    Updated what?
    What functions?

    Please quote some context when posting a reply.

    > work
    > but dont get any output when i call procedures which have statements
    > like "select 'hello' " in them.
    > on the mysql console, this would result in a line 'hello' nothing
    > happens in perl.
    >
    > Any suggestions ?


    You have a syntax error on line 42.

    That is a complete guess of course, because you've not given us any way
    of determining any more accurate information.

    Have you read the posting guidelines for this group? Please post a
    short-but-complete script that demonstrates your problem.

    Paul Lalli
     
    Paul Lalli, Jan 6, 2006
    #3
  4. Paul Lalli <> wrote:
    > Shiraz wrote:
    >> Updated to the latest version of DBD-mysql and now the functions

    >
    > Updated what?
    > What functions?
    >
    > Please quote some context when posting a reply.



    I think you are "talking to the hand", as I asked him the
    same thing last May...


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, Jan 7, 2006
    #4
  5. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 8, 2006
    #5
  6. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 8, 2006
    #6
  7. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 8, 2006
    #7
  8. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 8, 2006
    #8
  9. Shiraz

    Dr.Ruud Guest

    Shiraz schreef:

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


    This isn't a Perl problem.

    I see no need for the backticks in the CREATE line.
    `done` remains 0.
    `done` is tested both in the IF and in the UNTIL.
    Looks like a big mess to me.

    --
    Affijn, Ruud

    "Gewoon is een tijger."
     
    Dr.Ruud, Jan 8, 2006
    #9
  10. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 8, 2006
    #10
  11. Shiraz

    Shiraz Guest

    ok here is my function defination within MySQL

    ######################################################3
    CREATE FUNCTION `r2`()
    DETERMINISTIC
    BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE a INT;
    DECLARE b,c INT;
    DECLARE cur1 CURSOR FOR SELECT duration from TODAYSCDRS;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    set b=0;
    set c=0;
    OPEN cur1;

    REPEAT

    FETCH cur1 INTO a;

    IF NOT done THEN
    IF a > 0 THEN
    set b = b +1;
    END IF;
    set c = c + 1;
    END IF;

    UNTIL done END REPEAT;
    CLOSE cur1;
    return ceil(b/c*100) ;
    END
    ################################################

    and here is the perl script

    use DBI;
    $dbh = DBI->connect($dsn, $defs{dbuser}, $defs{dbpswd}, \%attr);
    $SQL_Text = "select r2() from dual " ;
    $sth=$dbh->prepare($SQL_Text);
    $sth->execute();
    while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }


    before the perl script would just error out and return nothing. then i
    updated the DBD-mysql and now calling functions within the perl script
    works.

    now the next problem is running stored procedures from mysql within
    perl
    here is Mysql stored procedure code:

    ##########################################
    CREATE PROCEDURE `PROC_ASR`()
    BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE duration INT;
    DECLARE T INT;
    DECLARE A INT;
    DECLARE ASR decimal(9,5);
    DECLARE getCDRScur CURSOR FOR SELECT DURATION FROM TODAYSCDRS;

    OPEN getCDRScur;

    REPEAT
    IF NOT done THEN
    FETCH getCDRScur INTO duration;
    if duration > 0 THEN SET T = T +1;
    end if;
    set A = A + 1;
    END IF;
    UNTIL done END REPEAT;
    CLOSE getCDRScur;
    select (A);
    END
    ############################################

    and here is the perl code calling it
    use DBI;
    $dbh = DBI->connect($dsn, $defs{dbuser}, $defs{dbpswd}, \%attr);
    $SQL_Text = "call PROC_ASR() " ;
    $sth=$dbh->prepare($SQL_Text);
    $sth->execute();
    while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }

    but i dont get any output. i have verified that the procedure PROC_ASR
    works within mysql but cannot call it from perl.
     
    Shiraz, Jan 9, 2006
    #11
  12. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 9, 2006
    #12
  13. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 9, 2006
    #13
  14. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 9, 2006
    #14
  15. Shiraz

    Shiraz Guest

    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 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
     
    Shiraz, Jan 9, 2006
    #15
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. .Net Sports
    Replies:
    3
    Views:
    610
    Robbe Morris [C# MVP]
    May 12, 2005
  2. veera maria
    Replies:
    2
    Views:
    1,172
    Sudsy
    Sep 5, 2003
  3. Alex. O. Koranteng
    Replies:
    8
    Views:
    515
    Alex. O. Koranteng
    Jun 16, 2009
  4. Replies:
    6
    Views:
    147
    Bob Barrows [MVP]
    May 29, 2008
  5. Replies:
    2
    Views:
    109
    Skye Shaw!@#$
    Mar 31, 2009
Loading...

Share This Page