SQLCODE | Teradata Vantage - SQLCODE - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

In ANSI session mode, SQLCODE is a host variable (embedded SQL) or status variable (stored procedures) that receives 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 or not.

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. In other words, 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 executing 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 via 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 executed always falls into one of three categories, as explained by the following table.

This SQLCODE value … Indicates that …
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:
  • No rows were selected.
  • All selected rows have been processed.

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 ensure 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 then populates it using an INSERT … SELECT statement.

You would write your application code to execute an SQLCODE check immediately after executing 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 it, so you would code WHENEVER statements to execute some appropriate action to prevent executing the INSERT … SELECT or, if all goes as planned, to continue with processing.

You should also test the INSERT … SELECT statement to ensure that subsequent references to the temporary table are valid.

For example, the SQLCODE value might be 0, indicating that one or more rows were successfully selected and inserted.

The value might also be +100, indicating that no rows were selected or inserted, and the table is empty. Any subsequent references to the empty temporary table would be inaccurate in that case, so some action needs to be taken to ensure that further references to the empty temporary table do not occur.

How Teradata Database Error Messages Map to SQLCODE Values

For information on mapping SQLCODE values to Teradata Database error message numbers, see SQL Communications Area (SQLCA).

SQLCODE Usage Constraints for Stored Procedures

The following uses of SQLCODE are valid within a stored procedure:
  • 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;
The following usages of SQLCODE are not valid within a stored procedure:
  • 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 Topics

For more information about: