SQLSTATE

What is SQLSTATE

• SQLSTATE is a 5-character code returned with every SQL statement, along with SQLCODE.
• It is defined by the SQL standard, so the same SQLSTATE means the same thing across database products.
• SQLCODE is DB2-specific. SQLSTATE is portable.
• Both SQLCODE and SQLSTATE are stored in the SQLCA block after each SQL statement.

SQLSTATE classes - the first two characters

• The first two characters are the class. The last three are the subclass.
• 00 = successful completion (00000).
• 01 = warning. Example: value truncated on UPDATE or DELETE.
• 02 = no data. 02000 is the same condition as SQLCODE +100.
• 23 = integrity constraint violation. 23505 is a duplicate key, the same as SQLCODE -803.
• 40 = transaction rollback. 40000 is deadlock or timeout, the same as SQLCODE -911.
• 57 = unavailable resource. 57011 is the same as SQLCODE -904.

Using SQLSTATE in a COBOL program

• SQLSTATE is PIC X(5) in the SQLCA, so compare it with character literals in quotes.
• It is useful when the same program logic must run against DB2 and other databases.
• Example:
  
  WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. ... PROCEDURE DIVISION. MAIN-PARA. EXEC SQL FETCH C1 INTO :WS-EMPNO, :WS-EMPNAME END-EXEC EVALUATE SQLSTATE WHEN '00000' DISPLAY 'Fetch OK: ' WS-EMPNAME WHEN '02000' DISPLAY 'End of cursor reached' PERFORM CLOSE-PARA WHEN OTHER DISPLAY 'SQL error: ' SQLCODE ' ' SQLSTATE STOP RUN END-EVALUATE.