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.

NoteTo be verified

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:

  1. DECLARE the cursor, with its query;
  2. OPEN it: the query is executed;
  3. FETCH the rows one by one, until the “no record” condition;
  4. CLOSE it.
           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 CURSOR can appear in the DATA DIVISION or in the PROCEDURE DIVISION. It is not an executable statement: it does not set SQLCODE.

  • FETCH does not accept the AT clause: the cursor is used on the connection where it was declared.

  • A cursor declared WITH HOLD stays open after a COMMIT:

             EXEC SQL
                 DECLARE C1 CURSOR WITH HOLD FOR SELECT ...
             END-EXEC.
Important

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_cursors option; 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

See Connections and transactions.

Other statements

NoteTo be completed

EXEC SQL IGNORE, CALL / stored procedures, LOCK TABLE, SET TRANSACTION, DECLARE TABLE.