OpenESQL Stored Procedures

OpenESQL supports two statements that are used with stored procedures:

• CALL

  Provides generic support for ODBC stored procedure calls.

• EXECSP

  Provides backwards compatibility with the Micro Focus Embedded SQL Toolkit for Microsoft SQL Server.

A stored procedure can:

• Accept input parameters
• Return output parameters
• Accept and return input/output parameters
• Use positional or keyword parameters
• Return a result
• Return result sets
• Be called with parameter arrays

When a stored procedure is called, any parameters are passed as a comma separated list, optionally enclosed in parentheses. A parameter can be a host variable or a literal, or the keyword CURSOR. The keyword CURSOR causes the parameter to be unbound, and should only be used with Oracle 8 stored procedures which return result sets.

If the parameter is a host variable it can be followed by one of the following words, which indicate the parameter type: IN, INPUT, INOUT, OUT, OUTPUT. If no parameter type is specified, INPUT is assumed.

Host variable parameters can be passed as keyword parameters, by preceding the host variable with the formal parameter name and an equals sign:

     EXEC SQL CALL myProc (keyWordParam = :hostVar) END-EXEC

For maximum portability, literal parameters should be avoided and only host variable parameters should be used, and a given call should use either only normal, positional parameters or keyword parameters, but not both. Some servers support a mixture, but keyword parameters should occur after all positional parameters. Keyword parameters are useful as an aid to readability and where the server supports default parameter values and optional parameters.

If a stored procedure call returns a result set, it must be used in a cursor declaration, thus:

     EXEC SQL 
         DECLARE cursorName CURSOR FOR storedProcecureCall

The stored procedure is then called by OPENing the cursor and FETCHing result set rows, like any other type of cursor.

Currently OpenESQL supports only a single result set.

ODBC parameters differ fom Oracle array parameters. The effect of using a parameter array is the same as repeating the statement for each element of the array. On a stored procedure call, if one parameter is passed as an array, then all parameters must be arrays with the same number of elements. The stored procedure will "see" one call for each "row" of parameters. The number of rows passed can be limited to less than the full array size by preceding the call with the phrase FOR :hvar where :hvar is an integer host variable containing a count of the numer of rows to be passed.