SQLSTATE Messages

SQLSTATE codes return values that are errors, warnings, or "no data found" messages. The following table displays how SQLSTATE and SQLCODE messages correspond to each other.

SQLSTATE code

SQLCODE code

First digit greater than 0

Negative number

First digit equal to 1

1

First digit equal to 2

100


If a WHENEVER SQLERROR CALL myproc() statement is in effect, myproc() will be called in all cases where SQLSTATE returns a value with a nonzero first digit.

Use SQLSTATE to check for exceptions and warnings. To use SQLSTATE in your program, you must explicitly declare SQLSTATE within a host variable declaration block. For example:


EXEC SQL BEGIN DECLARE SECTION ;
char SQLSTATE [6] ;  // 5 characters for code and one character for
                     // null terminator
EXEC SQL END DECLARE SECTION;

Errors caught at compile time using the /SQLACCESS option will not be mapped to SQLSTATE values at runtime.

The following table lists SQL Server errors and the SQLSTATE codes they map to.

SQLSTATE code

SQL Server message or DB-Library error numbers

Description

01001

532,10095 (DB-Library)

Cursor update or delete failure due to optimistic concurrency check failure.

01003

8153

Elimination of null values by set operator (warning )

01004

n/a

Select or fetch into host variable that is too short.

02000

n/a

No data found. Equivalent to SQLCODE = 100. Occurs when:

Cursor fetch at end of result set

Cursor fetch on empty result set

Singleton select returns no data

Searched delete affects no rows*

Insert…Select affects no rows*

Searched update affects no rows*


The conditions designated with * are only detected when the program is compiled with the NOSQLACCESS option to NSQLPREP.

07002

n/a

Number of columns does not match number of host variables.

21000

512

Singleton select returns more than one row , or subquery preceded by = returns more than one value

22001

8152

String data truncated on right on insert or update

22002

n/a

Null value returned with no indicator variable provided.

22003

168,220,232,234,
236-238,244,
246,248,519-524,
535,8115,
10015
(DB-Library)

Arithmetic overflow error

22005

206,235,245,247,249,
256,257,305,409,518,
529

Datatype conversion error.

22008

210,211,241,242,295,
296,517

Datatype conversion error.

22012

8134

Divide by zero error.

22019

1010, 506

Invalid escape character

22022

n/a

Indicator variable overflow.

22024

n/a

Unterminated C string passed as input host variable

22025

310

Invalid escape sequence, e.g. escape char not followed by %, _, or escape char.

23000

233,272,273,515,530,
547,1505,1508,2601,
2615,2626,2627,3604,
3605

Constraint violation

24000

n/a

Invalid cursor state.

25000

266,277,611,628,3902,
3903,3906,3908,6401

Transaction state error.

40001

1205,1211,2625,
3309,7112

Transaction rollback due to deadlock.

42000

207,208,213,229,
230,260

Syntax error, permission violation, or other non-specific error.

44000

550

View with check option violation