Federated procedures for Sybase
Before you create a federated procedure, review which parameters are supported, how result sets are returned, and what limitations exist.
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.
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.
- 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.
- When another procedure is already called
- When another statement is executed during a single connection
DECLARE clientcur CURSOR FOR SELECT colsml,coldec,colvch,coltsp FROM syb_nick OPEN clientcur; CALL syb_proc();
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;
- 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.
- 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.