Re: Stored procedures and DBD::Sybase

[Chuck Fox]

Steven,

Do perldoc DBD::Sybase.  There is a writeup on how to detect multiple 
result sets.  This method deviates from the DBI standards.  Basically, 
you check a special Sybase attribute in the statement handle, 
syb_more_results, to see if more fetchable results exist.

Chuck

[EMAIL PROTECTED] wrote:

Chuck,

Thanks for the response.  Although, the DBD::Sybase docs do detail
retrieving sproc out params as I have done, using the select statement
to explicitly form a result is an interesting solution.
One additional question rises from the fact that DBD::Sybase can handle
returning many result sets and my real life procedure (not this test
one) actually does return many result sets.  With this solution the only
way I can know that I am getting my procedure params is by placing my
select statement last and assuming that the last result row is the one I
am interested in.  Is there a better way of doing this? Also, can I move
the client-side cursor to the end of the result set without having to
fetch all the intermediate results?
Thank you greatly,
Steven
-----Original Message-----
From: Chuck Fox [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 10, 2003 12:51 PM
To: Steven Velez
Cc: [EMAIL PROTECTED]
Subject: Re: Stored procedures and DBD::Sybase

Steven,

I believe that you are misunderstanding what it is that you are 
receiving.  The only output from your commands is the print @c.  This 
comes through the error handler since a print statement is a level 0 or 
level 10 message from the server and not a result set.  Try changing the

print to a select and then you can receive it as a row result.

Regards,

Chuck

[EMAIL PROTECTED] wrote:

 

Hello,

I am using DBI v. 1.38, DBD::Sybase v. 1.01 (?), and FreeTDS 0.61.2 to
connect to an MSSQL 6.5 database from a Linux client and I am having
problems retrieving the values of out parameters to a stored procedure
call.
I followed the example in the DBD::Sybase docs modifying for my purpose
as follows:
#!/usr/bin/perl

use DBI;
use DBD::Sybase;
my $dbh = DBI->connect('dbi:Sybase:server=****',
  'sa', '****', {PrintError => 1, AutoCommit => 1});
$dbh->do('use tempdb');
my $sql = q{
declare @a int
declare @b varchar(10)
exec foo @a output, @b output
declare @c varchar(255)
select @c = @b + ' : ' + convert(varchar(3), @a)
print @c
};
my $sth = $dbh->prepare($sql);
$sth->execute;
do {
      while ($d = $sth->fetch) {
          $rt = $sth->{'syb_result_type'};
          print("Iterating: $rt\n");
          if ($rt == DBD::Sybase::CS_PARAM_RESULT) {
              print("found param result [EMAIL PROTECTED]");
              $a = $$d[0];
              $b = $$d[1];
          } elsif ($rt == DBD::Sybase::CS_ROW_RESULT) {
              print("found row result [EMAIL PROTECTED]");
          } elsif ($rt == DBD::Sybase::CS_STATUS_RESULT) {
              print("found status result [EMAIL PROTECTED]");
          }
      }
} while($sth->{'syb_more_results'});
print("Retrieved values $a $b\n");
And received the following output:

Iterating: 4043
found status result [0]
hello : 5
Retrieved values
As I understand the docs, the there should have been a CS_PARAM_RESULT
result set but there was not.  As you see, I even printed out the
results from the sql to make sure the out params were being set.  Am I
doing something wrong or is this a known limitation when using my
configuration.  Perhaps I can use the Sybase Open Client libraries
instead?
I help will be greatly appreciated.

Thank you,
Steven