Federated procedures for Sybase

Before you create a federated procedure, review which parameters are supported, how result sets are returned, and what limitations exist.

Parameters

Sybase procedures use INPUT and OUTPUT parameters. The Sybase wrapper maps a Sybase INPUT parameter to a federated IN parameter and maps a Sybase OUTPUT parameter to a federated INOUT parameter. Although you can use optional parameters in a Sybase procedure, you cannot use optional parameters in a federated procedure. Therefore, when you issue a CALL statement, you must specify all of the parameters.

For Sybase Version 12.0, all of the parameters are input parameters. Federated procedures cannot return an output parameter value. This is a Sybase catalog limitation and does not apply to later versions of Sybase.

Result sets

For Sybase procedures that return an output parameter and a result set, the result set is discarded. If the Sybase procedure returns a result set and return value, a return value of 0 is provided, regardless of the actual return value from the data source procedure. You will not receive a warning message in either of these situations.

However, the result sets are discarded and the SQL0464W message is returned when both of the following conditions are true:

The federated procedure is defined for a Sybase procedure that returns result sets.
The federated procedure is invoked inside a trigger or a user-defined function.

Limitations

The Sybase wrapper cannot call a procedure in these situations:

When another procedure is already called
When another statement is executed during a single connection

To work around these limitations, you can define the federated procedure on another server. In this Sybase example, the following statements succeed if the nickname syb_nick and the procedure syb_proc are defined on the different servers. If the nickname and the procedure are defined on the same server, the statements fail.

DECLARE clientcur CURSOR FOR SELECT colsml,coldec,colvch,coltsp 
FROM syb_nick OPEN clientcur; CALL syb_proc();

Example

This example shows how to use the CREATE PROCEDURE statement to create a federated procedure for a data source procedure on Sybase. Note that Sybase does not support the UNIQUE ID clause of the CREATE PROCEDURE statement.

CREATE PROCEDURE PROC1 SOURCE BHATIA.PROC1_SYBASE
     NUMBER OF PARAMETERS 3 FOR SERVER SYBASE_SERVER
     SPECIFIC MYPROC1 WITH RETURN TO CLIENT ALL
     MODIFIES SQL DATA DETERMINISTIC EXTERNAL ACTION;

PROC1: Required. Specifies the name of the federated procedure.
SOURCE BHATIA.PROC1_SYBASE: Required. Specifies the schema and name for the Sybase procedure. For Sybase procedures, you specify a two-part name in the CREATE PROCEDURE statement. The format for this two-part name is source_schema_name.source_procedure_name.
NUMBER OF PARAMETERS 3: Specifies the total number of IN, OUT, and INOUT parameters that the Sybase procedure uses. Use this parameter when you have more than one procedure with the same schema name and procedure name. For example, if your schema is BHATIA and you have a PROC1 procedure with three parameters and another PROC1 procedure with one parameter, the name for both of these procedures is BHATIA.PROC1. The value for the NUMBER OF PARAMETERS in the data source procedure indicates which procedure you refer to in the CREATE PROCEDURE statement.
FOR SERVER SYBASE_SERVER: Required. Specifies a server definition where the federated procedure is created.
SPECIFIC MYPROC1: Specifies a unique name for the federated procedure that you are creating. This parameter is used only for federated procedures and is not associated with data source procedures. If you do not specify a unique name, a name is generated by the federated database manager.
WITH RETURN TO CLIENT ALL: Specifies that the result set is returned to the client application. Federation returns a maximum of one result set. If this parameter is not specified, the default is WITH RETURN TO CALLER ALL.
MODIFIES SQL DATA: Indicates the level of data access for SQL statements that are included in the federated procedure. If the clause specified does not match the Sybase procedure, an error message is returned. If you do not specify this clause, the clause for the Sybase procedure is used.
DETERMINISTIC: Specifies if the federated procedure always returns the same results for a given set of argument values. This parameter can improve the performance of the interaction between the federated server and the data source.
EXTERNAL ACTION: Specifies if the federated procedure takes an action that changes the state of an object that is not managed by the database manager.