The SPL output parameter feature

HCL OneDB™ ODBC Driver supports the ODBC defined method of getting the return value from a database procedure.

Specifically, ODBC supports the parameter to that precedes the equals sign in a procedure-call escape sequence. The host variable associated with that parameter is updated upon statement execution either with SQLExecute or SQLExecDirect.

In the HCL OneDB ODBC Driver definition of a procedure-call escape sequence, there is only one return value; therefore, the following restrictions are placed on this feature:
  • Procedures used with this feature must return only one value, although they might return multiple rows.

    If this condition is not met, the parameter and its binding are ignored.

  • Data from the first row only be placed in the host variable associated with the bound parameter, although procedures used with this feature can return multiple rows.

To return multiple-value, multiple-row result sets from the HCL OneDB database server, you have to fetch the data as though it were the result columns of a select statement. This output parameter feature works with existing applications that bind column or columnss and call SQLFetch or call SQLFetch and SQLGetData when accessing data through a procedure call. Therefore, no error or warning is generated when more than one row is available to be returned.

You can use either or both methods for retrieving the data from a stored procedure. A host variable can be bound as a parameter or as a column, or both. If separate buffers are used, only the host variable bound as a parameter is updated upon statement execution, and only the host variable bound as a column is updated upon a fetch. Unbound columns accessed through SQLGetData remain unaffected.