|
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:mailing list
<<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)
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.