COBOL Std 6 Embedded SQL Standards GixSQL EXEC SQL

6. Embedded SQL Standards (GixSQL / EXEC SQL)

6.1 SQL Formatting Rules

• All SQL keywords in UPPER CASE.
• Each SQL clause on its own line, indented 11 spaces from column 8 (Area B +3).
• Host variables prefixed with colon (:) and matching the DB- prefix.
• Never use SELECT * — always name columns explicitly.
• Every EXEC SQL block must be followed immediately by SQLCODE evaluation.
• Named database connection (AT clause) required on all transactional SQL statements.

Example — correctly formatted SELECT:

<syntaxhighlight lang="cobol">

          EXEC SQL AT primes
              SELECT ident, prime
              INTO   :DB-IDENT, :DB-PRIME
              FROM   primes
              WHERE  ident = :DB-PRIME-IDENT
          END-EXEC
          EVALUATE SQLCODE
              WHEN 0
                  MOVE 0 TO DAL-RESULT
              WHEN 100
                  MOVE 2 TO DAL-RESULT
              WHEN OTHER
                  MOVE 1 TO DAL-RESULT
                  PERFORM E000-HANDLE-SQL-ERROR
          END-EVALUATE

</syntaxhighlight>

6.2 SQLCODE Handling

Treat SQLCODE as the sole success/failure signal after every SQL statement:

SQLCODE	Meaning	Required Action
0	Success	Set DAL-RESULT = 0; continue.
100	No data / EOF	Set DAL-RESULT = 2; handle gracefully — not an error.
Negative	SQL error	Set DAL-RESULT = 1; log SQLERRMC; PERFORM E000-HANDLE-SQL-ERROR.

Use numeric SQLCODE 100 (not +100) to detect end-of-cursor; this is portable across GixSQL and ANSI SQL. Do not use bare IF SQLCODE = 0 … ELSE for cursor fetch — end-of-data is not an error.

6.3 Cursor Standards

• Declare cursors in the DATA DIVISION (compile-time), not inline in the PROCEDURE DIVISION.
• Always open a cursor inside a named transaction (START TRANSACTION).
• Always use FETCH … INTO with individually named host variables — never rely on implicit column ordering.
• Close cursors explicitly before disconnecting; do not rely on connection reset to close implicitly.
• One cursor per paragraph; do not interleave two cursor-fetch loops.

6.4 Transaction Management

Event	Required Action	Location
After DB connect	START TRANSACTION if reading with a cursor	S000-series (cursor open)
Successful write (INSERT/UPDATE)	COMMIT after each logical unit of work	S000-series (write paragraph)
Error on write	ROLLBACK; set DAL-RESULT = 1	E000-HANDLE-SQL-ERROR
Before DB disconnect	COMMIT or ROLLBACK pending work	Z000-PROGRAM-END

| name = Ambox{{subst:void|Don't change anything on this line. It will change itself when you save.}} | subst = {{subst:substcheck}}

6.5 Connection Management

• Use a named connection alias in every EXEC SQL AT <alias> statement.
• The alias must be consistent across all paragraphs in a program — mismatch causes a runtime alias-not-found error (pgcobol defect D-03).
• Store connection parameters in WORKING-STORAGE named constants using the DB- prefix.
• Move credentials to environment variables in production; hard-coded passwords in source are a security risk (pgcobol defect D-04).

---

---

← Back to index