ODBC Server Reference Manual

Stored Procedures
HP NonStop ODBC Server Reference Manual—429151-002
5-6
Configuration Options
DON HUE
DEBBIE SMITH
•
If the procedure returns a status code, the caller also receives the execution status.
In the previous example, the execution status is returned in the variable
@RETURN_STATUS.
•
If the procedure is defined to have output parameters and the procedure execution
requests output values, the caller receives those output values. In the following
example, the stored procedure DEBIT_ACOUNT also returns the account balance
in the variable BALANCE.
@RETURN_STATUS = DEBIT_ACCOUNT
@NAME="SMITH",
@AMOUNT=100,
@BALANCE OUT
For DBLIB users, the return status and output parameters are consumed within the
current batch and are not automatically returned to the application.
The result of a stored procedure execution can be obtained as a batch of statements is
processed. Errors regarding the stored procedure are obtained in the same way as for
other SQL statements.
Configuration Options
The configuration options handled by the NonStop ODBC server for execution of SQL
statements are as follows:
SQL_ACCESS_MODE
SQL_TXN_ISOLATION
SQL_CURSOR_MODE
SQL_MAX_ROWS
These configuration options do not apply to stored procedure execution, however.
Development of Stored Procedures
The following subsection provides guidelines for creating stored procedures, using as
an example a set of procedures and files that are included with the NonStop ODBC
Server product for instructional purposes.
Design Considerations
The NonStop ODBC Server support of stored procedures is implemented using
Pathway server programs. A set of stored procedures can be implemented using one
or more Pathway server classes.
The NonStop ODBC Server defines an interprocess communication (IPC) format and
protocol (SPEIPC) between the NonStop ODBC server and the Pathway server. The
NonStop ODBC Server provides a set of library functions (SPELIB) for the Pathway
server program to decode the request and to encode parameters, result sets, and error