Re: SQL state

[Karen L Hodge/MIS/Genesys <KHodge@xxxxxxxxxxx>]

The SQL state is a five-character value with the following structure:
The first two characters are the class.
The last three characters are the subclass.

The meaning of the class values are as follows:
  00            -- Success
  01            -- Warning
  02            -- No data
03 through ZZâError
The V5R1 âSQL Messages and Codesâ manual provides a comprehensive list
of SQL state values. The manual is available in the V5 iSeries Info
Center at
http://publib.boulder.ibm.com/pubs/html/as400/v5r1/ic2924/info/rzala/rzalamst02.html
 .

D SqlStateOK      C                   Const( â00000â )
D SqlStateNoRow   C                   Const( â02000â )
D SqlStateDupKey  C                   Const( â23505â )
D SqlStateWrnPfx  C                   Const( â01â )


Here are three common patterns for testing the outcome of an SQL statement.
     For statements where the only outcome youâre interested in is
âsuccess,â the following simple test will suffice:

C                   If        SqlStt <> SqlStateOK
C                     ExSr    SqlError
C                   EndIf

Obviously, you can code your own actions in place of the exampleâs ExSr
statement.
     For Fetch statements within a loop to detect when no more rows are
available, as well as warnings and errors, use the following test:

C                   Select
C                     When    SqlStt = SqlStateOK
C                       ExSr  ProcessRow
C                     When    SqlStt = SqlStateNoRow
C                       Eval  MoreRows = False
C                     When    %Subst( SqlStt : 1 : 2 ) = SqlStateWrnPfx
C                       ExSr  SqlWarning
C                       Eval  MoreRows = False
C                     Other
C                       ExSr  SqlError
C                       Eval  MoreRows = False
C                   EndSl

In this pattern, the MoreRows variable can be used to control the Fetch
loop. (Note that False is just a mnemonic for â0â.) You should not use
SqlStt itself to control a Fetch loop, because SqlStt may be reset by other
SQL statements that may be within the scope of the loop.
     For statements such as Insert, where you want to handle specific
errors and just want to fall through for successful completion, use this
test:

C                   Select
C                     When    SqlStt = SqlStateOK
C*                      Skip
C                     When    SqlStt = SqlStateDupKey
C                       ExSr  SqlDupKey
C                     When    %Subst( SqlStt : 1 : 2 ) = SqlStateWrnPfx
C                       ExSr  SqlWarning
C                     Other
C                       ExSr  SqlError
C                   EndSl

Notice that the âSkipâ line is just a comment. You can add additional When
conditions to handle other SQL states.

Thank you,

Karen Hodge
Senior System Analyst
Genesys Health System
1000 Healthpark Blvd, Grand Blanc, Mi 48439
Office 810.606.5180, Fax 810.606.7204
khodge@xxxxxxxxxxx