Re: SQLSTATE 01004 now appears with v7.1 ?

For anyone who wants it, here is some example code for a checkSqlState()
procedure.

Note that this procedure was originally a subprocedure within a main
program, so it doesn't have any parameters passed in, but is instead using
the global SQLCA (shown at the top). If you decide to make it a procedure in
a separate *SRVPGM, then you'd want to pass SQLCA into it, obviously.

There's a bunch of code to check SQLCODE and SQLSTATE and log the details of
the error and then at the end, there's processing to return a value
depending on the type of error. You'd call it immediately following an SQL
operation as follows:

if checkSqlState() > 0;
exsr *pssr;
endif;

Some of the details aren't shown. The PSQnnnn message ID's are our own ones,
not related to any system ones.

PLEASE NOTE: I copied this from an older version and added bits from memory
that I couldn't find. So it probably doesn't work 'as-is', but should be
treated as just some example code...

D SQLCA DS Inz
D SQLCAID 8A
D SQLCABC 10I 0
D SQLCODE 10I 0
D SQLERRML 5I 0
D SQLERRMC 70A
D SQLERRP 8A
D SQLERRD 24A
D SQLER1 10I 0 Overlay(SQLERRD)
D SQLER2 10I 0 Overlay(SQLERRD:*Next)
D SQLER3 10I 0 Overlay(SQLERRD:*Next)
D SQLER4 10I 0 Overlay(SQLERRD:*Next)
D SQLER5 10I 0 Overlay(SQLERRD:*Next)
D SQLER6 4A Overlay(SQLERRD:*Next)
D SQLWARN 11A
D SQLSTATE 5A
* SQLSTATE Class code
D SQLCLCD 2A Overlay(SQLSTATE)
* SQLSTATE Subclass code
D SQLSCCD 3A Overlay(SQLSTATE:*Next)



*=============================================================================================
P checkSqlState B Export
D PI 10I 0
*---------------------------------------------------------------------------------------------
D ClassCodesStr S Like(ClassCodesArr)
D Inz('00 SUCCESS 0 0+
D 01 WARNING 0 1+
D 02 NO DATA 1 PSQ0402 2+
D 07 DYN ERR 1 PSQ0407 2+
D 08 CONN EXCP 1 PSQ0408 2+
D 09 TRG EXCP 1 PSQ0409 2+
D 0A NOTSUPP 1 PSQ040A 2+
D 0E INV SNL 1 PSQ040E 2+
D 0F INV TKN 1 PSQ040F 2+
D 0K RESIGNAL 1 PSQ0410 1+
D 0W TRG PRH 1 PSQ0411 2+
D 0Z DIAG EXCP 1 PSQ0412 2+
D 20 CASE NF 1 PSQ0420 2+
D 21 CARD VIOL 1 PSQ0421 2+
D 22 DATA EXCP 1 PSQ0422 2+
D 23 CONST VIOL 1 PSQ0423 2+
D 24 INV CSR ST 1 PSQ0424 3+
D 25 INV TRN ST 1 PSQ0425 3+
D 26 INV STM ID 1 PSQ0426 3+
D 27 TDC VIOL 1 PSQ0427 4+
D 28 INV AUTH 1 PSQ0428 3+
D 2D INV TRN TR 1 PSQ042D 3+
D 2E INV CONN 1 PSQ042E 3+
D 2F FUN EXCP 1 PSQ042F 5+
D 34 INV CSR NM 1 PSQ0434 3+
D 35 INV CND NB 1 PSQ0435 3+
D 36 CSR SENS 1 PSQ0436 3+
D 38 EXTFN EXCP 1 PSQ0438 3+
D 39 EXTFN CALL 1 PSQ0439 3+
D 3B SAVPT EXCP 1 PSQ043B 3+
D 3C AMB CSR NM 1 PSQ043C 3+
D 3F INV SCH NM 1 PSQ043F 4+
D 42 SYNTAX ERR 1 PSQ0442 6+
D 44 WCO VIOL 1 PSQ0444 3+
D 46 JAVA ERROR 1 PSQ0446 7+
D 51 INV APP ST 1 PSQ0451 3+
D 53 INV OPER 1 PSQ0453 3+
D 54 SQL LIMIT 1 PSQ0454 8+
D 55 BAD STATE 1 PSQ0455 8+
D 56 MISC ERR 1 PSQ0456 8+
D 57 RSC AVAIL 1 PSQ0457 8+
D 58 SYSTEM 1 PSQ0458 9')
*
D ClassCodesStr@ S * Inz(%addr(ClassCodesStr))
D ClassCodesArr DS Based(ClassCodesStr@) Qualified
D Array Dim(42)
D ClassCode 2A Overlay(Array:1)
D Desc 10A Overlay(Array:4)
D Audit N Overlay(Array:15)
D MsgId 7A Overlay(Array:17)
D RtnVal 1S 0 Overlay(Array:25)
*---------------------------------------------------------------------------------------------
/free

select;
// error condition
when SQLCODE < 0;
select;
// CPF message
when SQLER1 > 0;
msgid.prefix = 'CPF';
msgid.suffixn = SQLER1;
// CPD message
when SQLER2 > 0;
msgid.prefix = 'CPD';
msgid.suffixn = SQLER2;
// SQL message
other;
msgid.prefix = 'SQL';
msgid.suffixn = %abs( SQLCODE );
endsl;
// successful with warnings
when SQLCODE > 0;
msgid.prefix = 'SQL';
msgid.suffixn = SQLCODE;
// successful execution
other;
msgid.prefix = 'SQL';
msgid.suffix = SQLER6;
endsl;

// message text
if SQLERRML > 0;
msgdta = SQLERRMC;
endif;

if SQLCODE <> 0;
string = rtvMsg( msgid : msgdta );
log( string : AUDIT );
endif;

i = %lookup( SQLCLCD : ClassCodesArr.ClassCode );
if i > 0;
if ClassCodesArr.Audit(i);
if ClassCodesArr.MsgId(i) <> *blanks;
string = rtvMsg( ClassCodesArr.MsgId(i) : SQLSCCD );
log( string : AUDIT );
endif;
endif;
else;
i = 42; // Assume system error :(
endif;

return ClassCodesArr.RtnVal(i);

/end-free
P E


Rory