Problem:

How is it possible to set the SQL directive CURSOR_CLOSE_ON_COMMIT from COBOL?

Resolution:

Only specific SET statements can be executed through the static form of the exec sql statement - such as the ones specifically identified in the Net Express documentation i.e. set autocommit, set connection, set concurrency, set scrolloption etc.  

All other set statements need to be handled by the EXECUTE IMMEDIATE statement as follows:

          MOVE "SET CURSOR_CLOSE_ON_COMMIT ON" TO WS-MYSQL

          EXEC SQL EXECUTE IMMEDIATE :WS-MYSQL END-EXEC

where ws-mysql is declared as a character string.

Alternatively using the same method as described above, you could set the SQL directive ANSI_DEFAULTS. When enabled (ON), this option enables the following SQL-92 settings:

SET ANSI_NULLS

SET CURSOR_CLOSE_ON_COMMIT

SET ANSI_NULL_DFLT_ON

SET IMPLICIT_TRANSACTIONS

SET ANSI_PADDING

SET QUOTED_IDENTIFIER

SET ANSI_WARNINGS

As you'll see, CURSOR_CLOSE_ON_COMMIT is part of this collection of directives.

Further details on EXECUTE IMMEDIATE is contained in the Net Express documentation.