Re: Error message Text for SQL

On 11-Nov-2013 16:54 -0800, Jon Paris wrote:

<<SNIP>>

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.

i.e. error message SQ20399

_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!

The following example is somewhat redundant for including the /string/ of tokens, in addition to each of the seven tokens it requires [for -407 exception condition], but the point is to show that the syntax allows for multiple comma-separated variables set from the condition-information-item. Having to invoke the SQL only once, at least makes it nicer:

Exec SQL
get diagnostics condition 1
:tkn_str = db2_token_string
, :tkn1 = db2_ordinal_token_1
, :tkn2 = db2_ordinal_token_2
, :tkn3 = db2_ordinal_token_3
, :tkn4 = db2_ordinal_token_4
, :tkn5 = db2_ordinal_token_5
, :tkn6 = db2_ordinal_token_6
, :tkn7 = db2_ordinal_token_7
;