News
Abstract
Enable GET DIAGNOSTICS MESSAGE_TEXT to work against external UDF and UDTFs
Content
When external User Defined Functions (UDF) and User Defined Table Functions (UDTF) are created with PARAMETER STYLE SQL, the external function has the ability to return message text to describe a failure.
The SQL Reference describes the message text parameter as:
A VARCHAR(1000) output parameter for the message text.
Prior to this change, the application had no way of retrieving the message text using SQL.
SQL applications can retrieve the unique message text returned by the UDF/UDTF via the GET DIAGNOSTICS SQL statement.
For example:
EXEC SQL GET DIAGNOSTICS CONDITION 1 :Lclmsgtext = MESSAGE_TEXT;
Note:
- The GET DIAGNOSTICS SQL statement must be executed immediately after execution of the SQL statement which used the UDF or UDTF
- If the UDF or UDTF did not return anything for the message text, GET DIAGNOSTICS will return the message text associated with the SQL message identifier
Was this topic helpful?
Document Information
Modified date:
14 January 2020
UID
ibm11167682