Thank you for the suggestion, Tom.
I have written code for parsing the SQLSTATE value from the
SQLERM that is returned in the SQLCA. However I have
found that the data in SQLERM cannot be relied on.
Take the following example of an insert attempt which
resulted in a before trigger failing the insert:
This is what I see in the job log:
SQL trigger PKP10P100_VALIDATE_REQUESTACTION_INSERT in PRJ2620 failed with
SQLCODE -438 SQLSTATE 75004.
However this is what I get in SQLERM while debuggin my
SQLSTATE parsing code:
PKP10P100_VALIDATE_REQUESTACTION_INSERT PRJ2620??Ú¢ 75004 Request
Has anyone had an experience like this?
I have pasted my parsing code below for good measure:
*---------------------------------------------------------------
* Name : parseSQLState
* Description : Extract the SQLState from the passed in sql
* error message.
* Argument(s) : None.
* Return value :
* Usage : void
*---------------------------------------------------------------
// BEGIN PROCEDURE
p parseSQLState...
p b export
// Procedure Interface
//////////////////////////////////
d parseSQLState...
d pi like(SQLSTATE)
d pr_sqlerm const like(SQLERM)
// Define Local Vars
//////////////////////////////////
d i s 10i 0
d j s 10i 0
d state s like(sqlstate)
d keyword c 'SQLSTATE '
d endchar c '.'
//Main
/free
//EXAMPLE pr_sqlerm value
// 'SQL trigger PKP10P100_VALIDATE_REQUESTACTION_INSERT in '+
// 'PRJ2620 failed with SQLCODE -438 SQLSTATE 75004.';
//Get the starting position of the keyword value
//Example: i = 90
i = %scan(keyword:pr_sqlerm);
//If we do not find the keyword we must exit
if i < 1;
return *blanks;
endif;
//Get the position of the next end character found
// after the keyword value
//Example: j = 104
j = %scan(endchar:pr_sqlerm:i);
//If the end character is not found then default to the
// end of the string
if j < 1;
j = %len(%trim(pr_sqlerm));
//If we still do not get a good index then we must exit
if j < 1;
return *blanks;
endif;
endif;
//Substring out the sqlstate by starting at the end
// of the keyword value and going to the position
// of the next end character found (j).
//Examples are from the inner most calculation out
// Start: (i + %len(keyword)) = (90 + 9) = 99
// Length: (j-(i + %len(keyword))) = (104 - 99) = 5
// Result: substring from position 99 -> 104 = '75013'
//Now just to ensure that the value substringed out
// will fit in the destination, substring it to the
// size of the destination field trimming anything
// that would not fit.
state = %subst(%subst(pr_sqlerm:(i + %len(keyword))
:(j-(i + %len(keyword)))):1:%size(state));
return state;
/end-free
p parseSQLState...
p e
// END PROCEDURE
Thanks,
Robert Newton
EDPS
Electronic Data Processing Services
Software Engineer
rnewton@xxxxxxxxxxxxxxxxx
This communication is confidential and is intended to be privileged. If
there is a problem with this transmission, please contact the sender. If
the reader of this message is not the intended recipient, or the employee
or agent responsible to deliver it to the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
"Armbruster, Tom"
<Tom.Armbruster@t
ravelocity.com> To
Sent by: "RPG programming on the AS400 /
rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
01/18/2008 04:04 RE: RPG Getting SQLCODE for
PM Trigger's RAISE_ERROR
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
Robert,
The SQLCA structure is populated with the returns for the embedded
statement, which is why SQLSTATE returns 09000. I think the underlying
error can be parsed from SQLERM (SQL trigger &1 in &2 failed with
SQLCODE &3 SQLSTATE &4).
Tom Armbruster
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of RNewton@xxxxxxxxxxxx
Sent: Friday, January 18, 2008 7:57 AM
To: RPG programming on the AS400 / iSeries
Subject: RPG Getting SQLCODE for Trigger's RAISE_ERROR
Hi all,
I have created a before insert trigger that performs a RAISE_ERROR
setting
the SQLSTATE and message when a certain validation fails. This is
working
fine, however when I call the insert statement from embedded RPG, I
cannot
figure a way to get at the SQLSTATE value that I have the trigger
returning
in the event of an error. SQLCA's SQLSTATE always has value 0900
effectively overlaying the error that I raised.
Do any of you know how to get at the error that is raised by RAISE_ERROR
when using embeded SQL in RPG?
Thanks,
Robert Newton
EDPS
Electronic Data Processing Services
Software Engineer
rnewton@xxxxxxxxxxxxxxxxx
This communication is confidential and is intended to be privileged. If
there is a problem with this transmission, please contact the sender. If
the reader of this message is not the intended recipient, or the
employee
or agent responsible to deliver it to the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
midrange.com
