calling mysql stored procedures / functions

S

Shiraz

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; }
 
S

Shiraz

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

Paul Lalli

Shiraz said:
Updated to the latest version of DBD-mysql and now the functions
Click to expand...

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 ?
Click to expand...

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
 
T

Tad McClellan

Paul Lalli said:
Updated what?
What functions?

Please quote some context when posting a reply.
Click to expand...


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

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 
S

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 
S

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 
S

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 
D

Dr.Ruud

Shiraz schreef:
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is

and the perl code is
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?
Click to expand...

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.
 
S

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 
S

Shiraz

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.
 
S

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 
S

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 
S

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 
S

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_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...

for reference here is the mysql Funtion
now the next challenge is to call a mysql procedure from perl
where the mysql procedure code is
and the perl code is
$SQL_Text = "call r3() " ;
$sth=$dbh->prepare($SQL_Text);
$sth->execute();
while ( ($tt) = $sth->fetchrow_array( ) ) { print $tt; }
Click to expand...
but i donot get any output from this perl script.

any suggestions on the problem and the posting methods?

thanks
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

perl mysql procedures 3
MySQL RENAME TABLE error 3
Access denied in remote mysql connection 5
Calling SQL Server Stored Procedures: no results from SELECTfollowing INSERT 2
mysql -- cannot connect (can in Perl) 6
DBI:Pg script returning 'can't call method' error 3
mysql_server_init error 1
Cant connect to mysql from perl interface 6

Members online

Total: 34 (members: 3, guests: 31)
Robots: 382

Forum statistics

Threads
473,755
Messages
2,569,536
Members
45,020
Latest member
GenesisGai

Latest Threads

Top