Dynamic SQL Interface

If it is necessary for the application program to construct SQL statements on the fly at run time, the BIND-SETUP and SELECT-SETUP methods of describing bind and select data are not appropriate. For this case, the SQL API provides an alternative interface in which the bind and select data items are passed in arrays of descriptors, with a data type, length, and pointer for each item. When using this interface, the CALL interface is different:

CALL ‘PTPSQLRT' USING action,

  sqlrt,

  cursor,

  statement,

  bind-table,

  bind-table[place-keeper],

  select-table

The parameters correspond to the CALL used for executing predefined statements, but different values and formats for the following parameters indicate that this is a dynamic statement.

1. Action

This is a one (1) character code that indicates the type of statement. Use one of the data names with pattern "ACTION-" from copy member PTCSQLRT.

Example

ACTION-SELECT OF SQLRT

These are the dynamic actions provided:

Action Name	Service Provided
ACTION-SELECT	Process a SELECT statement.
ACTION-UPDATE	Process an UPDATE, INSERT, or DELETE statement.

Other actions, such as ACTION-FETCH, use the same calls as for predefined SQL, as documented previously.

2. SQLRT

This is the 01-level of copy data division PTCSQLRT used to send and return several sub-parameters and to provide work space.

The individual elements in PTCSQLRT are described in the "Interface Data" topic previously.

3. SQL-Cursor

A four (4) digit computational number that represents a resource connection unit. Copy data division SQLRT contains a common cursor for use when resources do not have to be saved for reuse.

Examples

SQL-CURSOR-COMMON OF SQLRT

SQL-CURSOR OF S-CHECK

4. Statement

The first three characters of the statement parameter are used to determine if the current statement is a dynamic statement, rather than the name of a predefined statement.

These are the allowable dynamic statements:

SELECT

INSERT

UPDATE

DELETE

5. Bind-Table

Use this parameter to specify a table form of bind setup information. This has the same format as the following Select-Table.

6. Bind-Table (place-keeper)

Because the bind data is supplied through pointers, the address of a bind data area is not applicable to dynamic calls. However, you must pass a parameter as a place-keeper, so provide the bind table a second time.

7. Select-Table

Use this parameter to specify the table form of select setup information. The first character indicates that the table form of setup list is in use. The SETUP-COUNT variable gives the number of entries, that is, the number of bind or select items. Each SETUP-ENTRY gives the address, length, scale (number of decimal places), and type of the corresponding data field.

 01  SQLSI.

     02  SETUP-FORMPIC X.

   88  SETUP-FORM-TBL   VALUE 'Y'.

     02  SETUP-COUNT     PIC 999  COMP.

   88  SETUP-COUNT-MAX  VALUE 100.

     02  SETUP-ENTRY  OCCURS 100

INDEXED BY

SQLSI-IDX.

   03  SETUP-DATA-PTR    POINTER.

   03  SETUP-LENGTHPIC 999  COMP.

   03  SETUP-SCALE PIC 99   COMP.

   03  SETUP-TYPE  PIC X.

88  SETUP-TYPE-CHAR    VALUE 'C'.

88 SETUP-TYPE-SMALLINT VALUE 'S'.

88 SETUP-TYPE-INTVALUE 'I'.

88 SETUP-TYPE-DECVALUE 'P'.

88 SETUP-TYPE-DATE     VALUE 'D'.

88 SETUP-TYPE-TIME     VALUE 'T'.