Problem:

Open ESQL supports 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 functionality and whether the ODBC data source has been configured to enable this capability. If WITH HOLD is not supported you will receive a sqlcode of -19520 Connection does not support HOLD cursors on the OPEN CURSOR statement.

Resolution:

BUILD W/RELEASE:     NETExpress 3.1

The program creates two tables in the database and inserts records into both. A cursor is opened on the first table using the 'WITH HOLD' option of the DECLARE CURSOR statement. A row is fetched and then a row from the other table is updated and committed. The next row is fetched from the table, if sucessful the "WITH HOLD" option prevented the COMMIT from closing the cursor.

SOURCE FILES:

=========

Program Files     Description

----------------     -----------------------------------------------------------

HOLD.CBL          COBOL program that demonstrates the WITH HOLD clause of the DECLARE

                           CURSOR statement.

Copy Files:

----------------     -----------------------------------------------------------

sqlca.cpy

REQUIREMENTS:

==========

This sample requires that a datasource has been created     pointing to any DBMS.

OPERATION:

========

Unzip the hold.zip file. Open NetExpress and open the tst_with_hold project. Rebuild the application and then run or animate it.

The program will prompt for a datasource. Select the desired datasource.  If required, you will be prompted to logon to your database. If the 'WITH HOLD' option is supported by your ODBC driver, the program will display in the Application output window

   HOLD cursors are supported

otherwise the program will display

   HOLD cursors are not supported

   Error (anticipate)=-0000019520(IM001)

   Connection does not suport HOLD cursors

and the program will terminate. If HOLD cursors are supported then the program will determine if a COMMIT will maintain the cursor position. If it does the program will display PASS - fetch on hold cursor otherwise it will display FAIL - fetch on hold cursor.

NOTE:

====

For DB2 the data source has to be configured to enable this capability. 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 - the Advanced tab.  You can set the cursor hold parameter to enable cursor hold (which is the default setting).

For the Data Direct 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.

SQL Server 6 does not support the WITH HOLD functionality.

==========================================================

Keywords: Example, sample, demo, demonstration, SQL, hold.zip

demo.ex

demo.ne