A client application can call a stored procedure on a Adaptive Server or an Open ServerConnect transaction running in a separate CICS or IMS region.
A client application can call a stored procedure or mainframe transaction in two ways:
By executing a SQL language request (for example, “execute myproc”).
By making an RPC.
RPCs have a few advantages over execute statements:
An RPC can execute a Adaptive Server stored procedure or any Open ServerConnect transaction.
A SQL language request can only execute a Adaptive Server stored procedure or a specially written Open ServerConnect language transaction.
When sending a request to a Adaptive Server, it is simpler and faster to accommodate stored procedure return parameters if the procedure is invoked with an RPC instead of a language request.
The following functions are related to RPCs:
ct_remote_pwd sets and clears the passwords that are used when logging into a remote server (This feature is not available for calls to Open ServerConnect).
ct_command initiates an RPC.
ct_param defines parameters for an RPC.
ct_send sends an RPC.
ct_results, ct_bind, and ct_fetch process remote procedure results.
A server can execute a stored procedure or transaction residing on another server. This might occur when a stored procedure being executed on one Adaptive Server contains an execute statement for a stored procedure on another Adaptive Server. The execute command causes the first server to log into the second server and execute the remote procedure. This is called a server-to-server RPC. It happens without any intervention from the application, although the application can specify the remote password that the first server uses to log into the second.
A server-to-server RPC also occurs when an application sends a request to execute a stored procedure that does not reside on the server to which it is directly connected.
SQL commands contained in a stored procedure that is
executed as the result of a server-to-server RPC cannot be rolled
back.
In addition to results generated by the SQL statements they contain, Adaptive Server stored procedures and Open ServerConnect transactions that are executed through an RPC:
Can generate a return parameter result set
Always generate a return status result set
All types of results—rows, status, and parameters—can be processed using ct_results, ct_bind, and ct_fetch.
Adaptive Server stored procedures and mainframe server transactions can return values for specified return parameters. Changes made to the value of a return parameter inside the stored procedure or transaction are then available to the program that called the procedure or transaction. This is analogous to the “pass by reference” facility available in some programming languages.
In order for a parameter to function as a return parameter, it must be declared as such within the stored procedure. For example, Client-Library applications use the ct_param routine to indicate return parameters.
Return parameter values are available to an application as a parameter result set only if the application invoked the stored procedure using an RPC.
ct_results returns CS_PARAM_RESULT if a parameter result set is available to be processed. Because stored procedure parameters are returned to an application as a single row, one call to ct_fetch copies all of the return parameters for a stored procedure into the program variables designated through ct_bind. However, an application should always call ct_fetch in a loop until it returns CS_END_DATA.
When executing a stored procedure, the server returns any parameter values immediately after returning all row results. Therefore, an application can fetch return parameters only after processing the stored procedure row results. A stored procedure can generate several sets of row results, one for each select it contains. An application must call ct_results and ct_fetch as many times as necessary to process these row results before calling ct_fetch to fetch the stored procedure return parameters.
Adaptive Server, Open Server, and Open ServerConnect applications can all return a status.
All stored procedures that run on a Adaptive Server version 4.0 or later return a status. Stored procedures usually return 0 to indicate normal completion. For a list of Adaptive Server default return status values, see return in the Adaptive Server Enterprise Reference Manual, which is part of the basic Sybase documentation set. Open ServerConnect status values are documented under TDSNDDON and TDSTATUS in the Mainframe Connect Server Option Programmer’s Reference for C.
Because return status values are a feature of stored procedures, only an RPC or a language request containing an execute statement can generate a return status.
When executing a stored procedure, Adaptive Server returns the status immediately after returning all other results. Therefore, an application can fetch a return status only after processing the stored procedure row and parameter results, if any.
Open Server applications return the status after any row results, but either before or after return parameters.
ct_results returns CS_STATUS_RESULT if a return status result set is available to be processed. Because a return status result set contains only a single value, one call to ct_fetch copies the status into the program variable designated through ct_bind. However, an application should always call ct_fetch in a loop until it returns CS_END_DATA.
