SQLCA Fields | Teradata Vantage - SQLCA Fields - 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™

SQLCA is used with embedded SQL applications only. Stored procedures using SQLCODE to report status declare an SQLCODE variable and interrogate it to determine statement status.

The SQLCA fields are described in the following table:

Field Name Format Description
SQLCAID CHARACTER(8) Contains the characters ‘SQLCA.   ’
SQLCABC INTEGER Length of the SQLCA (136 (x’88’)).
SQLCODE INTEGER Primary indicator of the result of SQL statement execution.
  • If SQLCODE is 0, the statement executed normally.
  • If SQLCODE is positive, a non-error exception occurred. Examples are no more data was found or various non-fatal warnings.
  • If SQLCODE is negative, the statement failed because of an error condition.

The possible values for SQLCODE and their definitions are detailed in Messages Reference.
SQLERRM VARCHAR(70) Contains the error message inserts for the SQLCODE associated with variable information.

The SQLERRM field inserts are presented to the application as a single character string. The length of the string is provided, but the lengths of the individual inserts are not.

The string begins with a 16-bit word that contains the length of the remaining data.

The data consists of as many as 70 characters of insert text, with the character X’FF’ serving as a separator between inserts.

If the inserts and separator characters are greater than 70 characters, then the array is truncated at the right.

As an example, with a SQLCODE of -552, which is a privileges violation, SQLERRM contains the following three or four inserts:

  • The user name for the user who does not have the required privilege
  • The name of the privilege that was unavailable
  • The name of the database on which the privilege was required
  • The name of the table, view or macro or stored procedure on which the privilege was required, unless it was a database level privilege
SQLERRP CHARACTER(8) Contains the name of the preprocessor module that detected the error.
SQLERRD 6-word array Contains miscellaneous information stored in an array.

Because array addressing nomenclature differs among C, COBOL, and PL/I, the following description of the six SQLERRD words is not numbered.

In order, the six words are the following:
  • The CLIv2, TDP or Teradata Database error code.
  • Reserved for future use.
  • The number of rows processed, where applicable.

    This field is generally referred to as the Activity Count.

    As an example, the number of rows selected upon OPEN of a selection cursor is returned to the application in this word.

  • The relative cost estimate. Its value, as returned by the Teradata Database, is set by the PREPARE statement and can be used to compare the estimated cost of different dynamic SQL statements, in CPU cycles, and so forth.
  • Reserved for future use.
  • Reserved for future use.
SQLWARN CHARACTER(11) array Indicates the presence of warning conditions.

Except for SQLWARN6, each character takes either the value pad character or ‘W’.

The 11 characters of SQLWARN are defined as follows:
  • SQLWARN0 indicates whether any of the remaining ten warning codes have been set, as shown by the following:
    • W means one or more of the other ten codes contains a ‘W’ or SQLWARN6 contains a ‘W’ or ‘R’.
    • pad character means the remaining ten characters are also pad characters.
  • For SQLWARN1:
    • W means one or more output character values or byte strings were truncated because the host variables designated to receive them were too small.

      If this condition occurs, the indicator variables for the truncated values contain the lengths before truncation.

    • pad character means that no truncation occurred.
  • For SQLWARN 2:
    • W means that a warning has been issued by the Teradata Database. The SQLCODE status variable contains the warning code.
    • pad character means that no warning issued.
  • For SQLWARN 3:
    • W means that he number of columns returned by a SELECT was not equal to the number of host variables in the INTO clause.

      The number of variables actually returned to the application program is the lesser of these two values.

    • pad character means that the number of columns returned by a SELECT was a match to the number of host variables in the INTO clause.
  • SQLWARN 4 is reserved for future use.
  • SQLWARN 5 is reserved for future use.\
  • For SQLWARN 6:
    • W means that the statement caused Teradata SQL implicitly to roll back a unit of work. An example of this might be because a deadlock was detected.
    • R means that a retryable error occurred.
    • pad character that there was no rollback or error
  • SQLWARN 7 is reserved for future use.
  • SQLWARN8 is reserved for future use.
  • SQLWARN9 is reserved for future use.
  • SQLWARNA is reserved for future use.
   
SQLEXT CHARACTER(5) Contains the SQLSTATE value associated with the SQLCODE