Re: Error message Text for SQL

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