Aren’t I supposed to be wishing Happy New Year there? Not this month! One of my readers asked me a question about SQLCODEs and it opened up a quite fascinating can of worms!
What is the SQLCODE?
Anyone reading this blog should already know exactly what the SQLCODE is, however, for those of you out there using Google in 2045, the definition is, at least in COBOL:
01 SQLCA.
05 SQLCAID PIC X(8).
05 SQLCABC PIC S9(9) COMP-5.
05 SQLCODE PIC S9(9) COMP-5.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP-5.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES
PIC S9(9) COMP-5.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
05 SQLEXT.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARNA PIC X.
10 SQLSTATE PIC X(5).The third field within the SQLCA is the SQLCODE, which is a four-byte signed integer and it is filled after every SQL call that a program/transaction makes.
All Clear So Far?
So far so good! In the documentation from IBM is this paragraph about how to handle and process SQLCODEs:
SQLCODE
Db2 returns the following codes in SQLCODE:
• If SQLCODE = 0, execution was successful.
• If SQLCODE > 0, execution was successful with a warning.
• If SQLCODE < 0, execution was not successful.
SQLCODE 100 indicates that no data was found.
The meaning of SQLCODEs, other than 0 and 100, varies with the particular product implementing SQL.
Db2 Application Programming and SQL Guide
So, every programmer I have ever talked to checks if the SQLCODE is 0 – Green! Everything is fine, if the SQLCODE is negative – Bad message and ROLLBACK, if the SQLCODE is +100 – End of cursor or not found by direct select/update/delete – normally 100% Ok, everything else issues a warning and is naturally very dependent on the application and business logic!
What’s Wrong With This Picture?
Well, sometimes zero is not really zero… I kid you not, dear readers! The sharp-eyed amongst you, will have noticed the last bytes of the SQLCA contain the SQLSTATE as five characters. Going back to the documentation:
An advantage to using the SQLCODE field is that it can provide more specific information than the SQLSTATE. Many of the SQLCODEs have associated tokens in the SQLCA that indicate, for example, which object incurred an SQL error. However, an SQL standard application uses only SQLSTATE.
So, it still seems fine, but then it turns out that SQLCODE 0 can have non-all zero SQLSTATEs!
New in the Documentation
At least for a few people this is a bit of a shock. From the SQLCODE 000 documentation:
SQLSTATE
- 00000 for unqualified successful execution.
- 01003, 01004, 01503, 01504, 01505, 01506, 01507, 01517, or 01524 for successful execution with warning.
Say What?
Yep, this is simply stating that you have got an SQLCODE 0 but up to nine different SQLSTATEs are possible… This is not good! Most error handling is pretty bad, but now having to theoretically add SQLSTATE into the mix makes it even worse!
What are the Bad Guys Then?
01003 Null values were eliminated from the argument of an aggregate function.
01004 The value of a string was truncated when assigned to another string data type with a shorter length.
01503 The number of result columns is larger than the number of variables provided.
01504 The UPDATE or DELETE statement does not include a WHERE clause.
01505 The statement was not executed because it is unacceptable in this environment.
01506 An adjustment was made to a DATE or TIMESTAMP value to correct an invalid date resulting from an arithmetic operation.
01507 One or more non-zero digits were eliminated from the fractional part of a number used as the operand of a multiply or divide operation.
01517 A character that could not be converted was replaced with a substitute character.
01524 The result of an aggregate function does not include the null values that were caused by evaluating the arithmetic expression implied by the column of the view.
Not Good!
From this list the 01004, 01503, 01506 and especially 01517 just jump right out and scream at you! Here in Europe, we have a right to have our names or addresses correctly written and, in Germany with all the umlauts, it can get difficult if you then have a 01517 but SQLCODE 0 result!
I hope you don’t find this newsletter too unsettling as, after all, Db2 and SQL normally works fine, but I do think that these SQLSTATEs should really have warranted a positive SQLCODE when they were first created…
What do you all think?
TTFN,
Roy Boxwell
Update:
One of my readers wonders how practicle these “errors” are. A good point, and so here is a nice and easy recreate for the 01003 problem:
CREATE TABLE ROY1 (KEY1 CHAR(8) NOT NULL,
VALUE1 INTEGER ,
VALUE2 INTEGER )
;
INSERT INTO ROY1 (KEY1) VALUES ('A') ;
INSERT INTO ROY1 (KEY1) VALUES ('AA') ;
INSERT INTO ROY1 (KEY1,VALUE1) VALUES ('B', 1) ;
INSERT INTO ROY1 (KEY1,VALUE1,VALUE2) VALUES ('BB', 1 , 1) ;
INSERT INTO ROY1 (KEY1,VALUE1) VALUES ('C', 2) ;
INSERT INTO ROY1 (KEY1,VALUE1,VALUE2) VALUES ('C', 2 , 2) ;
SELECT * FROM ROY1
;
CREATE VIEW ROYVIEW1 AS
(SELECT AVG(VALUE1) AS AVGVAL1, AVG(VALUE2) AS AVGVAL2
FROM ROY1)
;
SELECT * FROM ROYVIEW1
; This set of SQL ends up with these outputs:
---------+---------+---------+--------
KEY1 VALUE1 VALUE2
---------+---------+---------+--------
A ----------- -----------
AA ----------- -----------
B 1 -----------
BB 1 1
C 2 -----------
KEY1 VALUE1 VALUE2
---------+---------+---------+--------
C 2 2
DSNE610I NUMBER OF ROWS DISPLAYED IS 6
---------+---------+---------+---------+---------+---------+-----
AVGVAL1 AVGVAL2
---------+---------+---------+---------+---------+---------+-----
1 1
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 01003 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,,W,,, SQL WARNINGS
DSNT417I SQLWARN6-A = ,,,, SQL WARNINGS
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
This is not so good if you ask me…