Chapter 47 – SQL/CLI: Diagnostic Functions

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

The book and the standard does not reflect the features of CrateDB, we are just publishing it as a service for the community and for reference purposes.

On the one hand, CrateDB does not implement the SQL-99 standard thoroughly, on the other hand, it extends the standard for implementing cluster features and others.

For more information specific to CrateDB, check out the CrateDB Reference documentation.

What if something goes wrong? Every env and dbc and stmt and desc has a structure called the Diagnostics Area. The DBMS fills it with information about what happened during the last function call. The diagnostics area has one “header” and zero or more “status records”. This partial illustration shows only the most important fields:

-----------------------------------------
- SQL_DIAG_RETURNCODE | SQL_DIAG_NUMBER -
- --------------------+------------------
- -            -1     | 00003           |
-----------------------------------------
-
-     ----------------------------------------------------------------------
-     - SQL_DIAG_SQLSTATE | SQL_DIAG_MESSAGE_TEXT                    | ... |
-     ---------------------------------------------------------------------
-   1 - 23000      | Integrity Constraint Violation - constraint <X> | ... |
-   2 - 01003      | Warning - null value eliminated in set function | ... |
-   3 - 01008      | Warning - implicit zero-bit padding             | ... |
-     ---------------------------------------------------------------------

This diagram shows the diagnostics area of a stmt, just after this function call:

sqlreturn = SQLExecDirect(
   hstmt,"UPDATE T SET a=(SELECT MAX(b) FROM X),c=X'5'",SQL_NTS);

Looking at the header, we can see that the function failed because the SQL_DIAG_RETURNCODE field is -1, which is SQL_ERROR (sqlreturn will also equal -1). Also, from the fact that the header’s SQL_DIAG_NUMBER field is 3, we can see that there are three status records.

Looking at the status records, we can see that three different things went wrong during the execution of the SQL statement. Two of them were merely warnings (“completion conditions”). Probably the DBMS encountered these conditions while it was setting up for the UPDATE, but it kept on going. Then it hit a showstopper: an “exception condition”. Although this “integrity Constraint violation” error was the third condition encountered, it is the first in order among the status records because an error’s priority is higher than a warning’s.

The SQL_DIAG_SQLSTATE field contains a code for a reasonably precise categorization of the condition. This code is called the status code, or SQLSTATE value (because the DBMS always puts the status code in the SQL_DIAG_SQLSTATE field). You can see what the SQLSTATE codes mean by looking at the chart of codes at the end of this chapter.

The SQL_DIAG_MESSAGE_TEXT field might be the sort of text you’d like to send to the user’s screen for this condition. Unlike the status code, the message text is implementation-dependent: it’s not standardized. It might be internationalized; that is, it might not be in English. So there is a lot more information here than a mere “failed” return code. And there are many more fields than the ones in the picture, which can help you get an even more precise diagnosis of “why didn’t the SQLExecDirect function work”. In order to retrieve the diagnostics-area information into your application program, you need to use one of these CLI functions:

• SQLGetDiagField – You’ll need this function to get any field from any part of the diagnostics area, one field at a time.

• SQLGetDiagRec – With this function, you can pick up several of the most popular fields, including SQL_DIAG_SQLSTATE and SQL_DIAG_MESSAGE_TEXT.

• SQLError – You’ll want to know about this slightly obsolescent function because it appears frequently in legacy code.

• SQLRowCount – This isn’t exactly a diagnostics function, but it does get a value from a particular diagnostics-area field: SQL_DIAG_ROW_COUNT.

The descriptions of these four functions follow.

Table of Contents

SQLGetDiagField

Function Prototype:

SQLRETURN SQLGetDiagField(
  SQLSMALLINT HandleType,       /* 16-bit input */
  SQLINTEGER Handle,            /* 32-bit input */
  SQLSMALLINT RecordNumber,     /* 16-bit input */
  SQLSMALLINT DiagIdentifier,   /* 16-bit input */
  SQLPOINTER DiagInfo,          /* ANY* output */
  SQLSMALLINT BufferLength,     /* 16-bit input */
  SQLSMALLINT *StringLength     /* 16-bit output */
  );

Job: Get one piece of information from a diagnostics area, for example, the SQLSTATE of a warning that was posted for the last function call.

There are 50 variations of SQLGetDiagField, depending on the diagnostics field whose value you want to examine. Here’s an example and short description of each variation; each uses these shorthands:

• The words “last call” mean “the last function called using this handle, other than SQLGetDiagField or SQLGetDiagRec or SQLError”. The principle (anti-Heisenbergian) here is that the act of observation must not affect the thing observed, so the diagnostics routines don’t themselves post diagnostics information.

• The punctuation …,…, at the beginning of each function example’s parameter list means “assume there is a valid handle type and handle here”. The HandleType parameter must be SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT or SQL_HANDLE_DESC. The corresponding Handle parameter must be a henv or hdbc or hstmt or hdesc. Where the only acceptable value is a hstmt, the parameter list starts with SQL_HANDLE_STMT,hstmt.

• The name used for the DiagInfo parameter gives an indication of the <data type> that SQLGetDiagField returns: smallint, integer or character string.

• The word NULL in a function’s argument list means “doesn’t matter”. None of the diagnostics fields contain NULL in the SQL sense.

• The four-digit number at the beginning of each paragraph is the code for the DiagIdentifier parameter. We have done the same thing here that we did in our chapter on the desc functions; namely, treating the name of the sqlcli.h code constant as the name of the field.

Diagnostics Fields – Header

The nine “Header” fields in a diagnostics area occur only once. It does not matter what you pass for the RecordNumber parameter.

0001

SQLGetDiagField(...,...,NULL,SQL_DIAG_RETURNCODE,&smallint,NULL,NULL);

This field gives you the last call’s return code: SQL_SUCCESS, SQL_ERROR, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA or SQL_NO_DATA. You need to call this if you failed to save the return code in an sqlreturn variable.

0002

SQLGetDiagField(...,...,NULL,SQL_DIAG_NUMBER,&integer,NULL,NULL);

This field gives you the number of Status Records (exception or completion conditions) that the DBMS generated for the last call. The value will be zero if the return code is SQL_SUCCESS, and will probably (but not certainly) be zero if the return code is SQL_NO_DATA.

0003

SQLGetDiagField(
   SQL_HANDLE_STMT,hstmt,NULL,SQL_DIAG_ROW_COUNT,&integer,NULL,NULL);

If the last call was SQLExecDirect or SQLExecute for an UPDATE, DELETE or INSERT statement, this field gives you the number of rows affected. Read about the SQLRowCount function, which returns the same information. You must call this function immediately after calling SQLExecDirect or SQLExecute.

0007

SQLGetDiagField(
   SQL_HANDLE_STMT,hstmt,NULL,SQL_DIAG_DYNAMIC_FUNCTION,charstring,
   sizeof(charstring),&charstring_size);

If the last call was SQLExecDirect or SQLExecute, this field gives you a string that describes the type of SQL statement executed. Usually this is the first two or three <keyword>s in the statement. The official list of SQL statements and their function codes is shown at the end of this section.

0012

SQLGetDiagField(
   SQL_HANDLE_STMT,hstmt,NULL,SQL_DIAG_DYNAMIC_FUNCTION_CODE,
   &integer,NULL,NULL);

If the last call was SQLExecDirect or SQLExecute, this field gives you the code value for the type of SQL statement executed; see the codes in the “DYNAMIC_FUNCTION and DYNAMIC_FUNCTION_CODE” lists, above. If you allow users to type in SQL statements, it’s handy to call SQLPrepare and then call this function, so you know what kind of SQL statement it is before you call SQLExecute.

0013

SQLGetDiagField(SQL_HANDLE_STMT,hstmt,NULL,SQL_DIAG_MORE,&integer,NULL,NULL);

The return value for this field is either 1 “true” or 0 “false”: if there are more status records than would fit in the diagnostics area, you get a “true” code here. (Actually the Standard says that the returned value is ‘Y’ or ‘N’ but that must be an error.) You may or may not be able to change the maximum size of the diagnostics area with SET TRANSACTION ... DIAGNOSTICS SIZE statement.

0034

SQLGetDiagField(
   ...,...,NULL,SQL_DIAG_TRANSACTIONS_COMMITTED,&integer,NULL,NULL);

This field gives you the number of transactions committed.

0035

SQLGetDiagField(
   ...,...,NULL,SQL_DIAG_TRANSACTIONS_ROLLED_BACK,&integer,NULL,NULL);

This field gives you the number of transactions rolled back.

0036

SQLGetDiagField(...,...,NULL,SQL_DIAG_TRANSACTION_ACTIVE,&integer,NULL,NULL);

This field gives you a 1 “true” if a transaction is currently active. (A transaction is active if a Cursor is open or the DBMS is waiting for a deferred parameter.)

SQL_DIAG_DYNAMIC_FUNCTION Codes

If the last call was SQLExecDirect or SQLExecute, SQLGetDiagField's SQL_DIAG_DYNAMIC_FUNCTION gives you a string that describes the type of SQL statement executed. As we said earlier, this is usually the first two or three <keyword>s in the SQL statement. Here’s the official list of SQL statements and their function codes (note that not all these SQL statements are executable in a CLI context; we have given a full list here so as to avoid repetition elsewhere).

List of SQL Statements and Codes in SQL-92, but not in CLI

DYNAMIC_FUNCTION             DYNAMIC_FUNCTION_CODE
(string)                     (number)  (sqlcli.h definition)
ALLOCATE CURSOR                1       not defined
ALLOCATE DESCRIPTOR            2       not defined
CREATE TRANSLATION            79       not defined
DEALLOCATE DESCRIPTOR         15       not defined
DEALLOCATE PREPARE            16       not defined
DESCRIBE                      20       not defined
DYNAMIC CLOSE                 37       not defined
DYNAMIC DELETE CURSOR:        38       not defined
   (positioned)
DYNAMIC FETCH                 39       not defined
DYNAMIC OPEN                  40       not defined
DYNAMIC UPDATE CURSOR:        42       not defined
   (positioned)
EXECUTE                       44       not defined
EXECUTE IMMEDIATE             43       not defined
FETCH                         45       not defined
GET DESCRIPTOR                47       not defined
PREPARE                       56       not defined
SELECT (multiple row)         21       not defined
SET CURRENT_PATH              69       not defined
SET DESCRIPTOR                70       not defined

List of Additional SQL Statements and Codes in SQL-92 and CLI

DYNAMIC_FUNCTION             DYNAMIC_FUNCTION_CODE
(string)                     (number)  (sqlcli.h definition)
''                             0       <unknown statement type>
ALTER DOMAIN                   3       SQL_DIAG_ALTER_DOMAIN
ALTER TABLE                    4       SQL_DIAG_ALTER_TABLE
CLOSE CURSOR                   9       SQL_DIAG_CLOSE_CURSOR
COMMIT WORK                   11       SQL_DIAG_COMMIT
CONNECT                       13       SQL_DIAG_CONNECT
CREATE ASSERTION               6       SQL_DIAG_CREATE_ASSERTION
CREATE CHARACTER SET           8       SQL_DIAG_CREATE_CHARACTER_SET
CREATE COLLATION              10       SQL_DIAG_CREATE_COLLATION
CREATE DOMAIN                 23       SQL_DIAG_CREATE_DOMAIN
CREATE SCHEMA                 64       SQL_DIAG_CREATE_SCHEMA
CREATE TABLE                  77       SQL_DIAG_CREATE_TABLE
CREATE VIEW                   84       SQL_DIAG_CREATE_VIEW
DECLARE CURSOR               101       SQL_DIAG_DECLARE_CURSOR
DELETE CURSOR                 18       SQL_DIAG_DELETE_CURSOR
DELETE WHERE                  19       SQL_DIAG_DELETE_WHERE
DISCONNECT                    22       SQL_DIAG_DISCONNECT
DROP ASSERTION                24       SQL_DIAG_DROP_ASSERTION
DROP CHARACTER SET            25       SQL_DIAG_DROP_CHARACTER_SET
DROP COLLATION                26       SQL_DIAG_DROP_COLLATION
DROP DOMAIN                   27       SQL_DIAG_DROP_DOMAIN
DROP SCHEMA                   31       SQL_DIAG_DROP_SCHEMA
DROP TABLE                    32       SQL_DIAG_DROP_TABLE
DROP TRANSLATION              33       SQL_DIAG_DROP_TRANSLATION
DROP VIEW                     36       SQL_DIAG_DROP_VIEW
DYNAMIC DELETE CURSOR:        54       SQL_DIAG_DYNAMIC_DELETE_CURSOR
   (preparable, positioned)
DYNAMIC UPDATE CURSOR         55       SQL_DIAG_DYNAMIC_UPDATE_CURSOR
   (preparable, positioned)
GRANT                         48       SQL_DIAG_GRANT
INSERT                        50       SQL_DIAG_INSERT
OPEN                          53       SQL_DIAG_OPEN
REVOKE                        59       SQL_DIAG_REVOKE
ROLLBACK WORK                 62       SQL_DIAG_ROLLBACK
SELECT (single row)           65       SQL_DIAG_SELECT
SELECT (dynamic single row)   41       SQL_DIAG_SELECT
SELECT CURSOR                 85       SQL_DIAG_SELECT_CURSOR
  (dynamic multiple row)
SET CATALOG                   66       SQL_DIAG_SET_CATALOG
SET CONNECTION                67       SQL_DIAG_SET_CONNECTION
SET CONSTRAINT                68       SQL_DIAG_SET_CONSTRAINT
SET NAMES                     72       SQL_DIAG_SET_NAMES
SET TIME ZONE                 71       SQL_DIAG_SET_TIME_ZONE
SET SESSION AUTHORIZATION     76       SQL_DIAG_SET_SESSION_AUTHORIZATION
SET SCHEMA                    74       SQL_DIAG_SET_SCHEMA
SET TRANSACTION               75       SQL_DIAG_SET_TRANSACTION
UPDATE CURSOR (positioned)    81       SQL_DIAG_UPDATE_CURSOR
UPDATE WHERE                  82       SQL_DIAG_UPDATE_WHERE

List of Additional SQL Statements and Codes in SQL3, but not in CLI

DYNAMIC_FUNCTION             DYNAMIC_FUNCTION_CODE
(string)                     (number)  (sqlcli.h definition)
ALTER MODULE                  95       not defined
ALTER ROUTINE                 17       not defined
ALTER TYPE                    60       not defined
ASSIGNMENT                     5       not defined
BEGIN END                     12       not defined
CASE                          86       not defined
CREATE MODULE                 51       not defined
CREATE ORDERING              114       not defined
CREATE TRANSFORM             117       not defined
DECLARE VARIABLE              96       not defined
DROP MODULE                   28       not defined
FOR                           46       not defined
FREE LOCATOR                  98       not defined
HANDLER                       87       not defined
HOLD LOCATOR                  99       not defined
IF                            88       not defined
LEAVE                         89       not defined
LOOP                          90       not defined
RESIGNAL                      91       not defined
SET TRANSFORM GROUP          118       not defined
SIGNAL                        92       not defined
TEMPORARY TABLE               93       not defined
WHILE                         97       not defined

List of Additional SQL Statements and Codes in SQL3 and CLI

DYNAMIC_FUNCTION             DYNAMIC_FUNCTION_CODE
(string)                     (number)  (sqlcli.h definition)
CALL                           7       SQL_DIAG_CALL
CREATE ROLE                   61       SQL_DIAG_CREATE_ROLE
CREATE ROUTINE                14       SQL_DIAG_CREATE_ROUTINE
CREATE TRIGGER                80       SQL_DIAG_CREATE_TRIGGER
CREATE TYPE                   83       SQL_DIAG_CREATE_TYPE
DROP ROLE                     29       SQL_DIAG_DROP_ROLE
DROP ROUTINE                  30       SQL_DIAG_DROP_ROUTINE
DROP TRANSFORM               116       SQL_DIAG_DROP_TRANSFORM
DROP TRIGGER                  34       SQL_DIAG_DROP_TRIGGER
DROP ORDERING                115       SQL_DIAG_DROP_ORDERING
DROP TYPE                     35       SQL_DIAG_DROP_TYPE
GRANT ROLE                    49       SQL_DIAG_GRANT_ROLE
RELEASE SAVEPOINT             57       SQL_DIAG_RELEASE_SAVEPOINT
RETURN                        58       SQL_DIAG_RETURN
REVOKE ROLE                   60       SQL_DIAG_REVOKE_ROLE
SAVEPOINT                     63       SQL_DIAG_SAVEPOINT
SET ROLE                      73       SQL_DIAG_SET_ROLE
SET SESSION CHARACTERISTICS  109       SQL_DIAG_SET_SESSION_CHARACTERISTICS
START TRANSACTION            111       SQL_DIAG_START_TRANSACTION

(Note: In some contexts, the names DYNAMIC_FUNCTION and DYNAMIC_FUNCTION_CODE are COMMAND_FUNCTION and COMMAND_FUNCTION_CODE, respectively.)

Diagnostics Fields – Status Records

The 28 “status records” in a diagnostics area can occur multiple times. You must pass a record number between 1 and SQL_DIAG_NUMBER (or you can pass any positive number and see whether SQLGetDiagField returns SQL_NO_DATA). Other terms for Status Record are: “Descriptor Record” (preferred by Microsoft) and Condition Information Item (preferred by the SQL Standard in non-CLI contexts). Strings are returned according to the rules of Character String Retrieval.

< 0

A status record with a number less than zero identifies an implementation-defined diagnostics field.

0004

SQLGetDiagField(
   ...,...,n,SQL_DIAG_SQLSTATE,charstring,sizeof(charstring),
   &charstring_size);

This field gives you a 5-character status code – remember to allow 6 characters because of the null terminator. SQLSTATE is the most important diagnostics field. You’ll find the complete list of SQLSTATE values, often called simply status codes, at the end of this chapter. Quite often, the SQLSTATE class determines whether the other diagnostics fields have meaningful values.

0005

SQLGetDiagField(...,...,n,SQL_DIAG_NATIVE,&integer,NULL,NULL);

This field gives you an integer which has an implementation-defined numeric code for the error type. If your DBMS has been around for a few years, this will be the same as the SQLCODE value. It was once standard for DBMSs to return SQLCODE, and sometimes (for instance with IBM’s DB2) the SQLCODE is more informative than the SQLSTATE value. But there is no standardized interpretation for the codes, except that values less than zero are “errors”, equal to zero is “success”, greater than zero are “warnings”, and specifically +100 is “warning-no data”.

0006

SQLGetDiagField(
   ...,...,n,SQL_DIAG_MESSAGE_TEXT,charstring,sizeof(charstring),
   &charstring_size);

This field gives you an error message – sometimes merely an explanation of the SQLSTATE meaning, but the better DBMSs have context-sensitive tips. Useful for displays. Often, due to an ODBC requirement, messages start with bracketed information about the server and driver.

0008

SQLGetDiagField(
   ...,...,n,SQL_DIAG_CLASS_ORIGIN,charstring,sizeof(charstring),
   &charstring_size);

This field gives you the naming authority responsible for the definition of the class (the first two letters of SQLSTATE). Example: ‘ISO 9075’ would mean that the condition is documented in ISO/IEC 9075:1992 and is therefore “standard”.

0009

SQLGetDiagField(
   ...,...,n,SQL_DIAG_SUBCLASS_ORIGIN,charstring,sizeof(charstring),
   &charstring_size);

This field gives you the naming authority responsible for the definition of the subclass (the last three letters of SQLSTATE). Example: ‘ODBC 3.0’ would mean that the condition is documented in Microsoft’s ODBC manual version 3.0 but is not in any ISO specification, and is therefore “not standard”.

0010

SQLGetDiagField(
   ...,...,n,SQL_DIAG_CONNECTION_NAME,charstring,sizeof(charstring),
   &charstring_size);

This field gives you the <Connection name>. With the CLI this field is of minor importance, because the primary identifier for an SQL-Connection is the hdbc.

0011

SQLGetDiagField(
   ...,...,n,SQL_DIAG_SERVER_NAME,charstring,sizeof(charstring),
   &charstring_size);

If the last SQL statement was a failed CONNECT, DISCONNECT or SET CONNECTION, this field gives you the server that the attempt failed with. Otherwise, you get the same information that you’d get by calling SQLGetInfo(...,SQL_DATA_SOURCE_NAME,...).

0014

SQLGetDiagField(...,...,n,SQL_DIAG_CONDITION_NUMBER,&integer,NULL,NULL);

This field gives you the number of the Status Record (the terms “condition number” and “status record number” are synonymous). This will be the same thing as the RecordNumber parameter, so you won’t find out anything new here.

0015

SQLGetDiagField(
   ...,...,n,SQL_DIAG_CONSTRAINT_CATALOG,charstring,sizeof(charstring),
   &charstring_size);

0016

SQLGetDiagField(
   ...,...,n,SQL_DIAG_CONSTRAINT_SCHEMA,charstring,sizeof(charstring),
   &charstring_size);

0017

SQLGetDiagField(
   ...,...,n,SQL_DIAG_CONSTRAINT_NAME,charstring,sizeof(charstring),
   &charstring_size);

If SQLSTATE is '23000' (integrity constraint violation) or '27000' (triggered data change violation) or '40002' (transaction rollback-integrity constraint violation), then fields 0015, 0016 and 0017 give you the Catalog, Schema and name of the violated Constraint.

0018

SQLGetDiagField(
   ...,...,n,SQL_DIAG_CATALOG_NAME,charstring,sizeof(charstring),
   &charstring_size);

0019

SQLGetDiagField(
   ...,...,n,SQL_DIAG_SCHEMA_NAME,charstring,sizeof(charstring),
   &charstring_size);

0020

SQLGetDiagField(
   ...,...,n,SQL_DIAG_TABLE_NAME,charstring,sizeof(charstring),
   &charstring_size);

0021

SQLGetDiagField(
   ...,...,n,SQL_DIAG_COLUMN_NAME,charstring,sizeof(charstring),
   &charstring_size);

Fields 0018, 0019, 0020 and 0021 give you the Catalog, Schema and Table identifiers, plus the Column identifier if applicable, for what “caused” the problem. If SQLSTATE = '23000' or '27000' or '40002', these fields will identify the Table that the violated Constraint is associated with (assuming there is one such Table). If SQLSTATE = '42000', this is the Object that couldn’t be found or that you lack Privileges on (the Standard contains some ambiguities here, but it seems that these fields may be blank for access violations). If SQLSTATE = '44000', this is the View that has a violated WITH CHECK OPTION. If SQLSTATE = '09000' or '40004', this is the Table with the Trigger that can’t be executed. If SQLSTATE is any other value, results are implementation-dependent.

0022

SQLGetDiagField(
   ...,...,n,SQL_DIAG_CURSOR_NAME,charstring,sizeof(charstring),
   &charstring_size);

If SQLSTATE = '01001' or '24000', this field gives you the identifier of a Cursor. If SQLSTATE is anything else, results are implementation-dependent.

0023

SQLGetDiagField(...,...,n,SQL_DIAG_MESSAGE_LENGTH,&integer,NULL,NULL);

This field gives you the character length of the implementation-defined message string. You can get the same information using SQL_DIAG_MESSAGE_TEXT. By the way, the C include file sqlcli.h says “#define SQL_MAXIMUM_MESSAGE_LENGTH 512” so the suggestion is that you allow 512 bytes for the message – but it’s only a suggestion. It might be interesting to compare SQL_MAXIMUM_MESSAGE_LENGTH with what SQLGetDiagField returns for SQL_DIAG_MESSAGE_LENGTH.

0024

SQLGetDiagField(...,...,n,SQL_DIAG_MESSAGE_OCTET_LENGTH,&integer,NULL,NULL);

This field gives you the octet length of the implementation-defined message string. This will be the same as the message length in characters if the Character set is 8-bit.

0025

SQLGetDiagField(
   ...,...,n,SQL_DIAG_CONDITION_NAME,&charstring,sizeof(charstring),
   &charstring_size);

This field gives you the name of an unhandled user-defined exception.

0026

SQLGetDiagField(
   ...,...,n,SQL_DIAG_PARAMETER_NAME,charstring,sizeof(charstring),
   &charstring_size);

This field gives you the name of a parameter – presumably the parameter which contained bad (input) data. Since named parameters are not a universal feature, most DBMSs will not return anything here.

0027

SQLGetDiagField(
   ...,...,n,SQL_DIAG_ROUTINE_CATALOG,charstring,sizeof(charstring),
   &charstring_size);

0028

SQLGetDiagField(
   ...,...,n,SQL_DIAG_ROUTINE_SCHEMA,charstring,sizeof(charstring),
   &charstring_size);

0029

SQLGetDiagField(
   ...,...,n,SQL_DIAG_ROUTINE_NAME,charstring,sizeof(charstring),
   &charstring_size);

0030

SQLGetDiagField(
   ...,...,n,SQL_DIAG_SPECIFIC_NAME,charstring,sizeof(charstring),
   &charstring_size);

If the SQLSTATE error class is ‘38’ (external routine exception) or ‘39’ (external routine invocation exception), fields 0027, 0028 and 0029 give you the full identifier of the routine that “caused” the error, while field 0030 gives you the routine’s specific name.

0031

SQLGetDiagField(
   ...,...,n,SQL_DIAG_TRIGGER_CATALOG,charstring,sizeof(charstring),
   &charstring_size);

0032

SQLGetDiagField(
   ...,...,n,SQL_DIAG_TRIGGER_SCHEMA,charstring,sizeof(charstring),
   &charstring_size);

0033

SQLGetDiagField(
   ...,...,n,SQL_DIAG_TRIGGER_NAME,charstring,sizeof(charstring),
   &charstring_size);

If SQLSTATE='40004' or '09000', fields 0031, 0032 and 0033 give you the full identifier of the Trigger that “caused” the problem.

Algorithm:

If (HandleType <> SQL_HANDLE_STMT, SQL_HANDLE_ENV, SQL_HANDLE_DBC, or
SQL_HANDLE)DESC)
  return error: CLI-specific condition-invalid handle
If (handle isn't really the type indicated by HandleType)
  return error: CLI-specific condition-invalid handle
If (DiagIdentifier isn't a valid code)
  return error: HY024 CLI-specific condition-invalid attribute value
If (FieldIdentifier is for one of the Status Record fields)
  If (RecordNumber < 1)
    return error: 35000 invalid condition number -
  If (RecordNumber > actual number of status records)
    return warning: 01000 no data -
If (FieldIdentifier is for one of the Header fields)
  If (FieldIdentifier == SQL_DIAG_ROW_COUNT)
    If (last call was not SQLExecute or SQLExecDirect)
      return error: HY092 CLI-specific condition-invalid attribute identifier
      Otherwise: return a diagnostics-area field, as already described.

Notes:

• Status Records are sorted according to the severity of the error class:

  • Highest: Errors that cause rollback (class ‘40’).

  • Lower: Ordinary errors (everything except ‘40’ or ‘01’ or ‘02’).

  • Lower: No-data warning (class ‘02’).

  • Lowest: Mere warning (class ‘01’).

  The first Status Record is thus the most important. If the return code is SQL_ERROR you can be sure that the first Status Record describes an error condition.

• The SQLSTATEs associated with the possible SQLGetDiagField errors are only mentioned for documentary reasons. The SQLGetDiagField function does not itself post any diagnostics. The way you check for errors is: look at the return code, then start guessing. These tips may be useful:

  If (SQLGetDiagField returns SQL_SUCCESS_WITH_INFO)
    Probably the DiagInfo buffer is too small.
    Compare BufferLength (the maximum size of the DiagInfo buffer)
    to StringLength (the actual size of the string to be returned).
    If BufferLength is smaller, there's your problem.
  If (SQLGetDiagField returns SQL_INVALID_HANDLE)
    Sure, check that the handle is valid. But this problem can also occur
    if SQLHandleType is not SQL_HANDLE_..., so check that too.
  If (SQLGetDiagField returns SQL_ERROR)
    Check HandleType+DiagIdentifier. If the handle isn't a hstmt, then
    you can't ask for SQL_ROW_COUNT.
    Check RecordNumber. If you're looking for a header field, then it
    doesn't matter what you pass in RecordNumber. If you're looking for a
    status field, then RecordNumber must be >= 1.
    Check DiagIdentifier. If you use constants defined in sqlcli.h: the
    value here should be a constant beginning with SQL_DIAG_... -- but
    that's not enough. Also, make sure it's one of the values listed above.
    Check BufferLength. If you're looking for a numeric field, then it
    doesn't matter what you pass in BufferLength. If you're looking for a
    string field, then BufferLength must be >= 1.
  If (SQLGetDiagField returns SQL_NO_DATA)
    This always means that the value you passed in RecordNumber is greater
    than the value in the diagnostics area's NUMBER field.
    For example: you passed 1 but there are zero status records.
  

Some header fields always have valid information, even if the last call didn’t end with an error or warning. For example, you can find out what the last executed SQL statement was, and how many rows it affected, even if the number of Status Records is zero.

The great majority of diagnostics are only applicable to stmts. You will only need to get a dbc's diagnostics area fields if the last call used a dbc handle, which usually means if the last call was connect, disconnect, endtran or some variants of allochandle and freehandle. As for envs and descs, they too have diagnostics areas, but use of SQLGetDiagField with henvs and hdescs is esoteric.

Example:

  #include "sqlcli.h"
  ...
  SQLHSTMT     hstmt;
  SQLINTEGER   diag_number;         /* gets # of status records */
  SQLINTEGER   row_number;
  SQLCHAR      sqlstate[5+1];       /* gets SQLSTATE */
  SQLCHAR      catalog[128+1];      /* gets a catalog name */
  SQLSMALLINT  catalog_octet_length;/* size of catalog name */
  SQLCHAR      schema[128+1];       /* gets a schema name */
  SQLSMALLINT  schema_octet_length; /* size of schema name */
  SQLCHAR      name[128+1];         /* gets an object name */
  SQLSMALLINT  name_octet_length;   /* size of name */
  ...
  /* Make a one-Column Table, with a CHECK Constraint. */
  SQLExecDirect(hstmt,"CREATE TABLE Ts(col_1 INT,CHECK (col_1=7);",SQL_NTS);
  /* Try to violate the CHECK Constraint. */
  SQLExecDirect(hstmt,"INSERT INTO Ts VALUES(15);",SQL_NTS);
  /* Find out how many status records are in the diagnostics area. */
  SQLGetDiagField(SQL_HANDLE_STMT,NULL,SQL_DIAG_COUNT,&diag_count,NULL,NULL);
  /* Loop: For each status record ... */
  for (row_number=1; row_number<=diag_number; ++row_number) {
    /* Get SQLSTATE. */
    SQLGetDiagField(
      SQL_HANDLE_HSTMT,hstmt,row_number,SQL_DIAG_SQLSTATE,sizeof(sqlstate),
      sqlstate,NULL);
    /* The first two octets of SQLSTATE are the error class. */
    /* if class = '23' integrity constraint violation: what constraint? */
    if (memcmp(sqlstate,"23",2)==0) {
      /* Get Catalog . Schema . name of the Constraint */
      SQLGetDiagField(
        SQL_HANDLE_STMT,hstmt,row_number,SQL_DIAG_CONSTRAINT_CATALOG,catalog,
        sizeof(catalog),&catalog_size);
      SQLGetDiagField(
         SQL_HANDLE_STMT,hstmt,row_number,SQL_DIAG_CONSTRAINT_SCHEMA,
         schema,sizeof(schema),&schema_size);
      SQLGetDiagField(
         SQL_HANDLE_STMT,hstmt,row_number,SQL_DIAG_CONSTRAINT_NAME,
         name,sizeof(name),&name_size);
} }

ODBC: The SQLGetDiagField function is new in ODBC 3.0 (older ODBC versions had only SQLError for getting diagnostics fields). In addition to all the standard options, ODBC has an additional useful-looking one: SQL_DIAG_CURSOR_ROW_COUNT, for getting the number of rows in an open Cursor. (ODBC also gives row and Column number within the result set.) ODBC, unlike standard SQL, sorts status records by row number.

SQLGetDiagRec

Function Prototype:

SQLRETURN  SQLGetDiagRec(
  SQLSMALLINT HandleType,              /* 16-bit input */
  SQLINTEGER Handle,                   /* 32-bit input */
  SQLSMALLINT RecordNumber,            /* 16-bit input */
  SQLCHAR *Sqlstate,                   /* CHAR* output */
  SQLINTEGER *NativeError,             /* 32-bit output */
  SQLCHAR *MessageText,                /* CHAR* output */
  SQLSMALLINT BufferLength,            /* 16-bit input */
  SQLSMALLINT *TextLength);            /* 16-bit output */

Job: Get SQLSTATE, sqlcode and error-message from one status record.

Algorithm:

If (HandleType <> SQL_HANDLE_ENV | SQL_HANDLE_DBC | SQL_HANDLE_STMT
  | SQL_HANDLE_DESC)
Or (what handle references isn't the type that HandleType indicates)
  return error: CLI-specific condition-invalid handle
If (RecordNumber < 1)
  return error: 35000 invalid condition number -
If (RecordNumber > number of status records in diagnostics area)
  /* SQLGetDiagRec returns +100, but doesn't make its own diagnostics */
  /* In ODBC, some output parameters would be changed anyway. */
  return error: no data -
If (SqlState is not a null pointer)
  Set *SQLState = status record [RecordNumber] . SQL_DIAG_SQLSTATE
If (NativeError is not a null pointer)
  Set *NativeError = status record [RecordNumber] . SQL_DIAG_NATIVE_ERROR
If (MessageText is not a null pointer)
  /* ... The message text is copied in the usual Character String
     Retrieval way. */
  Set *MessageText = status record [RecordNumber ]. SQL_DIAG_MESSAGE_TEXT

For description of the SQL_DIAG_SQLSTATE, SQL_DIAG_NATIVE_ERROR, and SQL_DIAG_MESSAGE_TEXT fields, see the SQLGetDiagField descriptions.

Notes:

• The assumption behind SQLGetDiagRec is that, when you want diagnostics information, you specifically want SQL_DIAG_SQLSTATE, SQL_NATIVE_ERROR and SQL_DIAG_MESSAGE_TEXT (both contents and length). If the assumption is wrong and you want only some of these fields, or you want other fields, then you might find that SQLGetDiagField is all you need. We observed similar assumptions at work when we looked at the desc functions, SQLGetDescField and SQLGetDescRec.

• Calls to SQLGetDiagRec are frequent after a CLI function returns an error. That is:

  if (SQLfunction(...) < 0)  SQLGetDiagRec(...);
  

  is the normal way of calling.

Example: This example shows that SQLGetDiagRec and SQLGetDiagField may be similar. The first call retrieves dbc's SQLSTATE using SQLGetDiagField – we pass NULL for the final 4 parameters because we don’t care about them.

#include "sqlcli.h"
SQLCHAR sqlstate[6];
...
  SQLGetDiagField(SQL_HANDLE_DBC,hdbc,1,sqlstate,sizeof(sqlstate),NULL);
  SQLGetDiagRec(SQL_HANDLE_DBC,hdbc,1,sqlstate,NULL,NULL,NULL,NULL);

This example shows the minimalist error-handling procedure for applications that are written in a hurry: if anything goes wrong, print a message and stop the program. The symbol SQL... means “any CLI function”. The "if (sqlreturn < 0)" test uses an assumption (true at the moment) that SQL_SUCCESS and SQL_SUCCESS_WITH_INFO and SQL_NO_DATA – the non-problems – are all greater than or equal to zero; SQL_INVALID_HANDLE and SQL_NEED_DATA and SQL_ERROR – the problems – are less than zero.

#include "sqlcli.h"
SQLCHAR sqlstate[5+1];
SQLCHAR sqlmessage[SQL_MAX_MESSAGE_LENGTH+1];
SQLRETURN sqlreturn;
...
sqlreturn = SQLFunc(...);
if (sqlreturn < 0) {
  printf("Error: \n");
  if (sqlreturn==SQL_INVALID_HANDLE) {
    /* For the SQL_INVALID_HANDLE return code, there are no associated
       status records. So we have to make up and display our own error. */
    printf("Invalid handle.\n"); }
  if (sqlreturn==SQL_NEED_DATA) {
    /* This is shown for completeness; "need data" needs discussion later */
    printf("Need data.\n"); }
  if (sqlreturn==SQL_ERROR) {
    if (SQLGetDiagRec(...,...,1,sqlstate,NULL,sqlmessage,NULL,NULL)
     == SQL_NO_DATA) {
      /* Read the SQLAllocEnv description for special notes about handling
         errors from that function. For all other CLI functions, there
         will be at least one status record, so you won't get here. */
      printf("(No status rows).\n"); }
    else {
      printf("SQLSTATE=%s.\n",sqlstate);
      printf("MESSAGE_TEXT=%s.\n",sqlmessage);
  exit(1); }

This example displays warning or error messages after an execution.

#include "sqlcli.h"
SQLCHAR  sqlstate[6], sqlmessage[SQL_MAX_MESSAGE_LENGTH+1];
SQLINTEGER sqlnative, sqlmore;
SQLSMALLINT sqlrecordnumber, sqlmessagelength;
SQLHSTMT hstmt;
SQLRETURN sqlreturn1, sqlreturn2;
...
sqlreturn1 = SQLExecDirect(hstmt,"SQL statement goes here",SQL_NTS);
if (sqlreturn1 == SQL_ERROR || sqlreturn1 == SQL_SUCCESS_WITH_INFO) {
  for (sqlrecordnumber=1;;++sqlrecordnumber) {
    sqlreturn2=SQLGetDiagRec(
                  SQL_HANDLE_STMT,hstmt,sqlrecordnumber,sqlstate,&sqlnative,
                  sql_message,sizeof(sql_message),&sql_message_length);
  if (sqlreturn2 == SQL_NO_DATA || sqlreturn2 < 0) break;
  printf("SQLExecDirect returned: %d\n",sqlreturn1);
  printf("Status code = %s\n",sqlstate);
  printf("Native code or sqlcode = %ld\n",sqlnative);
  printf("Error/Warning message = %s.\n",sqlmessage);
  if (sqlmessagelength>sizeof(sqlmessage) printf("May be truncated."); }
SQLGetDiagField(SQL_HANDLE_STMT,hstmt,1,SQL_DIAG_MORE,&sqlmore,NULL,NULL);
if (sqlmore) {
  printf("Not all Error/Warning conditions have been displayed!\n"); }

ODBC: The SQLGetDiagRec function is new in ODBC 3.0; applications for earlier ODBC versions use SQLError, which is similar. ODBC example programs often use the names SqlState, Msg and rc where we have tended to use SQLSTATE, sqlmessage and sqlreturn.

SQLError

Function Prototype:

SQLRETURN  SQLError(
  SQLHENV henv,                   /* 32-bit input */
  SQLHDBC hdbc,                   /* 32-bit input */
  SQLHSTMT hstmt,                 /* 32-bit input */
  SQLCHAR *Sqlstate,              /* pointer to char* output -- char[5+1] */
  SQLINTEGER *NativeError,        /* pointer to 32-bit output */
  SQLCHAR *MessageText,           /* pointer to char* output */
  SQLSMALLINT BufferLength,       /* 16-bit input */
  SQLSMALLINT *TextLength         /* pointer to 16-bit output */
  );

Job: Return “diagnostics” – that is, the completion conditions (warnings) and exception conditions (errors) that are associated with the env or dbc or stmt.

Although all standard DBMSs will support it, and although it is an official SQL3 function, SQLError is obsolete. The modern way to get diagnostics is with SQLGetDiagRec or SQLGetDiagField.

Algorithm:

/* The diagnostics come from a stmt or (if hstmt
is 0) from a dbc or (if hdbc is also 0) from an env.
SQLError does not get diagnostics from
all three resources at once, or from a desc. */
If (hstmt <> 0)
  Set Handle = hstmt
Else
  /* hstmt == 0 */
  If (hdbc <> 0)
    Set Handle = hdbc
  Else
    /* hstmt == 0 and hdbc == 0 */
    If (henv <> 0)
      Set Handle = henv
    Else
      /* hstmt == 0 and hdbc == 0 and == 0 */
      return error: CLI-specific condition-invalid handle
/* Now Handle == handle of stmt or dbc or env */
/* The diagnostics, if any, were created by the last CLI function
   that was called using Handle. */
For (each status record generated by the last CLI function)
  If (we have already called SQLError and gotten this status record)
    continue
  If (there are no more status records)
    return SQL_NO_DATA (+100)
    /* ... a DBMS that follows the ODBC requirements would set
    sqlstate = '00000', NativeError = +100, before returning
    SQL_NO_DATA. This is a signal to the application program that it
    should break out of a loop. */
  Else
    break
/* We are now looking at a status record which was generated by a
   previous function call using the (passed) Handle. The following
   is the same as if we called:
     SQLGetDiagRec (<handle type>, <Handle>, <# of status record>,
     Sqlstate,NativeError, MessageText, BufferLength, TextLength) */
Return status record's SQL_DIAG_SQLSTATE value to Sqlstate.
Return status record's SQL_DIAG_MESSAGE_TEXT value to MessageText
(the return happens in the usual way for Character String Retrieval).
Return status record's SQL_DIAG_NATIVE value, presumably SQLCODE, to
NativeError.

Notes: The last five parameters of SQLError are the same as the last five parameters of SQLGetDiagRec. The effective difference is that, with SQLGetDiagRec, you pass a RecordNumber parameter, while with SQLError you depend on the DBMS to keep an internal counter – SQLError will always retrieve the next status record.

Example:

#include "sqlcli.h"
...
SQLCHAR sqlstate[6];            /* not 5: 6!! Allow for \0 at the end! */
SQLINTEGER sqlnative;           /* this is a "long int" */
SQLHSTMT hstmt;
...
sqlreturn=SQLExecDirect(hstmt,"INSERT VALUES;",SQL_NTS);
/* The above is illegal SQL so the return code will be negative */
if (sqlreturn==SQL_ERROR) goto error_handler_for_stmt;
...
error_handler_for_stmt:
/* **TRAP: sometimes errors happen for dbc or env functions too, each
   type of handle needs a separate error-handling procedure. */
SQLError(0,0,hstmt,sqlstate,&sqlnative,NULL,NULL,NULL);
/* sqlstate is probably '42000' */
/* sqlnative is probably less than zero */
...

ODBC: The SQLError function has been around since ODBC 1.0. In ODBC 3.0, it is labelled “deprecated” and ODBC’s driver manager will map it to SQLGetDiagRec.

SQLRowCount

Function Prototype:

SQLRETURN SQLRowCount(
  SQLHSTMT hstmt,           /* 32-bit input -- statement handle */
  SQLINTEGER *RowCount      /* 32-bit output */
  );

Job: Find out how many rows were inserted or updated or deleted during the execution of the last SQLExecute or SQLExecDirect call.

Algorithm:

If (hstmt does not refer to an executed statement)
  return error: HY010 CLI-specific condition-function sequence error
Set *RowCount = stmt's diagnostics area's SQL_DIAG_ROW_COUNT value.

Notes:

• The row count is the number of rows affected when you call SQLExecute or SQLExecDirect, and the SQL statement you’re executing begins with INSERT or UPDATE or DELETE (the SQL-data change statements).

• Only directly affected rows matter. If you delete one primary key row, and there are 10 foreign key rows that are also deleted because the FOREIGN KEY Constraint definition includes ON DELETE CASCADE, then a total of 11 rows are deleted: 1 directly, 10 indirectly – so the SQLRowCount function returns 1.

• Only “searched UPDATE” and “searched DELETE” statements matter. The UPDATE ... WHERE CURRENT OF <Cursor> and DELETE ... WHERE CURRENT OF <Cursor> statements have no effect on row count.

• With some DBMSs, SQLRowCount will contain the number of rows returned by the last SELECT statement. That’s very useful if you want to display the results on the screen along with a Windows scrollbar. If your DBMS won’t give you that, there are other options: (a) use a SELECT COUNT(*) statement (may be unreliable in a multi-user environment, may fail if selection is of a grouped View), (b) call SQLGetDiagField with the SQL_DIAG_CURSOR_ROW_COUNT option (non-standard, works only with ODBC) or (c) call SQLFetchScroll until the return is SQL_NO_DATA.

• You can get the same result by calling:

  SQLGetDiagField(SQL_HANDLE_STMT,hstmt,SQL_DIAG_ROW_COUNT,&RowCount,NULL,NULL);
  

  but SQLGetDiagField only returns results for “the last function”, which means (for instance) that if you’ve fetched since you executed the UPDATE statement, SQLGetDiagField can’t tell you anything. SQLRowCount, which returns results for “the last SQLExecute or SQLExecDirect function”, is better.

• If the number of changed rows is zero, then SQLExecute and SQLExecDirect both return SQL_NO_DATA (not SQL_SUCCESS or SQL_SUCCESS_WITH_INFO).

Example:

#include "sqlcli.h"
SQLINTEGER row_count;
...
if (SQLExecDirect(hstmt,"INSERT INTO Table_1 VALUES (1);",SQL_NTS)>=0) {
  if (SQLRowCount(hstmt,&row_count)>=0) {
    /* The value of row_count is 1. */ } }

ODBC: The SQLRowCount function has been around since ODBC 1.0.

And that’s it for the diagnostic functions. Now let’s take a look at the Standard’s SQLSTATE codes.

SQLSTATE Codes

The SQL status parameter SQLSTATE is a 5-character string value, with 2 parts: the first 2 characters represent a class value, the following 3 characters represent a subclass value. SQLSTATE codes are limited to digits and simple Latin upper-case letters.

Class values that begin with 0, 1, 2, 3, 4, A, B, C, D, E, F, G or H are called standard-defined classes and identify status conditions defined in either the SQL Standard or some other international standard. Subclass values associated with standard-defined classes that also begin with one of those 13 characters are called standard-defined subclasses and also identify status conditions defined in either the SQL Standard or some other international standard, while subclass values associated with standard-defined classes that begin with 5, 6, 7, 8, 9, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y or Z are called implementation-defined subclasses and identify status conditions defined by DBMS vendors.

Class values that begin with 5, 6, 7, 8, 9, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y or Z are called implementation-defined classes and identify exception conditions defined by DBMS vendors. All subclass values except ‘000’ (no subclass) associated with implementation-defined classes are implementation-defined subclasses. An implementation-defined completion condition is identified by returning an implementation-defined subclass together with one of the SUCCESSFUL COMPLETION, WARNING or NO DATA classes.

If a subclass value is not specified for a condition, then either subclass ‘000’ or an implementation-defined subclass is returned.

If multiple conditions are returned, then your DBMS decides which condition is the one that will be returned in the SQLSTATE parameter. (Any number of condition values, in addition to SQLSTATE, may be returned in the diagnostics area.)

Using the CLI, you can retrieve SQLSTATE with one of the diagnostics functions (SQLGetDiagField, SQLGetDiagRec or SQLError). Using embedded SQL, you can retrieve SQLSTATE with GET DIAGNOSTICS, or you can let the DBMS fill it in automatically. Since the status codes are reasonably standard, application programmers can anticipate what SQLSTATE values may crop up, and they can write appropriate error-testing or descriptive routines. The rest of this chapter contains detailed descriptions for all SQLSTATEs which are defined in the SQL Standard, as well as summary information about some SQLSTATEs which are used in common application environments, such as ODBC.

We suggest you examine the list of SQLSTATE codes and decide which warnings and errors you must take specific action for. Make a case statement which will be executed after each SQL operation. If you are programming for ODBC 3.x, don’t worry about the obsolete ODBC 2.0 entries in the list – Microsoft’s driver manager will translate them to the current standardized value. If you use the list in conjunction with some vendor manual, you will probably notice that our descriptions are more specific and detailed; however, you should prefer the vendor’s description in case of outright contradiction. For casual users, the DBMS’s in-context error-message displays, accompanied by a pointer to the offending SQL statement, will suffice. But that is an “after-the-fact” proposition. If your job is to program for errors that haven’t happened yet, you need a complete list with some examples and explanations for every entry.

Here is an example of a tiny C program (runnable in a DOS box) with one embedded SQL statement, followed by a case statement that checks for an error:

EXEC SQL INCLUDE SQLCA; /* so error message defined here? */
EXEC SQL BEGIN DECLARE SECTION;
char sqlcode[6];        /* Notice we allow 6 characters, there's a \0 too */
EXEC SQL END DECLARE SECTION;
void main ()
{
  EXEC SQL DROP TABLE x RESTRICT;
  portability(sqlstate);            /* Perhaps change the class */
  switch (sqlstate) {
    case '.....':                   /* Okay */
    case '.....':                   /* Table not found */
    case '.....':                   /* Another table depends */
    default:                        /* All other errors */
    } }
/* In a portable program you won't know what subclasses were added by each
   vendor, so change all implementation-defined subclasses to '000'. NIST uses
   a similar technique for its compliance-test programs. */
void portability (char sqlstate[])
{
  if (sqlstate[2]>='I' || (sqlstate[2]>='4' && sqlstate[0]<='9') {
    /* The third character of sqlstate, which is the first byte of subclass,
       is a letter >= 'I' or is a digit >= '4' -- implementation-defined. */
    sqlstate[2]=sqlstate[3]=sqlstate[4]='0'; } }/* subclass = '000' */

SUCCESSFUL COMPLETION SQLSTATEs

The following SQLSTATE codes identify a successful completion condition.

00000  successful completion

The “successful completion” class identifies “completion conditions” (as opposed to “exception conditions”, which are errors). In the case of SQLSTATE 00000, the last SQL operation sailed through without a problem. The 00000 status code is invisible to CLI programs because the Standard says: if the return code = SQL_SUCCESS, then no status records are generated.

WARNING SQLSTATEs

The following SQLSTATE codes identify a successful completion condition with a warning.

01000 warning

The “warning” class identifies completion conditions that were processed with some type of warning. They are usually associated with some DBMS-specific informational message, which you can retrieve with SQLGetDiagRec or GET DIAGNOSTICS. In the CLI, the existence of a warning diagnostic is signalled by the SQLRETURN value SQL_SUCCESS_WITH_INFO (1). SQLSTATE 01000 is the miscellaneous-warning category. For completion conditions that fit in the warning class, but don’t fit in one of the subclasses listed below (such as 01001), this is what you’ll get. Suggestion for this SQLSTATE: get the message with SQLGetDiagRec, display the message and continue.

01001 warning-cursor operation conflict

This SQLSTATE was included in the SQL-92 Standard by mistake; the corrigendum bulletin #3 says it should only be an SQL3 SQLSTATE. The NIST tests expect DBMSs to return SQLSTATE=01001 if you DELETE with and without a Cursor in the same transaction.

01002 warning-disconnect error

There was an error during execution of the CLI function SQLDisconnect, but you won’t be able to see the details because the SQLDisconnect succeeded.

01003 warning-null value eliminated in set function

The set function had to ignore a NULL when working on its argument. For example, the SUM of 5 and NULL is 5 (not NULL), but 01003 warns you that the result may be inaccurate because NULL usually means “unknown”.

01004 warning-string data, right truncation

Happens when you try to squeeze a 5-character (or bit) value into a 4-character (or bit) space (remember that “string” can be either character or bit string). The truncation should happen for data outbound from the database to a host variable, for example in the statement "SELECT ... INTO :x". It should not happen for data inbound to the database – that would be not a warning but an error (22001).

01005 warning-insufficient data item descriptor areas

Every descriptor area has multiple IDAs. You need one IDA per Column of a result set, or one per parameter. Either reduce the number of Columns in the select list or reduce the number of ?s in the SQL statement as a whole.

01006 warning-privilege not revoked

There is no Privilege descriptor for a combination of: this grantor, this grantee, this action. The DBMS does not return an error if a Privilege revocation fails – instead, it revokes whatever Privileges it can and returns this warning. If an equivalent Privilege was granted by a different grantor, it continues to exist, but warning 01006 does not appear.

01007 warning-privilege not granted

Probably the grantor doesn’t hold a Privilege WITH GRANT OPTION. For example: Susan has the UPDATE Privilege on TABLE_1 (without grant option) and the INSERT Privilege on TABLE_1 (WITH GRANT OPTION). She says: "GRANT SELECT, INSERT, UPDATE ON Table_1 TO Joe;". Result: Joe gets the INSERT Privilege, but not the SELECT or UPDATE Privileges, hence this warning (some DBMSs will generate the warning twice because two Privileges are ungranted). Warning 01007 also appears if the grantor has zero Privileges WITH GRANT OPTION, and says "GRANT ALL PRIVILEGES ...". On the other hand, if the grantor holds zero Privileges period, the result is error 42000 instead of warning 01007.

01008  warning-implicit zero-bit padding

Suppose you insert B'1' – a one-bit binary <literal> – into a two-bit Column. The second bit will be a 0, and the DBMS will return this warning.

01009  warning-search condition too long for information schema

Suppose you say “CREATE TABLE ... CHECK (<condition>)”, and the length of <condition> is larger than what can be stored in the INFORMATION_SCHEMA View, CHECK_CONSTRAINTS, in its CHECK_CLAUSE Column. The Table will still be created – this warning only means you won’t be able to see the entire information about the Table when you look at INFORMATION_SCHEMA. See also: 0100A and 0100B.

0100A warning-query expression too long for information schema

This is the same as warning 01009 except that instead of a search condition (as in a CHECK clause), you’re using a query condition (usually SELECT). Thus, if you say "CREATE VIEW ..." with a very long query, the size of Column VIEW_DEFINITION in View VIEWS in INFORMATION_SCHEMA is a limiting factor.

0100B  warning-default value too long for information schema

This is the same as warning 01009 except that instead of a search condition (as in a CHECK clause), you’re using a default value.

0100C  warning-dynamic result sets returned

0100D  warning-additional result sets returned

0100E  warning-attempt to return too many result sets

0100F  warning-fewer locators than dynamic result sets

0102F  warning-array data, right truncation

01Hxx  warning-external routine warning

The author of the external routine chooses the subclass value of xx.

01S00  warning-invalid connection string attribute (ODBC 2+3)

The ODBC function SQLBrowseConnect or SQLDriverConnect requires a parameter string with a certain format. Connection happens anyway.

01S01  warning-error in row (ODBC 3)

With ODBC 3.x, this warning happens only for SQLExtendedFetch or for SQLFetchScroll. Although an “error” has happened, this is only a warning because other rows might have been returned without error.

01S02  warning-option value changed (ODBC 3)

You used an ODBC function to change an option (e.g.: SQLSetEnvAttr). This warns you that the change occurred.

01S06  warning-attempt to fetch before the result set returned the first rowset (ODBC 3)

It would be clearer to use this wording: “attempt to fetch before the first row in the result set”. This error would be returned if your last fetch was of the first row in the result set and now you attempt to fetch PRIOR.

01S07  warning-fractional truncation (ODBC 3)

You’ll get this error if, for example, you assigned the value 5.432 to a Column whose definition is DECIMAL(3,2) – that is, the scale of the target is 2 and the scale of the source is 3, so only 5.4 is stored. A problem with the non-fractional part would result in another SQLSTATE: 22003.

01S08  warning-error saving file DSN (ODBC 3)

A warning from SQLDriverConnect. The Connection succeeds, but the file indicated by the FILEDSN keyword was not saved.

01S09  warning-invalid keyword (ODBC 3)

A warning from SQLDriverConnect. The Connection succeeds, but the SAVEFILE keyword was ignored.

NO DATA SQLSTATEs

The following SQLSTATE codes identify a successful completion condition where no data has been found that matches the given criteria.

02000 no data

The “data not found” class identifies completion conditions that were processed without any data that matched the given criteria being found. If the status code is class 02, then the return code will be SQL_NO_DATA. Most programs do not look for status records if the return code is SQL_NO_DATA, but there is a slight chance that a warning exists. The DBMS does not make a status record for SQLSTATE 02000, but it might make status records for implementation-defined subclasses within class 02. SQLSTATE 02000 goes together with sqlcode = +100 and return code = SQL_NO_DATA. There are several scenarios which lead to SQLSTATE 02000:

• fetches – If the Cursor position is now past the last row, or before the first row in the result set – e.g.: due to a SQLFetchScroll(...,SQL_PRIOR,...) call when Cursor was on first row of the result set.

• updates – Zero rows were affected by an INSERT, UPDATE or DELETE statement.

• diagnostics – No status record corresponds to the RecordNumber parameter.

• desc functions – No item descriptor area corresponds to the RecordNumber parameter

• in general – Whenever you ask for data, and there is no data.

In the CLI, the DBMS does not generate status records for SQLSTATE=02000. The only way to check for “no data” is to look at the return code.

02001  no data-no additional result sets returned

It is possible for a CALL statement to produce multiple result sets, but in this case there are no more. 02001 is possible if the function is SQLMoreResults.

ERROR SQLSTATEs

The following SQLSTATE codes identify an exception condition: something is preventing an SQL statement from being successfully completed.

03000  SQL statement not yet complete

The “SQL statement not yet complete” class identifies exception conditions that relate to incomplete processing of SQL statements.

07000  dynamic SQL error

The “dynamic SQL error” class identifies exception conditions that relate to dynamic SQL processing errors.

07001  dynamic SQL error-using clause does not match dynamic parameters

You might encounter this error if you set the length of a descriptor, then EXECUTE ... USING <descriptor>. Often this exception results from consistency-check failure during SQLExecute: see SQLSTATE HY021. In ODBC, the name for this subclass is “wrong number of parameters”.

07002  dynamic SQL error-using clause does not match target specifications

Often this exception results from consistency-check failure during SQLExecute: see SQLSTATE HY021. Sometimes this exception results from an incorrect number of parameters – but see also: SQLSTATE 07008. In ODBC, the name for this subclass is “COUNT field incorrect”.

07003  dynamic SQL error-cursor specification cannot be executed

07004  dynamic SQL error-using clause required for dynamic parameters

You cannot simply EXECUTE an SQL statement which has dynamic parameters – you also need to use a USING clause. See also: SQLSTATE 07007.

07005  dynamic SQL error-prepared statement not a cursor-specification

This results from an attempt to use ODBC function SQLColAttribute or SQLDescribeCol for an SQL statement that returned no result set, or from using “DECLARE CURSOR” followed by a prepare or execute of an SQL statement that does not return a result set.

07006  dynamic SQL error-restricted data type attribute violation

You are using a parameter whose value does not match the <data type>; the DBMS cannot even try to CAST to the correct <data type> because the source and target are too different. For example, you have a host variable defined as SQLINTEGER, you have a Column containing a TIMESTAMP and you try to fetch that Column into the host variable. With CLI, this might mean that you forgot to re-bind the parameters when you prepared a new SQL statement.

07007  dynamic SQL error-using clause required for result fields

You cannot simply EXECUTE an SQL statement which has result fields – you also need to use a USING clause. See also: SQLSTATE 07004.

07008  dynamic SQL error-invalid descriptor count

Using the embedded SQL ALLOCATE DESCRIPTOR statement, you allocated a 5-item descriptor. Now you are trying to use the sixth item in that descriptor. See also: SQLSTATE 07009.

07009  dynamic SQL error-invalid descriptor index

You are using a CLI descriptor function (such as SQLBindCol or SQLBindParameter) and the Column number is less than 1 or greater than the maximum number of Columns. Or, you are using the embedded SQL ALLOCATE DESCRIPTOR statement with a size which is less than 1 or greater than an implementation-defined maximum. See also: SQLSTATE 07008.

07S01  dynamic SQL error-invalid use of default parameter

You used SQLBindParameter with SQL_DEFAULT_PARAMETER, but now it turns out that the parameter does not have a default value.

08000  connection exception

The “connection exception” class identifies exception conditions that relate to SQL-Connections.

08001  connection exception-SQL-client unable to establish SQL-connection

The client could not get in touch with the server – perhaps there is no such server or perhaps the network is busy.

08002  connection exception-connection name in use

The name of an SQL-Connection must be unique. With standard SQL, this would happen if you said “CONNECT ... AS 'X' ...” twice (only one X at a time, please).

08003  connection exception-connection does not exist

You are trying to use a connection-related function (such as SQLGetConnectAttr) but the SQL-Connection is not open. Or, you said “DISCONNECT 'X'” and either X was never connected, or has already been disconnected. If the call is SQLAllocHandle(SQL_HANDLE_STMT,...), &hstmt is set to zero. You can get diagnostics from the hdbc.

08004  connection exception-SQL-server rejected establishment of SQL-connection

You’ll get this error if, for example, the SQLConnect function was unsuccessful. The server might not like the password, or it might already be handling the maximum number of clients.

08006  connection exception-connection failure

This occurs for a SET CONNECTION statement, where the argument is presumably a dormant Connection. The failure might be due to a server failure that occurred while the Connection was dormant. The SET CONNECTION might be implicit – for example, a DISCONNECT statement might result in an attempt to re-establish the last dormant Connection.

08007  connection exception-transaction resolution unknown

While you were trying to COMMIT, you were cut off. This is a bad one, because you are not told whether the transaction finished successfully or not. The ODBC manual calls this error “Connection failure during transaction” and implies that it can happen for ROLLBACK too.

08S01  connection exception-communication link failure (ODBC 2+3)

This can happen during execution of pretty well any ODBC function. Perhaps there was a hiccup on a phone line.

09000   triggered action exception

The “triggered action exception” class identifies exception conditions that relate to Triggers.

0A000  feature not supported

The “feature not supported” class identifies exception conditions that relate to features you’re trying to use, but that your DBMS hasn’t implemented. The Standard does not specify what will cause this SQLSTATE, possibly because the expectation is that all features will be supported. If the feature is ODBC-related, see also: SQLSTATE IM001, HYC00.

0A001  feature not supported-multiple server transactions

The meaning is “a single transaction cannot be performed on multiple servers”. Such a feature is sophisticated and rare.

0B000  invalid transaction initiation

The “invalid transaction initiation” class identifies exception conditions that relate to beginning a transaction.

0D000  invalid target type specification

The “invalid target type specification” class identifies exception conditions that relate to specifying a target for data.

0E000  invalid schema name list specification

The “invalid schema name list specification” class identifies exception conditions that relate to specifying Schema paths.

0F000  locator exception

The “locator exception” class identifies exception conditions that relate to locators: BLOB and CLOB <data type>s, and their values.

0F001  locator exception-invalid specification

This will be returned if a value passed for a BLOB or CLOB is invalid.

0F002  locator exception-update attempted with non-updatable locator

0F003  locator exception-location does not represent specified object

0F004  locator exception-unknown native value

0G000  reference to null table value

0H000  invalid SQLSTATE value

0K000  resignal when handler not active

0K002  resignal when handler not active-modifying SQL-data not permitted

0K003  resignal when handler not active-prohibited SQL-statement attempted

0K005  resignal when handler not active-function executed no return statement

0L000  invalid grantor

0N000  most specific type mismatch in invocation of type-preserving function

0P000  invalid role specification

0Q000  source result set not created by current SQL-server

0R000  cursor already allocated to result set or procedure

20000  case not found for case statement

21000  cardinality violation

Suggested error message: “subquery contained more than one row”. For example, suppose you have a Table T, with a Column S1 and two rows. In both rows, Column S1 has the value 5. Then either “SELECT (SELECT s1 FROM T) FROM ...” (scalar subquery) or “... WHERE 5 = (SELECT s1 FROM T)” (row subquery) violate cardinality. Another possibility, applicable to embedded SQL only, is that you are using a singleton-SELECT statement format but there are two rows returned. Some cardinality violations, e.g.: OVERLAPS operand with degree greater than 2, cause SQLSTATE=42000 instead of SQLSTATE=21000.

21S01  cardinality violation-insert value list does not match column list (ODBC 2+3)

For example: the statement “INSERT INTO T (a,b) VALUES (1,2,3)” is trying to insert three values into two Columns.

21S02  cardinality violation-degree of derived table does not match column list (ODBC 2+3)

For example: the SQL statement “CREATE VIEW (a,b) AS SELECT a,b,c FROM T;” is creating a 2-Column View for a 3-Column select.

22000  data exception

The “data exception” class identifies exception conditions that relate to data errors.

22001  data exception-string data, right truncation

Suppose you try to insert a 5-character string into a Column defined as CHAR(4), or suppose you use the expression “CAST (12345 AS CHAR(4))”. No truncation actually occurs since the SQL statement fails. See also: SQLSTATE 01004.

22002  data exception-null value, no indicator parameter

Suggested error message: “NULL seen, host program passed no indicator”. For example, you used SQLBindCol, but passed no parameter for an indicator value to be returned to. This is not an error unless you fetch a NULL.

22003  data exception-numeric value out of range

Suggested error message: “the numeric value <> is too big to fit in the target <>”. Often this is the result of an arithmetic overflow – for example, “UPDATE ... SET SMALLINT_COLUMN = 9999999999”, or you’re trying to retrieve a value of 5 billion into a host variable defined in Pascal as “Word”. Fractional truncation won’t cause this error, see SQLSTATE 01S07.

22004  data exception-null value not allowed

22005  data exception-error in assignment

For GET DESCRIPTOR and SET DESCRIPTOR statements, where the <data type> and size indicated in the descriptor does not match the value, this error appears.

22006  data exception-invalid interval format

For example, a year-month interval should contain only a year integer, a ‘-‘ separator, and a month integer. See also: SQLSTATE 22015.

22007  data exception-invalid datetime format

Suggested message: “For the <data type> <>, <> is not a valid value”. This error only occurs if there is an explicit or implicit CAST to a datetime (date or time or timestamp). See also: SQLSTATE 22008, 22018.

22008  data exception-datetime field overflow

Suggested message: “For the data type <>, <> is not a valid value”. One thing to look for: arithmetic which causes the DAY field of a date to be greater than the last day of the month – for example DATE '1994-03-31' + INTERVAL '01' MONTH. See also: SQLSTATE 22007.

22009  data exception-invalid time zone displacement value

Suggested message: “The time zone displacement value <> is outside the range -12:59 to 13:00”. This could happen for SET LOCAL TIME ZONE INTERVAL '22:00' HOUR TO MINUTE;, or for TIMESTAMP '1994-01-01 02:00:00+10:00'. (In the latter case, it is the result of the calculation that is a problem.)

2200A  data exception-null value in reference target

2200B  data exception-escape character conflict

2200C  data exception-invalid use of escape character

2200D  data exception-invalid escape octet

22010  data exception-invalid indicator parameter value

The value of the indicator variable is less than zero but is not equal to -1 (SQL_NULL_DATA).

22011  data exception-substring error

Suggested message: “The maximum length of SUBSTRING parameter is <>”. For example, “... SUBSTRING (string_column FROM 5 FOR 100) ...” when the length of STRING_COLUMN is only 1.

22012  data exception-division by zero

For example: “... column_name / ? ...”, where ? is a parameter marker, and the value of the parameter at run time is 0. If the Column contains NULL, then the result is NULL – the Standard makes it clear that dividing NULL by zero is not an error.

22014  data exception-invalid update value

22015  data exception-interval field overflow

Suggested message: “The <> field contains <>, the maximum is <>”. For example, “... DATE '1993-01-01' + INTERVAL '1000' YEAR ...” (this is a tricky one – the default size of all interval fields including year fields is only 2 digits). See also: SQLSTATE 22006.

22018  data exception-invalid character value for cast

Suggested message: “The character <> cannot be used when CAST``ing to data type <>". For example, if you try to cast ``'1994/10/10' to a date, it won’t work because the separator is ‘/’ (the correct separator is ‘-‘).

22019 data exception-invalid escape character

Suggested message: “The LIKE escape value <> is longer than 1 character”. The expression “... LIKE '...' ESCAPE 'AB' ...” would return this error.

2201B  data exception-invalid regular expression

2201C  data exception-null row not permitted in table

22020  data exception-invalid limit value

22021  data exception-character not in repertoire

Suggested message: “The character <> is not in the repertoire of Character set <>”. For example, the Character set SQL_CHARACTER does not contain the tilde (~), so this <literal> is not allowed: “... _SQL_CHARACTER '~' ...”.

22022  data exception-indicator overflow

Suggested message: “indicator is too small for size value <>”. This could happen if you use embedded SQL and you define the indicator as a C “short int” or Pascal “Word”. If you use ODBC, then the message won’t happen because all indicators must be 32-bit.

22023  data exception-invalid parameter value

22024  data exception-unterminated C string

Suggested message: “the C parameter string starting with <> is too long”. For example, an SQL statement uses a ? parameter, for a string, but at runtime it is seen that the C char-string does not have a terminating '\0'. The DBMS can only detect this error if it knows what the maximum string size is, i.e.: only in embedded SQL. Usually this problem will appear as a too-long or improperly-formatted string, so several other SQLSTATE error returns are possible – for example. SQLSTATE 22019.

22025  data exception-invalid escape sequence

Suggested message: “LIKE pattern <> has invalid escape sequence <>”. If you use an escape character, it must be followed in the pattern by _ or % or another escape character. If you use “... LIKE 'X%@' ESCAPE '@' ...”, you’ll get this error.

22026  data exception-string data, length mismatch

With ODBC this error should only occur for SQL_LONGVARCHAR or SQL_LONGVARBINARY strings. For standard SQL, this error should only occur for bit strings.

22027  data exception-trim error

Suggested message: “the TRIM string <> is longer than 1 character”. For example, “... TRIM('AB' FROM '...') ...” results in this error.

22028  data exception-row already exists

2202C  data exception-sublist error

2202D  data exception-null instance used in mutator function

2202E  data exception-array element error

2202F  data exception-array data, right truncation

23000  integrity constraint violation

The “integrity constraint violation” class identifies exception conditions that relate to Constraint violations. Suggested message for SQLSTATE 23000: “Attempted violation of constraint <>”. For example, Table T has a PRIMARY KEY Constraint and you attempt to insert two rows into T, both with precisely the same values in all Columns. For SQL-92, this SQLSTATE applies to attempted violations of any kind of Constraint, including NOT NULLs and FOREIGN KEY Constraints. The message can also occur if the total length of a foreign key Column list is exceeded. See also: SQLSTATE 40002.

23001  integrity constraint violation-restrict violation

24000  invalid cursor state

The “invalid cursor state” class identifies exception conditions that relate to Cursors. For SQLSTATE 24000, the Cursor-related operation can’t happen because some preliminary function hasn’t been called or hasn’t been completed – for example:

• OPEN <Cursor>, then immediately try to OPEN <Cursor> again.

• FETCH without opening the Cursor.

• OPEN <Cursor>, forget to FETCH, then DELETE ... WHERE CURRENT OF <Cursor>.

For CLI programs, the DBMS returns SQLSTATE=24000 if you try to FETCH and there is no result set (for example, because the previous SQL statement was INSERT). However, if there was no previous SQL statement at all, then the return is not 24000 but HY010 (CLI-specific error-function sequence error).

25000  invalid transaction state

The “invalid transaction state” class identifies exception conditions that relate to transactions. For SQLSTATE 25000, you are most likely trying to execute an SQL statement that can only be executed at transaction start – for example you are issuing a SET SESSION AUTHORIZATION statement after selecting something. Alternatively, you specified SET TRANSACTION READ ONLY and now you are saying UPDATE, DROP, etc. Finally, it is possible you are saying INSERT after a FETCH.

25001  invalid transaction state-active SQL-transaction

START TRANSACTION or DISCONNECT or SET SESSION AUTHORIZATION or SET ROLE statements cannot be issued if a transaction has already been started.

25002  invalid transaction state-branch transaction already active

SET TRANSACTION LOCAL ..., which applies only in multiple-server contexts, is illegal if a local transaction is already happening.

25003  invalid transaction state-inappropriate access mode for branch transaction

25004  invalid transaction state-inappropriate isolation level for branch transaction

25005  invalid transaction state-no active SQL-transaction for branch transaction

25006  invalid transaction state-read-only SQL-transaction

25007  invalid transaction state-schema and data statement mixing not supported

Some DBMSs do not allow SQL-Schema statements (such as CREATE) to be mixed with SQL-data statements (such as INSERT) in the same transaction.

25008  invalid transaction state-held cursor requires same isolation level

The SET TRANSACTION statement cannot be used to change isolation level if there is a held Cursor made with a different isolation level left over from the last transaction.

25S01  invalid transaction state-transaction state unknown (ODBC 3)

The attempt to end the transaction (with SQLEndTran) failed for at least one of the environment’s Connections.

25S02  invalid transaction state-transaction is still active (ODBC 3)

The attempt to end the transaction (with SQLEndTran) failed; the transaction did not end (that is, the transaction is not rolled back).

25S03  invalid transaction state-transaction is rolled back (ODBC 3)

The attempt to end the transaction (with SQLEndTran) failed; the transaction is rolled back (that is, the transaction ended).

26000  invalid SQL statement name

Probable cause: you failed to PREPARE an SQL statement and now you are trying to EXECUTE it.

27000   triggered data change violation

With SQL-92, you can cause this error with interlocked FOREIGN KEY Constraints that CASCADE ON UPDATE, so that when you UPDATE row#1 in TABLE#1, it causes an UPDATE to row#2 in TABLE#2, which in turn causes an UPDATE to row#1 in TABLE#1 – and that’s an error because the Standard doesn’t allow this kind of looping. With SQL3, this error can also happen for Triggers. See also: SQLSTATE 09000, 40004.

28000  invalid authorization specification

This error is caused by an invalid <AuthorizationID>. For example, “SET SESSION AUTHORIZATION 'PUBLIC'” is illegal because 'PUBLIC' has a special significance in SQL. It’s implementation-defined whether this can happen due to an entry of the wrong password.

2A000  direct SQL syntax error access or rule violation

This error does not appear in ordinary programs.

2B000  dependent privilege descriptors still exist

You used “REVOKE GRANT OPTION FOR”, but not CASCADE.

2C000  invalid character set name

Presumably an invalid <Character set name> would be one that begins with a digit, contains a non-Latin letter, etc.

2D000  invalid transaction termination

Has to do with savepoints and atomicity of transactions. Should not be a matter of concern until SQL3 gets going.

2E000  invalid connection name

For a CONNECT statement, the argument must be a valid <identifier>.

2F000  SQL routine exception

An SQL routine is a procedure or function which is written in SQL. SQLSTATE class 2F identifies exception conditions that relate to SQL routines. (Exceptions for non-SQL routines are class 38.)

2F002  SQL routine exception-modifying SQL-data not permitted

The probable cause of this error is that the CREATE PROCEDURE or CREATE FUNCTION statement contained the clause: CONTAINS SQL or READS SQL, but the function contains an SQL statement which can modify the database (for example, an UPDATE statement). The corresponding external-routine exception is 38002.

2F003  SQL routine exception-prohibited SQL-statement attempted

The prohibited procedural SQL statements are the SQL-transaction statements (START TRANSACTION, SET TRANSACTION, SET CONSTRAINTS, CREATE SAVEPOINT, RELEASE SAVEPOINT, COMMIT, ROLLBACK) or the SQL-Connection statements (CONNECT, SET CONNECTION, DISCONNECT) or the SQL-Schema statements (CREATE, DROP, ALTER, GRANT, REVOKE). The corresponding external-routine exception is 38003.

2F004  SQL routine exception-reading SQL-data not permitted

The probable cause of this error is that the CREATE PROCEDURE or CREATE FUNCTION statement contains the clause: CONTAINS SQL, but the function contains an SQL statement which reads the database (for example, a SELECT statement). The corresponding SQL-routine exception is 38004.

2F005  SQL routine exception-function executed no return statement

30000  invalid SQL statement

31000  invalid target specification value

33000  invalid SQL descriptor name

If, in embedded SQL, you use “EXECUTE ... USING DESCRIPTOR 'X';”, a descriptor named X must exist.

34000  invalid cursor name

If the function is SQLSetCursorName, then the problem is that a <Cursor name> must be a unique, valid <identifier>. If the function is SQLPrepare or SQLExecDirect, the SQL statement is “UPDATE ... WHERE CURRENT OF <Cursor>” or “DELETE ... WHERE CURRENT OF <Cursor>” and <Cursor> is not the name of an open Cursor.

35000  invalid condition number

With embedded SQL, you get this by saying “GET DIAGNOSTICS EXCEPTION 0”. With the CLI, you get this by calling SQLGetDiagRec or SQLGetDiagField with a RecordNumber parameter less than 1. If RecordNumber is greater than the number of status records, you don’t get this error. Instead, you get an SQL_NO_DATA return code.

36000  cursor sensitivity exception

The “cursor sensitivity exception” class identifies exception conditions that relate to Cursors and their sensitivity attribute.

36001  cursor sensitivity exception-request rejected

An attempt was made to open a sensitive Cursor, but the DBMS cannot guarantee that data changes will be visible throughout the transaction.

36002  cursor sensitivity exception-request failed

For example, an attempt was made to execute a positioned DELETE statement, but there is a sensitive Cursor open, and (for some implementation-dependent reason) the effects of the DELETE cannot be made visible via that Cursor.

37000  dynamic SQL syntax error or access rule violation

The SQL-92 Standard originally mentioned this SQLSTATE, but according to a later correction (corrigendum bulletin #3) we should use SQLSTATE = 42000 instead. That is what all ODBC 3.x drivers do.

38000  external routine exception

An external routine is a procedure or function which is written in a language other than SQL. SQLSTATE class 38 identifies exception conditions that relate to external routines. (Exceptions from SQL routines are class 2F.)

38001  external routine exception-containing SQL not permitted

The probable cause is that the CREATE PROCEDURE or CREATE FUNCTION statement contained the clause: NO SQL, but the routine contains an SQL statement.

38002  external routine exception-modifying SQL-data not permitted

The probable cause is that the CREATE PROCEDURE or CREATE FUNCTION statement contained the clause: NO SQL or CONTAINS SQL or READS SQL, but the function contains an SQL statement which can modify the database (for example, an UPDATE statement).

38003  external routine exception-prohibited SQL-statement attempted

The prohibited procedural SQL statements are the SQL-transaction statements (START TRANSACTION, SET TRANSACTION, SET CONSTRAINTS, CREATE SAVEPOINT, RELEASE SAVEPOINT, COMMIT, ROLLBACK) or the SQL-Connection statements (CONNECT, SET CONNECTION, DISCONNECT) or the SQL-Schema statements (CREATE, DROP, ALTER, GRANT, REVOKE).

38004  external routine exception-reading SQL-data not permitted

The probable cause is that the CREATE PROCEDURE or CREATE FUNCTION statement contained the clause: NO SQL or CONTAINS SQL, but the function contains an SQL statement which reads the database (for example, a SELECT statement).

39000  external routine invocation exception

39001  external routine invocation exception-invalid sqlstate returned

39004  external routine invocation exception-null value not allowed

3B000  savepoint exception

3B001  savepoint exception-invalid specification

3B002  savepoint exception-too many

3C000  ambiguous cursor name

A more appropriate wording is: duplicate <Cursor name>. In ODBC you can get this by calling SQLSetCursorName with an argument that is the name of an already-open Cursor.

3D000  invalid catalog name

Presumably a <Catalog name> could be invalid if it is used as a qualifier or as the argument of SET CATALOG, and does not refer to an existing Catalog or is not a valid <identifier>. However, all those situations are equally covered by SQLSTATE=42000 (syntax error or access violation). For SQLSetConnectAttr, the problem is with a SQL_ATTR_CURRENT_CATALOG specification.

3F000  invalid schema name

Presumably a <Schema name> could be invalid if it is used as a qualifier or as the argument of SET SCHEMA, and does not refer to an existing Schema or is not a valid <identifier>. However, all those situations are equally covered by SQLSTATE=42000 (syntax error or access violation).

3G000  invalid UDT instance

40000  transaction rollback

40001  transaction rollback-serialization failure

Two SQL jobs are running simultaneously, and a concurrency problem arose. For example, using a locking protocol, there was a deadlock or, using a timestamp protocol, a younger job has read the Object.

40002  transaction rollback-integrity constraint violation

This occurs for COMMIT, if there were deferred Constraints (deferred Constraints aren’t checked until COMMIT time unless SET CONSTRAINTS IMMEDIATE is executed). So: you asked for COMMIT, and what you got was ROLLBACK. See also: SQLSTATE 23000.

40003  transaction rollback-statement completion unknown

The SQL-Connection was lost during execution of an SQL statement.

40004  transaction rollback-triggered action exception

This occurs for COMMIT, if there was a deferred Constraint – presumably a FOREIGN KEY Constraint unless Triggers are supported by the DBMS – and there was an attempt to violate the Constraint. See also: SQLSTATE 09000, 27000.

42000  syntax error or access rule violation

The favourite exception. Syntax errors include not just grammar or spelling errors, but “bind problems” such as failure to find an Object. Access violations are due to lack of Privileges. A high security DBMS will try to hide from the user whether the problem is “you don’t have access to X” as opposed to “X isn’t there”; that’s why these two different categories are lumped together in one SQLSTATE (thus users can’t discover what the <Table name>s are by trying out all the possibilities).

Caution

It’s easy to think that a syntax violation will always be caught during the prepare stage. Not so. Many DBMSs don’t bind until the execution stage. You have to check after both SQLPrepare and SQLExecute, and perhaps even after SQLFetch (because a DBMS may evaluate expressions in the select list at FETCH time, or a Column might have been dropped since the Cursor was opened).

42S01  syntax error or access rule violation-base table or view already exists (ODBC 3)

This is caused by something like “CREATE TABLE T ...” when there’s already a Table named T.

42S02  syntax error or access rule violation-base table or view not found (ODBC 3)

This is caused by something like “SELECT * FROM T;” when there’s no Table named T.

42S11  syntax error or access rule violation-index already exists (ODBC 3)

This is caused by something like “CREATE INDEX I ON T(c);” when there’s already an index named I.

42S12  syntax error or access rule violation-index not found (ODBC 3)

This is caused by something like “DROP INDEX I;” when there’s no index named I.

42S21  syntax error or access rule violation-column already exists (ODBC 3)

This is caused by something like “ALTER TABLE T ADD COLUMN c ...” when Column C already exists.

42S22  syntax error or access rule violation-column not found (ODBC 3)

This is caused by something like “SELECT c FROM T;” when Table T has no Column named C.

44000  with check option violation

This is caused by something like “CREATE VIEW V AS SELECT x FROM T WHERE x=5 WITH CHECK OPTION;” then “UPDATE V SET x = 6;”. The View’s WITH CHECK OPTION clause is violated by the attempted UPDATE, which fails.

45000  unhandled user-defined exception

70100  operation aborted (ODBC 2)

Possible because tasks or threads can be destroyed in some operating systems, but don’t expect to see this.

H1zzz  SQL Multimedia part 1

H2zzz  SQL Multimedia part 2

H3zzz  SQL Multimedia part 3

H4zzz  SQL Multimedia part 4

H5zzz  SQL Multimedia part 5

H6zzz  SQL Multimedia part 6

H7zzz  SQL Multimedia part 7

H8zzz  SQL Multimedia part 8

H9zzz  SQL Multimedia part 9

HAzzz  SQL Multimedia part 10

HBzzz  SQL Multimedia part 11

HCzzz  SQL Multimedia part 12

HDzzz  SQL Multimedia part 13

HEzzz  SQL Multimedia part 14

HFzzz  SQL Multimedia part 15

HY000 CLI-specific condition-invalid handle

CLI-specific condition-dynamic parameter value needed

There is no status record for the invalid-handle exception. The return from the CLI function is -2 (SQL_INVALID_HANDLE), so the only test for invalid-handle is:

if (sqlreturn == SQL_INVALID_HANDLE) ...

The “invalid handle” exception occurs if (a) the passed hstmt or hdbc or henv or hdesc is not a handle of any resource at all or (b) the passed handle refers to the wrong type of resource, for example you passed a hdesc but a hdbc was expected in this context.

There is no status record for the need-data exception either. The return from the CLI function is +99 (SQL_NEED_DATA), so the only test for need-data is:

if (sqlreturn == SQL_NEED_DATA) ...

This exception is associated with deferred parameters.

HY001  CLI-specific condition-memory allocation error

Probable cause: a malloc failure. One possible solution is to close all other windows. If SQLAllocHandle(ENVIRONMENT HANDLE ...): the DBMS returns 0 to &henv. Since there is no valid handle, you can’t get diagnostics. If SQLAllocHandle(CONNECTION HANDLE ...): the DBMS returns 0 to &hdbc. You can get diagnostics using the henv.

HY002  CLI-specific condition-link-to-result-sets attribute precludes using this routine

HY003  CLI-specific condition-invalid data type in application descriptor

Actually, the invalid <data type> is not in an application descriptor, but in the parameter of a CLI function (SQLBindCol, SQLBindParameter, SQLGetData, SQLGetParamData).

HY004  CLI-specific condition-invalid data type

The SQLGetTypeInfo function requires a DataType parameter whose value is either SQL_ALL_TYPES or one of the “concise type” codes. If the DataType parameter has an invalid value, you get this error. SQLSetDescField and SQLBindParameter parameters must also contain “concise type” codes.

HY007  CLI-specific condition-associated statement is not prepared

A function (for example SQLGetDescField) requires a descriptor field, but the SQL statement has not been prepared so no description exists.

HY008  CLI-specific condition-operation cancelled

Many functions can operate asynchronously. Such operations can be cancelled using the SQLCancel function. That’s what happened to this one.

HY009  CLI-specific condition-invalid use of null pointer

One of the parameters for a function is a pointer (address). The passed pointer (address) is 0000:0000, which isn’t acceptable. Some DBMSs will return this error if they detect that the host language can’t handle pointers.

HY010  CLI-specific condition-function sequence error

Some functions won’t work unless some other function has successfully executed. For example, it’s impossible to “fetch” if you’ve never connected or selected. Or, an asynchronously executing function has not finished. Or, the last SQLExecDirect or SQLExecDirect call returned SQL_NEED_DATA (meaning the SQL statement has not finished executing), and you’re trying to free the stmt. See also: SQLSTATE 24000.

HY011  CLI-specific condition-attribute cannot be set now

Some settings cannot be changed in the middle of a transaction. For example, you can’t call the function SQLSetConnectAttr to change SQL_ATTR_TXN_ISOLATION, after you’ve already done some inserts.

HY012  CLI-specific condition-invalid transaction operation code

For the function SQLEndTran, the only possible arguments are SQL_COMMIT and SQL_ROLLBACK.

HY013  CLI-specific condition-memory management error

Many functions can return this. Usually the reason is “low memory conditions”.

HY014  CLI-specific condition-Limit on number of handles exceeded

This can come from a routine that allocates a handle (env, dbc, stmt, desc) such as SQLAllocHandle. The maximum number of handles is implementation-defined for each type.

• If SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv): the DBMS puts a handle in &henv despite the error; however, the handle is just a skeleton – you won’t be able to use it to allocate a connection handle with.

• If SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc): the DBMS puts 0 in &hdbc (SQL_NULL_HDBC). You can get diagnostics using the henv.

• If SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt): the DBMS puts 0 in &hstmt (SQL_NULL_HSTMT). You can get diagnostics using the hdbc.

• If SQLAllocHandle(SQL_HANDLE_DESC,hdbc,&hdesc): the DBMS puts 0 in &hdesc (SQL_NULL_HDESC). You can get diagnostics using the hdbc.

HY015  CLI-specific condition-no cursor name available

Obsolete – happens only for ODBC 2.x drivers, when you call SQLGetCursorName.

HY016  CLI-specific condition-cannot modify an implementation row descriptor

The target of a function (for example SQLCopyDesc) was an IRD, which can’t be changed.

HY017  CLI-specific condition-invalid use of an automatically allocated descriptor handle

An attempt was made to free or change a descriptor which was not made by the application program.

HY018  CLI-specific condition-Server declined cancel request

The ODBC function SQLCancel was called; it’s up to the data source to decide whether this can be processed. For example, the data source might not be responding to the driver manager’s requests.

HY019  CLI-specific condition-non-character and non-binary data sent in pie    ces

The function SQLPutData was called twice for the same parameter or Column; this is only allowed for character and binary data.

HY020  CLI-specific condition-attempt to concatenate a null value

The function SQLPutData was called twice for the same parameter or Column; in one of the calls, the passed value was NULL.

HY021  CLI-specific condition-Inconsistent descriptor information

The fields of a desc have failed the consistency check. For example, the SQL_DESC_SCALE value is greater than the SQL_DESC_PRECISION value (for a DECIMAL field) or the SQL_DESC_TYPE value is SQL_DATETIME but the SQL_DESC_INTERVAL_CODE value is 6 (only 1 to 5 would be legal). This error happens only for the function calls SQLSetDescField and SQLSetDescRec. For other functions, inconsistency causes SQLSTATE 07001 or 07002.

HY024  CLI-specific condition-invalid attribute value

One of the “attribute” functions was called (SQLGetEnvAttr, SQLSetEnvAttr, SQLGetConnectAttr, SQLSetConnectAttr, SQLGetStmtAttr, SQLSetStmtAttr). The numeric value for the attribute parameter for this function is not defined.

HY055  CLI-specific condition-non-string data cannot be used with string routine

HY090  CLI-specific condition-invalid string length or buffer length

You called any CLI function that passes a string value. The size parameter for this string value was less than or equal to zero, and was not equal to -3 (SQL_NTS).

HY091  CLI-specific condition-invalid descriptor field identifier

A descriptor is a structure with information about a selected Column (in a result set); a field in that descriptor is usually identified by a numeric code; the number you used was out of bounds. For example, you can’t call SQLColAttribute with a field identifier parameter = 0.

HY092  CLI-specific condition-invalid attribute/option identifier

You called any CLI function that passes an option number (for example SQLGetConnectAttr). The value you passed was not one of the defined values.

HY095  CLI-specific condition-Function type out of range (ODBC 3)

The SQLGetFunctions function was called, with a FunctionId parameter, the value of which is not defined for ODBC. This SQLSTATE is not mentioned in the SQL3 Standard.

HY103  CLI-specific condition-invalid retrieval code (ODBC 3)

The ODBC function SQLDataSources or SQLDrivers was called, with a Direction parameter, the value of which does not equal SQL_FETCH_FIRST, SQL_FETCH_NEXT, etc.

HY104  CLI-specific condition-invalid precision or scale value

The maximum precision or scale for some <data type>s is up to the data source (i.e. sometimes the driver can’t handle big things), so the SQLBindParameter function gets this return. Try querying the data source to find out what its maxima are.

HY105  CLI-specific condition-invalid parameter mode

The function call (e.g.: SQLBindParameter) contains an InputOutputMode parameter, the value of which is not one of: SQL_PARAM_MODE_IN, SQL_PARAM_MODE_OUT, SQL_PARAM_MODE_INOUT.

HY106  CLI-specific condition-invalid fetch orientation

Only certain fetch “orientations” are allowed (e.g.: NEXT, FIRST, LAST, PRIOR, ABSOLUTE, RELATIVE). The value you passed wasn’t one of them, for the function SQLFetchScroll. Or, if the Cursor isn’t a SCROLL Cursor, the only legal orientation is NEXT. ODBC variations: this could also happen for SQLExtendedFetch, and the name for this SQLSTATE is: Fetch type out of range.

HY107  CLI-specific condition-Row value out of range

One of the ODBC “fetch” or “set position” functions was called (e.g.: SQLFetch) and the Cursor is “key set driven”, but the row involved isn’t in the key set’s range.

HY109  CLI-specific condition-invalid cursor position

A row could not be fetched, probably because it has been deleted or because it is now locked.

HY110  CLI-specific condition-invalid driver completion (ODBC 3)

The ODBC function SQLDriverConnect contains a DriverCompletion parameter, the value of which is not defined for ODBC. This SQLSTATE is not mentioned in the SQL Standard.

HY111  CLI-specific condition-invalid bookmark value (ODBC 3)

The ODBC function SQLExtendedFetch or SQLFetchScroll was called, with a FetchOrientation parameter, the value of which was not defined for ODBC, or was a null pointer. This SQLSTATE is not mentioned in the SQL Standard.

HYC00  CLI-specific condition-optional feature not implemented (ODBC 3)

Many of the ODBC functions have optional features. It is unlikely that any driver will support every optional feature. When the driver doesn’t, this is the error you’ll get. See also: SQLSTATE 0A000 (which applies to unimplemented features outside the CLI) and SQLSTATE IM001 (which applies to unsupported ODBC functions rather than features). The ODBC manual refers to HYC00 as “particularly significant”.

HYT00  CLI-specific condition-timeout expired (ODBC 3)

You can specify a timeout value in milliseconds (with the SQLSetStmtAttr ODBC function call). If the timeout value that you set goes by and the statement is unfinished, this return happens.

HYT01  CLI-specific condition-connection timeout expired (ODBC 3)

This is similar to HYT00, but it applies to the connection rather than to the statement.

HZzzz  Remote Database Access

The Remote Database Access standard, ISO/IEC 9579-2, defines several subclass code values which may be passed on via SQL, but are not defined within SQL. These will all be in the class HZ.

IM001  driver does not support this function (ODBC 2+3)

There are a lot of ODBC functions. Some drivers don’t support them all, especially since ODBC 3.x is still new (and Microsoft keeps changing it). So this error comes from the driver. Compare SQLSTATE HYC00.

IM002  Data source name not found and no default driver specified (ODBC 2+3)

This happens when you’re connecting with ODBC, and there’s no DSN registered.

IM003  specified driver could not be loaded (ODBC 2+3)

Possibly the driver’s DLL is missing, or is not in the directory that the driver manager is searching.

IM004  driver's SQLAllocHandle on SQL_HANDLE_ENV failed (ODBC 2+3)

Usually this would indicate low memory, or that the maximum number of handles is exceeded, or a problem with function sequence.

IM005  driver's SQLAllocHandle on SQL_HANDLE_DBC failed (ODBC 2+3)

Usually this would indicate low memory, or that the maximum number of handles is exceeded, or a problem with function sequence.

IM006  driver's SQLSetConnectAttr failed (ODBC 2+3)

A connection error (during SQLBrowseConnect or SQLConnect or SQLDriverConnect).

IM007  no data source or driver specified; dialog prohibited (ODBC 2+3)

An error return from the ODBC function call SQLDriverConnect.

IM008  dialog failed (ODBC 2+3)

The SQLDriverConnect function puts up a dialog box; presumably the user did not end with “OK”.

IM009  unable to load translation DLL (ODBC 2+3)

A failure during connect or during SQLSetConnectAttr, probably due to a missing DLL, which may indicate an installation error.

IM010  data source name too long (ODBC 3)

The ODBC connection functions (SQLBrowseConnect or SQLConnect or SQLDriverConnect) have a maximum size for the name of the data source (DSN). You’ve exceeded it.

IM011  driver name too long (ODBC 3)

The SQLBrowseConnect and SQLDriverConnect functions have a maximum size for the name of the driver. You’ve exceeded it.

IM012  driver keyword syntax error (ODBC 3)

The SQLBrowseConnect or SQLDriverConnect functions require data in a fixed format.

IM013  trace file error (ODBC 3)

Couldn’t perform an operation on the trace file, perhaps a failure to write because of a disk-full situation.

IM014  invalid name of file DSN (ODBC 3)

The SQLDriverConnect function requires a valid identifier.

IM015  corrupt file data source (ODBC 3)

The SQLDriverConnect function can’t read the file.

OH000  invalid SQLSTATE value

OK000  resignal when handler not active

And that’s it for CLI diagnostics. In the next chapter, we’ll take a look at some general functions.