Problem:

Using the WITH HOLD Clause for the Declare Cursor with OpenESQL

Resolution:

Support has been added to Open ESQL to support the WITH HOLD syntax of the DECLARE

CURSOR statement. WITH HOLD maintains resources across transactions or multiple units of

work. Cursors defined by WITH HOLD will remain open after a COMMIT. This is of course

dependent on whether the ODBC interface supports this function and whether the ODBC data source has been configured to enable this capability. If WITH HOLD is not supported you will receive sqlcode of -19520 Connection does not support HOLD cursors on the OPEN CURSOR statement.

For example, WITH HOLD does not work with either the Microsoft ODBC driver or the Net

Express Intersolv driver for SQL Server 6.5. However WITH HOLD is enabled using the

Microsoft ODBC driver for SQL Server 7. For Oracle 7 and 8, both Oracle and Net Express

ODBC drivers support the WITH HOLD capability.

For DB2 the data source has to be configured to enable this functionality. With

IBM's UDB ODBC driver, using the Client Configuration Assistant, select properties

for the ODBC data source and proceed to the CLI/ODBC settings for the Advanced tab. You

can set the cursor hold parameter to enable cursor hold (which is the default setting).

For the Net Express Intersolv Driver for DB2 use the ODBC Administrator to configure

your data source. From the Advanced tab for the ODBC DB2 Driver Setup, set the cursor

behavior to 1-Preserve (the default is 0-Close). Preserve will hold cursors at the current

position when the transaction ends.

On the FTP site, hold.cbl (http://downloads.microfocus.com/examplesandutilities/sql/hold.zip) is a sample program that demonstrates the WITH HOLD clause of the DECLARE CURSOR

statement.