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.
OPEN ... USING on a cursor declared on a prepared statement.
Parameter placeholders
Each database has its own syntax for parameters:
- PostgreSQL:
$1,$2… - Oracle:
:nameor: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.