Editing 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 (<code>:</code>) and matching the <code>DB-</code> prefix.
* '''Never use <code>SELECT *</code>''' — 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:

{| class="wikitable" style="width:100%"
! SQLCODE !! Meaning !! Required Action
|-
| <code>0</code> || Success || Set <code>DAL-RESULT = 0</code>; continue.
|-
| <code>100</code> || No data / EOF || Set <code>DAL-RESULT = 2</code>; handle gracefully — not an error.
|-
| Negative || SQL error || Set <code>DAL-RESULT = 1</code>; log <code>SQLERRMC</code>; PERFORM E000-HANDLE-SQL-ERROR.
|}

Use numeric SQLCODE <code>100</code> (not <code>+100</code>) to detect end-of-cursor; this is portable across GixSQL and ANSI SQL. Do not use bare <code>IF SQLCODE = 0 … ELSE</code> 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 (<code>START TRANSACTION</code>).
* Always use <code>FETCH … INTO</code> 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 ===

{| class="wikitable" style="width:100%"
! Event !! Required Action !! Location
|-
| After DB connect || <code>START TRANSACTION</code> if reading with a cursor || S000-series (cursor open)
|-
| Successful write (INSERT/UPDATE) || <code>COMMIT</code> after each logical unit of work || S000-series (write paragraph)
|-
| Error on write || <code>ROLLBACK</code>; set <code>DAL-RESULT = 1</code> || E000-HANDLE-SQL-ERROR
|-
| Before DB disconnect || <code>COMMIT</code> or <code>ROLLBACK</code> pending work || Z000-PROGRAM-END
|}

{{Ambox
| type  = notice
| text  = '''Defect pattern to avoid''' — The pgcobol reference implementation has the COMMIT statement commented out in the INSERT paragraph (<code>r83-write-prime</code>). This relies on PostgreSQL auto-commit and is not portable. Always include explicit COMMIT/ROLLBACK.
}}

=== 6.5 Connection Management ===

* Use a named connection alias in every <code>EXEC SQL AT &lt;alias&gt;</code> 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 <code>DB-</code> prefix.
* Move credentials to environment variables in production; hard-coded passwords in source are a security risk (pgcobol defect D-04).

----

----
''[[Cobol programming standards|← Back to index]]''