SQLCA

Writing an SQL Program › SQL Status Checking and Error Handling › SQLCA

SQLCA

The SQL Communication Area (SQLCA) is a data structure to which the DBMS returns information about the execution of an SQL statement.

SQLSTATE

SQLSTATE is a five-character string in which CA IDMS returns the status of the last SQL statement executed. It is divided into a two-character class and a three-character subclass. Standard values are associated with each class and subclass, which minimizes the need for vendors to define their own values and makes applications more portable from one environment to another.

The following list displays the SQLSTATE values that CA IDMS can return. It is divided into sections based on the class (the first 2 characters of the SQLSTATE value). Each subclass (the last 3 characters of the SQLSTATE value) is listed under its associated class.

SQL standard values—Class and subclass values beginning with the characters A-H and 0-4 are established by the SQL standards organizations.
CA IDMS-defined values—Class and subclass values beginning with the characters I-Z and 5-9 are vendor-defined. In this case, they are specific to CA IDMS. (Any subclass value associated with a vendor-defined class is also defined by that vendor.)

SQLSTATE Values

      00 Successful completion
         000  No subclass

      01 Warning
         000  No subclass
         004  String data, right truncation
         00C  SQL-invoked procedure returned result sets
         00D  Additional result sets returned
         00E  Attempt to return too many result sets
         010  Column cannot be mapped
         600  Inconsistent or invalid option
         602  Entity or association already exists
         605  Entity not defined in Catalog
         606  Invalid option for physical DDL
         607  Invalid option for DMCL
         608  Connecting to a dictionary which is missing either or
              or both of DDLCAT/DDLDML areas
         610  Database is inconsistent with request
         611  SQL routine parse error
         612  ADS compilation for an SQL routine failed
         613  Drop of SQL routine completed with warnings
         638  Warning returned from table procedure

      02 No data
         000  No subclass

      07 Dynamic SQL error
         000 No subclass
         001 USING clause does not match dynamic parameter specification
         002 USING clause does not match target specification
         003 Cursor specification cannot be executed
         004 USING clause required for dynamic parameters

      08 Connection exception
         000  No subclass
         004  SQL-server rejected establishment of SQL-connection
         006  Connection failure

      0M Invalid SQL-invoked procedure reference
         000  No subclass

      0N SQL/XML Mapping Error
         000  No subclass
         001  Unmappable XML name
         002  Invalid XML character

      21 Cardinality violation
         000  No subclass

      22 Data Exception
         000  No subclass
         001  String data, right truncation
         002  Null value, no indicator parameter
         003  Numeric value out of range
         005  Error in assignment
         007  Invalid datetime format
         008  Datetime field overflow
         00J  Nonidentical notations with the same name
         00K  Nonidentical unparsed entities with the same name
         00L  Not an XML document
         00M  Invalid XML document
         00N  Invalid XML content
         00R  XML value overflow
         00S  Invalid comment
         00T  Invalid processing instruction
         011  Substring error
         012  Division by zero
         019  Invalid escape character

      23 Constraint violation
         000  No subclass
         501  Duplicate key violation
      24 Invalid cursor state
         000  No subclass

      25 Invalid transaction state
         000  No subclass
         006  Read-only SQL-transaction

      26 Invalid SQL statement name
         000  No subclass

      28 Invalid authorization specification
         000  No subclass
         602  Entity or association already defined
         605  Entity or association not previously defined
         607  Authorization ids not specified

      2C Invalid character set name
         000  No subclass

      34 Invalid cursor name
         000  No subclass

      37 Syntax error or access rule violation
         000  No subclass

      38 External routine exception
         000  No subclass
         999  ADS dialog failed or dialog does not exist

      39 External routine invocation exception
         000  No subclass

      3F Invalid schema name
         000  No subclass

      40 Transaction rollback
         000  No subclass
         001  Serialization failure

      42 Syntax error or access rule violation
         000  No subclass
         500  Table not found
         501  Column not found
         502  Entity already defined
         503  Authorization failure
         504  Cursor not declared or previously declared
         505  Entity not found
         506  Invalid identifier
         507  Keyword used as identifier
         600  Invalid statement
         601  Statement not valid in this context
         603  Statement not valid for this schema
         604  Invalid data type
         606  Invalid statement option
         607  Missing statement option
         609  Invalid constraint definition
         610  Invalid number of columns

      50 CA-defined errors
         000  No subclass
         002  Limit exceeded
         003  Space exceeded
         00B  Internal error
         00I  Schema mismatch
         00J  Invalid entity definition
         00K  Uncategorized error
         00L  Invalid calling parameters

      60 &U$IDMS. specific errors
         000  No subclass
         001  Problem with load module or synchronization stamps
         002  Database error
         003  Rollback failed
         004  Failure while opening or describing a received cursor
         005  Unexpected error from GET/PUT SCRATCH

      64 &U$IDMS. Physical DDL error
         000  No subclass

      6U &U$IDMS. Utility error
         000  No subclass

SQLCODE

For status checking, another important field in the SQLCA structure is SQLCODE. The following table shows the values that the DBMS may return to this field.

Value	Meaning
< 0	The SQL statement returned an error (see the following error values)
0	The SQL statement was executed successfully
1	The SQL statement was executed successfully with a warning
100	There are no more rows associated with the current query, or no rows satisfied the search criteria in a searched update or delete Note: The SQL standard only defines meaning to the values of 0 and 100. Negative SQLCODE values signify an error; however, specific values are not standardized as with SQLSTATE.

SQLCODE Error Values

The following table associates SQLCODE error values with one of the three kinds of SQL statement failure and suggests the appropriate error-handling strategy for each category of error:

Value	Level of failure	Meaning
-7	Task	An internal error caused a task abend, leading to rollback and termination of the SQL transaction and termination of the SQL session
-6	SQL session	An error caused an SQL session failure, leading to rollback, termination of the SQL transaction and termination of the SQL session. A program intending to retry the SQL statements should first terminate the SQL session with one of these statements: ROLLBACK RELEASE RELEASE The equivalent TP monitor command
-5	SQL transaction	An error has caused an SQL transaction failure, leading to rollback and termination of the SQL transaction. A program intending to retry the SQL statements should first terminate the transaction with one of these statements: ROLLBACK ROLLBACK RELEASE RELEASE The equivalent TP monitor command
-4	SQL statement	An error has caused failure of the SQL statement to execute; the effect of the statement, if any, on the database has been rolled out. Unless the reason for the error is one that the program can handle, the program should terminate the session or transaction.

SQLCERC

If an error is returned, the DBMS also returns a value in the SQLCERC field of the SQLCA. The value in this field is the SQL error code.

In certain cases, you can use this information to recover from error conditions. For example, if 1038 is returned to SQLCERC, a deadlock has occurred. The application program can handle the deadlock by first terminating the database transaction and then resuming processing after the last commit or start of transaction.

SQLCERC values correspond to the last four digits of the CA IDMS/DB runtime messages. To determine the meaning of a particular SQLCERC value, refer to the text and description of the equivalent DB message.

Note: For more information about the documentation of DB messages, see the CA IDMS Messages and Codes Guide, or issue a DCMT DISPLAY MESSAGE DBnnnnnn statement, as documented in the CA IDMS System Tasks and Operator Commands Guide.

Other SQLCA Fields

For error checking and reporting, these are other useful SQLCA fields:

Field	Description of contents
SQLCLNO	Source file line number from which the SQL statement was obtained
SQLCSER	Offset into the SQL statement buffer where a syntax error was recognized
SQLCNRP	Number of rows processed by the SQL statement
SQLCERM	Text of the error message
SQLCERL	Length of error message text
SQLCNRRS	Actual number of results sets that an SQL invoked procedure returns

How SQLCA Is Initialized

The DBMS initializes SQLCA values on every SQL statement execution. If the program accesses the SQLCA after issuing an SQL statement, all SQLCA values refer to that statement.

SQLPIB Fields

When you display or log error information, you may wish to include information in fields of the SQL Program Information Block (SQLPIB):

Field	Description of contents
SQLDTS	Date and time the program was compiled
SQLPGM	Name of the RCM that is the source of the SQL statement