Embedded SQL statements
Executing SQL statements
Most SQL statements are passed to the database as they are, after the host variables are replaced by parameter placeholders: INSERT, UPDATE, DELETE, DDL statements (CREATE, DROP…), SAVEPOINT, and so on. The precompiler does not check the SQL syntax: errors are reported by the database at run time.
EXEC SQL
INSERT INTO EMP (EMPNO, ENAME, SAL, DEPTNO)
VALUES (:EMP-NUMBER, :EMP-NAME, :SALARY, :DEPT-NO)
END-EXEC.
EXEC SQL
UPDATE EMP SET SAL = :SALARY WHERE EMPNO = :EMP-NUMBER
END-EXEC.
EXEC SQL
DELETE FROM EMP WHERE DEPTNO = :DEPT-NO
END-EXEC.
After an INSERT, UPDATE or DELETE, SQLERRD(3) of the SQLCA contains the number of rows processed.
SQLERRD(3) for each driver.
Single-row queries: SELECT INTO
A SELECT that returns at most one row stores its result directly in host variables:
EXEC SQL
SELECT ENAME, SAL, COMM
INTO :EMP-NAME, :SALARY, :COMM:COMM-IND
FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
If no row is found, SQLCODE is the “no record” code (100 by default, see The “no record” condition). If several rows are found, an error is returned.
Cursors
A query that returns several rows is processed with a cursor:
DECLAREthe cursor, with its query;OPENit: the query is executed;FETCHthe rows one by one, until the “no record” condition;CLOSEit.
EXEC SQL
DECLARE EMPCUR CURSOR FOR
SELECT ENAME, SAL FROM EMP
WHERE DEPTNO = :DEPT-NO
ORDER BY ENAME
END-EXEC.
EXEC SQL OPEN EMPCUR END-EXEC.
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL
FETCH EMPCUR INTO :EMP-NAME, :SALARY
END-EXEC
IF SQLCODE = 0
DISPLAY EMP-NAME " " SALARY
END-IF
END-PERFORM.
EXEC SQL CLOSE EMPCUR END-EXEC.
The input host variables of the query (:DEPT-NO) are read when the cursor is opened.
DECLARE CURSORcan appear in the DATA DIVISION or in the PROCEDURE DIVISION. It is not an executable statement: it does not setSQLCODE.FETCHdoes not accept theATclause: the cursor is used on the connection where it was declared.A cursor declared
WITH HOLDstays open after aCOMMIT:EXEC SQL DECLARE C1 CURSOR WITH HOLD FOR SELECT ... END-EXEC.
With PostgreSQL native cursors, cursors must be used inside a transaction: see Autocommit.
Updatable cursors
The current row of a cursor declared FOR UPDATE can be modified or deleted with WHERE CURRENT OF:
EXEC SQL
DECLARE C2 CURSOR FOR
SELECT ENAME, SAL FROM EMP FOR UPDATE
END-EXEC.
...
EXEC SQL
UPDATE EMP SET SAL = SAL * 1.1 WHERE CURRENT OF C2
END-EXEC.
The support depends on the database:
- Oracle and PostgreSQL (with native cursors) support them directly.
- MySQL and SQLite emulate them, with the
updatable_cursorsoption; the table must have a unique key. - ODBC depends on the ODBC driver.
Cursors on prepared statements
A cursor can also be declared on a prepared statement (see Dynamic SQL):
EXEC SQL DECLARE C3 CURSOR FOR STMT1 END-EXEC.
Transactions
Other statements
EXEC SQL IGNORE, CALL / stored procedures, LOCK TABLE, SET TRANSACTION, DECLARE TABLE.