SQL query on DB2 with XML data type results in Invalid operation error
A query of XML data types in IBM DB2 in Rational Integration Tester (RIT) of IBM Rational Test Workbench gives the error: "Invalid operation: No data is retrieved for OUT parameter".
You see an error such as the following.
[jcc][t4][3.63.123] Invalid operation: No data is retrieved for OUT parameter. ERRORCODE=-4472, SQLSTATE=null
DB2 is returning an XML object that RIT incorrectly interprets.
Diagnosing the problem
- Test the query in IBM Data Studio, to verify that the query itself is valid.
- Review the returned data. If the data looks like the following output, you might have an XML object.
Resolving the problem
Cast the data that the query returns to another type. The following example uses the XMLCAST option to cast the returned XML objects to the VARCHAR() data type.
All source code and/or binaries attached to this document are referred to here as "the Program". IBM is not providing program services of any kind for the Program. IBM is providing the Program on an "AS IS" basis without warranty of any kind. IBM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES (INCLUDING LOST PROFITS OR SAVINGS), EVEN IF IBM, OR ITS RESELLER, HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
SELECT XMLCAST (XMLQUERY('$d' PASSING MY_DATA AS "d") AS VARCHAR (20))
More support for:
Rational Test Workbench
Rational Integration Tester
Software version: 8.0
Operating system(s): AIX, Linux, Solaris, Windows
Reference #: 1618154
Modified date: 21 December 2012