The Adaptive Server Enterprise database driver for Perl supports stored procedures that include both input and output parameters.
Stored procedures are handled in the same way as any other Transact-SQL statement. However, Sybase stored procedures return an extra result set that includes the return status that corresponds to the return statement in the stored procedure code. This extra result set, named CS_STATUS_RESULT with numeric value 4043, is a single row and is always returned last.
The driver can process the stored procedure using a special attribute, $sth->{syb_do_proc_status}. If this attribute is set, the driver processes the extra result set, and places the return status value in $sth->{syb_proc_status}. An error is generated if the result set is a value other than 0.
$sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?"); $sth->execute('one', 'two');
$sth = $dbh->prepare("exec my_proc ?, ?"); $sth->execute('one', 'two');
$sth = $dbh->prepare("exec my_proc \@p1 = 1, \@p2 = ?");
$sth = $dbh->prepare(qq[declare @name varchar(50) exec getname abcd, @name output]);
$sth = $dbh->prepare("exec my_proc ?"); $sth->execute('foo');
$sth = $dbh->prepare("exec my_proc 'foo'"); $sth->execute('foo');
do { while($data = $sth->fetch) { do something useful... } } while($sth->{syb_more_results});
declare @id_value int, @id_name char(10) exec my_proc @name = 'a_string', @number = 1234, @id = @id_value OUTPUT, @out_name = @id_name OUTPUT
$sth = $dbh->prepare('select * .......'); $sth->execute(); @results = $sth->syb_output_params(); # this method is available in SybaseASE.pm
This returns an array for all the OUTPUT parameters in the procedure call and ignores any other results. The array is undefined if there are no OUTPUT parameters or if the stored procedure fails.
$sth = $dbh->prepare("declare \@id_value int, \@id_name OUTPUT, @out_name = @id_name OUTPUT"); $sth->execute(); { while($d = $sth->fetch) { # 4042 is CS_PARAMS_RESULT if ($sth->{syb_result_type} == 4042) { $id_value = $d->[0]; $id_name = $d->[1]; } } redo if $sth->{syb_more_results}; }
The OUTPUT parameters are returned as a single row in a special result set.