in reply to SQL Stored Procedure return value

Since you only have one output parameter and don't appear to be processing a result set as well, you can just do this after the execute: @output = $sth->func('syb_output_params');And $output[0] will contain the value (unless the proc failed, of course).

If you're also processing a result set, it's more complicated. Look up the documentation in the module, the section called "Retrieving output parameters from stored procedures" (appropriately enough).

Replies are listed 'Best First'.
Re: Re: SQL Stored Procedure return value
by gnangia (Scribe) on Jul 17, 2003 at 18:38 UTC
    I have tried the @output = $sth->func('syb_output_params'); way, and program just hangs. I have also followed the example from perldoc DBD::Sybase, but I always get a value of 0 back. I have read the section in perldoc DBD::Sybase, and the only thing different I have is my actual sql statement where I had to escape  \@diskreads=\@test OUTPUT which may or may not be right. Removing the backslashes gives an sql error from SQL server. Please advise as to where I am going wrong.
[reply]
[d/l]
[select]
      Besides the good advice of VSarkiss, do some error checking. Maybe you don't have permission to run the stored proc or maybe your not in the correct database when you login from your script. I try to always follow this template when doing DBI work: 

      #!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect( $dsn, $user, $pass, 
                   { PrintError => 0, RaiseError => 1 } );

eval {
   my $sql = "blah ... ";
   my $sth = $dbh->prepare( $sql );
   $sth->execute;
   do {
       ...
   } while( $sth->{syb_more_results});
   $sth->finish;
};

if( $@ ) {
   print STDERR "DB error: $@\n";
}

$dbh->disconnect if $dbh;

      [download]

      -derby

[reply]
[d/l]

      There are a few possibilities. First, it may seem obvious, but are you sure the stored procedure is doing what you think? Can you invoke in interactive SQL and get what you expect? Second, are you using FreeTDS to connect to SQL Server? There are some known problems with it, and you may be stepping on one of them. Try using DBD::ADO or DBD::ODBC (which have their own set or problems, I know), and see if the situation improves. Otherwise, I don't see any obvious problems.

[reply]
        I am using freetds, DBI and DBD::Sybase as my means of connecting to sql server. I can connect to the SQL server because I can do a select \@\@version sql command and get the output ok. So I know the perl connection to SQL server is ok. Here is a test procedure I used and it still does not work. Stored Procedure is -> 
        CREATE PROCEDURE TEST @results numeric(9,4) OUTPUT 
AS

BEGIN
SELECT @results = 9999.9999
      
END

        [download]
        Here is the sql statement I use to retrieve the result at a sql command line. 
        declare @mytest numeric(9,4)
exec TEST @results=@mytest OUTPUT
print @mytest

        [download]
        Now how would I translate that to my perl code to retrieve the output which should just be 9999.9999. Please note that the last print @mytest is only being used to print to stdout. Please advise.
[reply]
[d/l]
[select]
      Hi Michael,
      Thanks for writing but I get the following error when I run the above code DBD::Sybase::st execute failed: Server message number=156 severity=15 state=1 line=1 server=WIN-SVR2text=Incorrect syntax near the keyword 'select'. at ./syb.pl line 52.. Here is the code - 
      #$sth = $dbh->prepare("declare \@ret numeric exec TEST \@results=@ret 
+select @ret");
 $sth = $dbh->prepare("declare \@ret numeric(9,4) exec TEST \@results=
+@ret select @ret");
        $sth->execute;
        do {
                while($d = $sth->fetch)
                {
                        print "@$d\n";
                }
        } while($sth->{syb_more_results});

      [download]
      It complains with the sql statement essentially. I have also tried escaping all the @ signs, it runs but always returns a value of 0.($sth = $dbh->prepare("declare \@ret numeric(9,4) exec TEST \@results=\@ret select \@ret");) Please advise.
[reply]
[d/l]
[select]
        I'm uncertain how FreeTDS handles this, but the following should work: 
        $sth = $dbh->prepare("
declare \@ret numeric
exec TEST \@results=@ret
select @ret
");
$sth->execute;
do {
    while($d = $sth->fetch) {
        print "@$d\n";
    }
} while($sth->{syb_more_results});

        [download]
        (with appropriate error handling, of course).

        Michael

[reply]
[d/l]

In Section Seekers of Perl Wisdom