47.rst

Chapter 47 -- SQL/CLI: Diagnostic Functions

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
- Diagnostics Fields -- Header
  - SQL_DIAG_DYNAMIC_FUNCTION Codes
- Diagnostics Fields -- Status Records
SQLGetDiagRec
SQLError
SQLRowCount
SQLSTATE Codes

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.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Chapter 47 -- SQL/CLI: Diagnostic Functions

SQLGetDiagField

Diagnostics Fields -- Header

SQL_DIAG_DYNAMIC_FUNCTION Codes

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

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

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

List of Additional SQL Statements and Codes in SQL3 and CLI

Diagnostics Fields -- Status Records

SQLGetDiagRec

SQLError

SQLRowCount

SQLSTATE Codes

SUCCESSFUL COMPLETION SQLSTATEs

WARNING SQLSTATEs

NO DATA SQLSTATEs

ERROR SQLSTATEs

FilesExpand file tree

47.rst

Latest commit

History

47.rst

File metadata and controls

Chapter 47 -- SQL/CLI: Diagnostic Functions