In ANSI session mode, SQLCODE is a host variable (embedded SQL) or status variable (stored procedures) that gets SQL statement status information (error or warning code and the condition of an SQL statement, including control statements). The status codes permit an application program to test whether an executable embedded SQL statement completed successfully.
In Teradata session mode (for embedded SQL), SQLCODE is a field within SQLCA.
ANSI Compliance
SQLCODE is not ANSI/ISO SQL:2011-compliant. SQLCODE was deprecated in the ANSI/ISO SQL-92 standard and is not defined in the SQL:2011 standard. The ANSI/ISO SQL committee recommends that new applications be written using SQLSTATE in place of SQLCODE.
SQLCODE is required for all embedded SQL applications written for ANSI session mode that do not specify a SQLSTATE host variable. That is, you must specify one or the other (or both) for any embedded SQL application you write, and SQLSTATE is the preferred choice.
A stored procedure application can test the status of either SQLCODE or SQLSTATE or both.
The SQLCODE field within the SQLCA is also not defined in the ANSI/ISO SQL-99 and SQL:2011 standards, nor is SQLCA. Stored procedures do not use SQLCA.
SQLCODE in ANSI Session Mode
SQLCODE is defined as a 32-bit signed integer.
If SQLCODE is not defined within an SQL DECLARE SECTION in an embedded SQL application, then Preprocessor2 assumes that a valid SQLCODE is defined within the program.
You can test the status values of either SQLCODE or SQLSTATE for stored procedure applications.
SQLCODE in Teradata Session Mode
The SQLCODE field within SQLCA communicates the result of running an SQL statement to an embedded SQL application program. Stored procedures do not use SQLCA.
When the Preprocessor2 option SQLFLAGGER or -sf is set to NONE, then SQLCODE is defined in an embedded SQL application program using SQLCA. Otherwise, you must define SQLCODE explicitly in your application.
You can test the status values of either SQLCODE or SQLSTATE for stored procedure applications.
SQLCODE Value Categories
The SQLCODE value returned to an application after an embedded SQL or stored procedure statement is run falls into one of three categories, as explained by the following table.
| SQLCODE Value | Meaning |
|---|---|
| Negative | An error occurred during processing. The nature of the error is indicated by the numeric value of the code. |
| 0 | Processing was successful. |
| Positive | Termination was normal. Positive values other than 0 and +100 indicate system warnings. For example, an SQLCODE value of +100 indicates one of the following results:
|
When SQLCODE Is Updated
SQLCODE is updated during runtime after each executable statement has been processed. You must write your own application code to test the status codes written to the SQLCODE variable.
When to Test SQLCODE
Test SQLCODE after each execution of an SQL statement to make sure that the statement completes successfully or that an unsuccessful statement is handled properly.
You must also write code to resolve unacceptable SQLCODE values.
SQLCODE Testing Example
Consider an application that creates a temporary table and populates it using an INSERT ... SELECT statement.
Write your application code to run an SQLCODE check immediately after running the CREATE TABLE statement.
If this statement fails to create the table successfully, there is no reason to process the INSERT ... SELECT statement that follows the CREATE TABLE statement, so code WHENEVER statements to run appropriate action to prevent running the INSERT ... SELECT or, if all goes as planned, to continue with processing.
Test the INSERT ... SELECT statement to make sure that subsequent references to the temporary table are valid.
For example, the SQLCODE value may be 0, indicating that one or more rows were successfully selected and inserted.
The value may also be +100, indicating that no rows were selected or inserted, and the table is empty. Subsequent references to the empty temporary table are inaccurate, so action must be taken to make sure that further references to the empty temporary table do not occur.
How Database Error Messages Map to SQLCODE Values
For information on mapping SQLCODE values to database error message numbers, see SQL Communications Area (SQLCA).
SQLCODE Usage Constraints for Stored Procedures
- When specified as the operand of a SET statement.
For example, the following statement is valid.
SET h1 = - SQLCODE; IF SQLCODE = h1 THEN ... ... END IF;
- When specified as an expression in an SQL statement within a stored procedure.
For example, the following statements are valid.
INSERT INTO table_1 (column_1) VALUES (:SQLCODE); UPDATE table_1 SET column_1 = column_1 + :SQLCODE;
- SQLCODE cannot be declared explicitly.
- SQLCODE cannot be SET to a value or an expression.
For example, the following statement is not valid.
SET SQLCODE = h1 + h2;
- SQLCODE cannot be specified in the INTO clause of a SELECT statement.
For example, the following statement is not valid.
SELECT column_1 INTO :SQLCODE FROM table_1;
- SQLCODE cannot be specified in place of the INOUT and OUT parameters of a CALL statement.
Related Information
- SQLCODE, see SQL Communications Area (SQLCA).
- SQLSTATE, see SQLSTATE Result Code Variable.
- For embedded SQL applications, see WHENEVER for information about condition handling.
- For stored procedure applications, see Completion, Exception, and User-defined Condition Handlers for information about condition handling.