SQLCODE or SQLSTATE ???

Craig Theisen

SQLCODE or SQLSTATE ???
We are a fairly new 0S/390 DB2 rel 5.1 shop with a clean slate and
we are still establishing our applications standards and common routines.
Are any other newer IBM mainframe shops willing to share some info on what
they are using: ANSI- SQLSTATE or IBM's SQLCODE and why? I expect that
either to work equally well, but are there some subtle advantages of 1
over the other?

Thanks,

Craig T.



Hemant (CORP Kumar

Re: SQLCODE or SQLSTATE ???
(in response to Craig Theisen)
In my previous project which was a huge development endeavor, the
standards prescribed the use of SQLSTATE rather than SQLCODE. There were
some points mentioned in favour of SQLSTATE but can't remember all of them
right away. One of the things was for compatibility with ANSI SQL standard.

Hemant Kumar


-----Original Message-----
From: Theisen, Craig [mailto:[login to unmask email]
Sent: Monday, December 13, 1999 1:12 PM
To: [login to unmask email]
Subject: SQLCODE or SQLSTATE ???


We are a fairly new 0S/390 DB2 rel 5.1 shop with a clean slate and
we are still establishing our applications standards and common routines.
Are any other newer IBM mainframe shops willing to share some info on what
they are using: ANSI- SQLSTATE or IBM's SQLCODE and why? I expect that
either to work equally well, but are there some subtle advantages of 1
over the other?

Thanks,

Craig T.








Craig Mullins

Re: SQLCODE or SQLSTATE ???
(in response to Hemant (CORP Kumar)
SQLCODE contains the SQL return code, which indicates the success or failure
of the last SQL statement executed. SQLSTATE performs a similar function as
SQLCODE but is consistent across DB2 (and ANSI-compliant SQL) platforms.
Always code an IF statement in your programs immediately after every SQL
statement to check the value of the SQLCODE or SQLSTATE. In general, when
gearing your application programs to check for SQLCODE you can simply check
for negative values (that is, SQLCODE < 0). This takes into account that
positive SQLCODE values are warnings and SQLCODE = 0 means no rows found.
Of course, you can code a similar IF condition when using SQLSTATE, but you
need to take into account that SQLSTATE values that indicate warnings have a
'1' in the second position - that is, they are of the form '01xxx' and that
a SQLSTATE of '02000' means no rows found - so an equivalent IF statement
for SQLSTATE could be coded as SQLSTATE > '02000'.
SQLSTATE values consist of five characters: a two-character class code and a
three-character subclass code. The class code indicates the type of error,
and the subclass code details the explicit error within that error type.
Checking the SQLSTATE value can be easier when you must check for a group of
SQLCODEs associated with a single SQLSTATE, when you want to check for a
particular class of error using the two-character class code, or when your
program runs on multiple platforms.
For example, SQLSTATE '08001' is issued when three SQLCODEs are issued,
-808, -30080, -30082... all are associated with communication errors. If you
need to specifically check for this type of error it is easier to check for
SQLSTATE = '08001' instead of SQLCODE = -808 or SQLCODE = -30080 or SQLCODE
= -30082. Of course, this type of situation is not very common.
So, to summarize:
* favor SQLCODE if your programs are DB2 only and do not need to worry about
other SQL database access
* favor SQLSTATE if you are adamant that all programs adhere to ANSI
standards or you need to check groups of error conditions by SQLSTATE class
code
Of course, checking either will get the job done. Be sure you always check
one or the other after every SQL statement in your application programs. I
would expect that most mainframe shops are using SQLCODE in most of their
programs simply because it has been available longer.
Cheers,
Craig S. Mullins
Director, DB2 Technology Planning
BMC Software
[login to unmask email]

>>>>>>>>>>>>>>>>>>>>>ORIGINAL MESSAGE
FOLLOWS<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>From: Theisen, Craig [mailto:[login to unmask email]
>Sent: Monday, December 13, 1999 1:12 PM
>To: [login to unmask email]
>Subject: SQLCODE or SQLSTATE ???
>
>
> We are a fairly new 0S/390 DB2 rel 5.1 shop with a clean slate and
>we are still establishing our applications standards and common routines.
>Are any other newer IBM mainframe shops willing to share some info on what
>they are using: ANSI- SQLSTATE or IBM's SQLCODE and why? I expect
that
>either to work equally well, but are there some subtle advantages of 1
>over the other?
>
>Thanks,
>
>Craig T.
>
>