#!/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

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.

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.

#$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]

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

Michael, Which Sybase libraries/drivers do I need to download to get DBD::Sybase to work with?
Thank-you