Dynamic SQL

Dynamic SQL statements are built at run time, in a host variable. GixSQL supports the first three “methods” of Pro*COBOL; the fourth one (descriptors, DESCRIBE) is not supported.

Method 1: EXECUTE IMMEDIATE

For a statement without host variables and that returns no rows:

           MOVE "DELETE FROM EMP WHERE DEPTNO = 40" TO SQL-STMT.
           EXEC SQL EXECUTE IMMEDIATE :SQL-STMT END-EXEC.

           EXEC SQL
               EXECUTE IMMEDIATE 'UPDATE TAB1 SET FLD1 = FLD1 + 100'
           END-EXEC.

Method 2: PREPARE and EXECUTE

For a statement executed several times, with input parameters:

       01  DYNSTMT  SQL TYPE IS VARCHAR(200).
       ...
           MOVE "INSERT INTO TAB1 (F1, F2) VALUES (?, ?)" TO DYNSTMT-ARR.
           MOVE FUNCTION LENGTH(FUNCTION TRIM(DYNSTMT-ARR))
             TO DYNSTMT-LEN.
           EXEC SQL PREPARE STMT1 FROM :DYNSTMT END-EXEC.

           MOVE 1 TO T1. MOVE 2 TO T2.
           EXEC SQL EXECUTE STMT1 USING :T1, :T2 END-EXEC.

EXECUTE ... INTO :a, :b USING ... returns the values of a single-row query.

Method 3: prepared queries and cursors

For a query returning several rows:

           EXEC SQL PREPARE STMT2 FROM :DYNSTMT END-EXEC.
           EXEC SQL DECLARE C1 CURSOR FOR STMT2 END-EXEC.
           EXEC SQL OPEN C1 USING :DEPT-NO END-EXEC.
           EXEC SQL FETCH C1 INTO :EMP-NAME END-EXEC.
           ...
           EXEC SQL CLOSE C1 END-EXEC.
NoteTo be verified

OPEN ... USING on a cursor declared on a prepared statement.

Parameter placeholders

Each database has its own syntax for parameters:

  • PostgreSQL: $1, $2…
  • Oracle: :name or :1, :2…
  • ODBC: ?
  • SQLite: any of these.

For static statements, the precompiler generates the placeholders chosen with -z / --param-style: d for $1 (default), a for ?, c for :1. The Oracle driver also accepts $n placeholders.

For prepared statements, the text is written by the program. GixSQL can convert the placeholders of a prepared statement to those of the database, with the connection option fixup_params=on or the environment variable GIXSQL_FIXUP_PARAMS=on.