Re: Re (2): SQL Stored Procedure return value

Sure they are. From the DBD::Sybase docs (which also gives the shortcut you've shown in another node):

If your stored procedure only returns OUTPUT parameters,
then you can use this shorthand:

$sth = $dbh->prepare('...');
$sth->execute;
@results = $sth->func('syb_output_params');

This will return an array for all the OUTPUT parameters in
the proc call, and will ignore any other results. The
array will be undefined if there are no OUTPUT params, or
if the stored procedure failed for some reason.

The more generic way looks like this:

$sth = $dbh->prepare(
   "declare \@id_value int, \@id_name
   exec my_proc @name = 'a string', @number = 1234, @id = @id_value
   OUTPUT, @out_name = @id_name OUTPUT"
);

$sth->execute;
do {
   while($d = $sth->fetch) {
      if($sth->{syb_result_type} == 4042) { # it's a PARAM result
         $id_value = $d->[0];
         $id_name  = $d->[1];
      }
   }
 } while($sth->{syb_more_results});
[download]

update: Unless of course this is one of those odd little areas where SQLServer and Sybase differ.

-derby

Comment on Re: Re (2): SQL Stored Procedure return value Download Code

Replies are listed 'Best First'.
Re (4): SQL Stored Procedure return value by VSarkiss (Monsignor) on Jul 17, 2003 at 18:24 UTC
Your initial answer only showed retrieving multiple result sets -- the ... was leaving out something important, which your later example showed. And I stand by my statement: DBD::Sybase makes output parameters appear to be part of a result set, but in fact they are handled differently by the SQL Server API.	[reply]
Re: Re (4): SQL Stored Procedure return value by mpeppler (Vicar) on Jul 17, 2003 at 20:18 UTC
And I stand by my statement: DBD::Sybase makes output parameters appear to be part of a result set, but in fact they are handled differently by the SQL Server API Actually that's not quite true. DBD::Sybase simply uses the Client Library API, which in turn uses the TDS protocol. For TDS 4.2 and 5 OUTPUT parameters are returned as a normal result set (although it's a CS_PARAM_RESULT result set, instead of a CS_ROW_RESULT result set). I believe that MS changed things somewhat in TDS 7 and 8 (at least that's what I think I've read on the FreeTDS mailing list). TDS 4.2 is the protocol level that was spoken by MS-SQL 4.2, which was originally Sybase SQL Server 4.2. TDS 5 is spoken by Sybase versions 10 and later. TDS 7 is MSSQL 7, TDS 8 is MSSQL 2K. Michael	[reply]
Re: Re (4): SQL Stored Procedure return value by derby (Abbot) on Jul 17, 2003 at 18:42 UTC
Hmmm ... it's just semantics and perspective .. wether you consider output parameters part of a multiple result set or not. They're definetly not part of the CS_ROW_RESULT set but they are part of the CS_PARAM_RESULT set which given the implementation of DBD::Sybase will be returned with the basic fetch loop. Sure I left out the detailed spefics but there was enough information in the node (linkage to docs, key verbage - syb_result_type) for the user to find out the info. Sorry, just my style of "teaching to fish" instead of "giving a fish". -derby.	[reply]