NamedPreparedStatement: SQL with Named Parameters

The JDBC Iterator executes SQL statements with named parameters, if they are deposited in the following input XML structure.

Input

HTML/XML

<any element name>
   <namedpreparedstatement resulttag="result element name" timezone="time zone" 
    format="date format">
      <sql>  
         <!--SQL statement (optionally within a CDATA block) -->
         <!--Each parameter has an XML element with the same name as the corresponding <row> child element -->
      </sql>
      <rowset>
         <row>
            <Name><!--value--></Name>
         </row> 
      </rowset> 
   </namedpreparedstatement> 
</any element name>

The following rules apply to each <namedpreparedstatement/> element:

The optional timezoneattribute specifies a time zone according to java.util.TimeZone, e. g. GMT+2, see http://docs.oracle.com/javase/1.5.0/docs/api/java/util/TimeZone.html.
The optional attribute format specifies a date format according to java.text.SimpleDateFormat and defines the returned date values' formats.
It contains one <sql/> element.

The following rules apply to each named parameter XML element within element <sql/>:

The element name for the parameter is equivalent to the element name within the corresponding <row/> element.
The optional attribute mode indicates the “direction” of the data stream (in: Input parameter for the database; out: Result parameter from the database; inout: Both, input parameter and result parameter; default: in.
The optional attribute type specifies the data type, e. g. INTEGER, BLOB, or VARCHAR (default).
The optional attribute timezone specifies a time zone according to java.util.TimeZone, e. g. GMT+2, see http://docs.oracle.com/javase/1.5.0/docs/api/java/util/TimeZone.html.
The optional attribute format specifies a date format according to java.text.SimpleDateFormat and defines the expected value format of the corresponding <row> element.

If you use characters within the SQL statement or for the values or data of the table columns that have a meaning in XML, mask the SQL statement with surrounding CDATA block, e.g. <![CDATA[ SELECT * FROM TABLE WHERE ID<]]><id type="INTEGER" /> or <rowset><row><name><!CDATA[ XML character “<“ in CDATA element]]></name></row></rowset>.

This ensures that the characters in the CDATA element are not interpreted as XML, but as data for the adapter.

The following rules apply to each <rowset/> element:

It can be used as often as required (0-n times)
It contains any number of <row/> elements controlling the statements' execution.

The following rules apply to each <row/> element:

It must contain at least one element corresponds to the element name within the SQL statement.
The elements inside contain the corresponding values.

Make sure to avoid unintended line breaks within the element content of each data element within <row/>, as all line breaks would be converted to \n in the generated SQL statement.

Please note

Allowed data types for the attribute type when setting (input parameter) and reading (ResultSet) values are: BIGINT, BINARY, BIT, BOOLEAN, BLOB, CHAR, CLOB, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGNVARCHAR, LONGVARBINARY, LONGVARCHAR, NCHAR, NCLOB, NULL, NUMERIC, NVARCHAR, REAL, SMALLINT, SQLXML, TIME, TIMESTAMP, TINYINT, VARBINARY und VARCHAR (default), XML (alias for SQLXML).

Example

HTML/XML

<Rootelement>
	<namedpreparedstatement resulttag="namedPreparedStatementWithCData" 
     timezone="UTC" format="dd.MM.yyyy">
		<sql>
             SELECT * FROM PROCESS WHERE X4_DURATION= <duration type="INTEGER" 
                mode="in" /> AND X4_PROCESSNAME= <processname 
                type="VARCHAR" mode="in" />
        </sql>
		<rowset>
			<row>
				<duration>101</duration>
				<processname>Project/Process/Path/Process.wrf</processname>
			</row>
		</rowset>
	</namedpreparedstatement>
</Rootelement>

Output

If the SQL statement was executed successfully, the database connection element outputs an XML document containing the query response as a string with the following structure:

HTML/XML

<Data>
   <result element name>
      <Field1><!--value--></Field1>
      <Fieldn><!--value--></Fieldn>
   </result element name>
</Data>