Dynamic SQL Support

This section describes the dynamic SQL support provided by Natural SQL Gateway. Natural SQL Gateway does not support static SQL.

SQL Support - General Information

The SQL support of Natural SQL Gateway provides the flexibility of dynamic SQL support.

In contrast to static SQL support, the Natural dynamic SQL support does not require any special consideration with regard to the operation of the SQL interface. All SQL statements required to execute an application request are generated automatically and can be executed immediately with the Natural RUN command. Before executing a program, you can look at the generated SQLCODE, using the LISTSQL command.

Internal Handling of Dynamic Statements

Natural automatically provides for the preparation and execution of each SQL statement and handles the opening and closing of cursors used for scanning a table.

Statement Table

If possible, an SQL statement is only prepared once and can then be executed several times if required. For this purpose, Natural internally maintains a table of all SQL statements that have been prepared. In addition, this table maintains the cursors used by the SQL statements SELECT, FETCH, UPDATE (positioned), and DELETE (positioned).

Each SQL statement is uniquely identified by:

  • the name of the Natural program that contains this SQL statement,

  • the line number of the SQL statement in this program,

  • the name of the Natural library, into which this program was stowed,

  • the time stamp when this program was stowed.

Once a statement has been prepared, it can be executed several times with different variable values, using the dynamic SQL statement EXECUTE USING DESCRIPTOR or OPEN CURSOR USING DESCRIPTOR respectively.

When the full capacity of the statement table is reached, the entry for the next prepared statement overwrites the entry for a free statement whose latest execution is the least recent one.

When a new SELECT statement is requested, a free entry in the statement table with the corresponding cursor is assigned to it and all subsequent FETCH, UPDATE, and DELETE statements referring to this SELECT statement will use this cursor. Upon completion of the sequential scanning of the table, the cursor is released and free for another assignment. While the cursor is open, the entry in the statement table is marked as used and cannot be reused by another statement.

If the number of nested FIND (SELECT) statements reaches the number of entries available in the statement table, any further SQL statement is rejected at execution time and a Natural error message is returned.

Since the statement table is contained in the SQL buffer area, the DB2SIZE parameter may not be sufficient and may need to be increased.