Re: Stored proc execution using DBI

On Thu, 2005-02-03 at 21:49, Raju, Ramakrishna (Equity) wrote:
> >From the name, this is a sybperl list. 

Yes, but DBI questions relating to Sybase are more than welcome.

> $ret_val =  $sth->execute();

> 1.	What exactly does execute() return ? It seems to always return a
> value of -1 irrespective of what the stored procedure returns in its
> return  statement..

The DBI man page should explain it. execute() returns the number of rows
affected by the command, if it is known, or -1 if the command was
successful but the number of rows affected was unknown.


> 2.	If I return rows from the procedure using a "select" command, I
> don't see any output ( bcoz I need to fetchrow and print it) , whereas
> if I use the SQL print command in the stored proc, I can see the
> output. How does the print output reach the screen when there is no
> print statement in the user perl program. Is the driver printing it ?
> I suspect so. Is the message handler output being sent to the screen
> thru some error  handling routine ?

Yes. You can configure that with the syb_errhandler attribute. See the
DBD::Sybase man page for more details.

> 3.	How do I pass output variables to a Sybase stored procedure and get
> back the values from the stored procedure ? I see a lot of Oracle
> examples on the web/books but no Sybase example. I would appreciate a
> small but complete working code to demonstrate question # 3.

Pass in output variables in the same way you would in isql. Then simply
fetch them in a normal fetch loop. Something like:

use DBD::Sybase; (to import things like CS_PARAM_RESULT...)

$sth = $dbh->prepare(...);
$sth->execute;
loop: {
    if($sth->{syb_result_type} == CS_PARAM_RESULT) {
        it's an OUTPUT parameter...
    }
    while($row = $sth->fetch) {
        ....
    }
    redo loop if $sth->{syb_more_results};
}

Michael
-- 
Michael Peppler  -  [email protected]  -  http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html