Re: Error message Text for SQL

Jeff - as Chuck pointed out you can do it by studying the DSPMSGD output for any message you want to extract the details from. Since SQLCODE equates directly to SQ(L) message numbers this is easy enough to do. You may see a blog on this topic later today. <grin>

For me the number of messages where I care about the individual variables is relatively small so I have no incentive to automate the process and make it generic - but it is a nice weapon to have in the old quiver.


On 2013-11-11, at 9:49 PM, Jeff Young <jyoung0950@xxxxxxxxx> wrote:

Jon,
Is there any way to determine genericaly how many tokens are needed so that
this logic could be used in a catch all exception routine for SQL
exceptions?

Thanks,


On Mon, Nov 11, 2013 at 7:54 PM, Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

Thanks for this Chuck - it got me thinking.

The DB2_ORDINAL_TOKEN_s do indeed contain the substitution text used in
the message. I also got a note from Kent Milligan that suggested that I
might be missing a PTF as the "~" I am seeing is the character
representation of the error position - i.e. 161. But if the PFT corrects
that and returns it as a character string "161".

Taking the missing PTF into account I have retrieved both
DB2_ORDINAL_TOKEN_1 and DB2_ORDINAL_TOKEN_2. That seems to give me
everything I need.

As you say - it should all be much easier!

Thanks for your help.

For the archive the required syntax is:

Exec SQL
GET DIAGNOSTICS EXCEPTION 1
:errorText = DB2_ORDINAL_TOKEN_1;

Exec SQL
GET DIAGNOSTICS EXCEPTION 1
:errorText = DB2_ORDINAL_TOKEN_2;

This can be continued for as many tokens as are present/required. As
Chuck pointed out earlier you can determine how many there are to retrieve
by looking at the message details for the SQL message that appears in the
log for a specific error condition. In the case of SQL validation there are
only 2. _1 is the error position in the XML text and _2 is the detailed
message describing the specific validation error.

That was all a lot harder than it should have been!


On 2013-11-11, at 6:23 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 11-Nov-2013 14:17 -0800, Jon Paris wrote:

<<SNIP>>

The SQL error code really doesn't help at all in this case (XML
validation) as all it tells me is that it failed. I need to know
_what_ failed and _where_ in the XML doc it is.

The information is clearly available as it appears in the job log.

I've found that DB2_TOKEN_STRING gives me the full length text that
is truncated into SQLERRMC so that gives me the _what_ part.
MESSAGE_TEXT just gives me the Level 1 text of the SQ20399 error
which is the bit I can easily derive from the SQLSTATE. The full job
log message here:

Message ID . . . . . . : SQ20399 <<SNIP>>
Message . . . . : XML parsing or validation failed.
Cause . . . . . : The XML parsing failed during validation. The byte
offset into the XML value being processed after conversion to UTF-8
is 161. The XML parser error description is: cvc-datatype-valid.1.2:
The value "A38472" is not a valid value for the "int" datatype.

As you can see it includes the location (161) It seems so odd that
via diagnostics I can get L1 and the latter part of the L2 text - but
not the first bit of L2.

To see what the /tokens/ provide, issue the following request. If
that is not understood [in conjunction with my prior reply], then
include the result of the following request so what are the FMT()
definitions for that message are known... and a more desirable GET
DIAGNOSTIC request to allow constructing the same thing that the IBM i
SQL does in its messaging:

DSPMSGD RANGE(SQ20399 *ONLY) MSGF(QSQLMSG) DETAIL(*FULL)
FMTTXT(*YES) OUTPUT(*PRINT)

--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and 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.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com




--
This is the RPG programming on the IBM i (AS/400 and 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.

--
Jeff Young
Sr. Programmer Analyst
--
This is the RPG programming on the IBM i (AS/400 and 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.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com