Stored procedure support for placeholders

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');

This example illustrates the use of positional parameters:

$sth = $dbh->prepare("exec my_proc ?, ?");

	$sth->execute('one', 'two');

You cannot mix positional and named parameters in the same prepare statement; for example, this statement fails on the first parameter:

$sth = $dbh->prepare("exec my_proc \@p1 = 1, \@p2 = ?");

If the stored procedure returns data using output parameters, you must declare them first:

$sth = $dbh->prepare(qq[declare @name varchar(50) exec getname abcd, @name output]);

You cannot call stored procedures with bound parameters, as in:

$sth = $dbh->prepare("exec my_proc ?"); 

	$sth->execute('foo');

This works as follows:

$sth = $dbh->prepare("exec my_proc 'foo'");

	$sth->execute('foo');

Because stored procedures almost always return more than one result set, use a loop until syb_more_results is 0:

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

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

$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.

The driver does not attempt to determine the correct parameter type for each parameter. The default for all parameters is driver defaults all parameters to the ODBC style SQL_CHAR value, unless you use bind_param() with a type value set to a supported bind type.

The driver supports these ODBC style bind types:

• SQL_CHAR

• SQL_VARCHAR

• SQL_VARBINARY

• SQL_LONGVARCHAR

• SQL_LONGVARBINARY

• SQL_BINARY

• SQL_DATETIME

• SQL_DATE

• SQL_TIME

• SQL_TIMESTAMP

• SQL_BIT

• SQL_TINYINT

• SQL_SMALLINT

• SQL_INTEGER

• SQL_REAL

• SQL_FLOAT

• SQL_DECIMAL

• SQL_NUMERIC

• SQL_BIGINT

• SQL_WCHAR

• SQL_WLONGVARCHAR

The ODBC types are mapped in the driver to equivalent Adaptive Server datatypes. See the Adaptive Server Enterprise ODBC Driver by Sybase User Guide 15.7.

Execute the stored procedure, sp_datatype_info to get a full list of supported types for the particular Adaptive Server. For example:

$sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?");

	$sth->bind_param(1, 'one', SQL_CHAR);

	$sth->bind_param(2, 2.34, SQL_FLOAT);

	$sth->execute;

	....

	$sth->execute('two', 3.456);

	etc...

For example, consider this stored procedure definition:

declare proc my_proc @p1 numeric(5,2) as...

	$sth = $dbh->prepare("exec my_proc \@p1 = ?");

	$sth->bind_param(1, 3.456, SQL_NUMERIC);

which generates this error:

DBD::SybaseASE::st execute failed: Server message 
number=241 severity=16 state=2 line=0 procedure=my_proc 
text=Scale error during implicit conversion of NUMERIC 
value '3.456' to a NUMERIC field.

Set the arithabort option as follows to ignore these errors:

$dbh->do("set arithabort off");

See the Adaptive Server reference documentation.