NamedPreparedStatement: SQL with Named Parameters 1

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

Input

CODE

<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 timezone attribute 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 can contain at least one elements with a name that corresponds to the parameter name in
the SQL statement.
The elements inside contain the corresponding value.

For each data element within <row/> make sure to avoid unintended line breaks within the element
value, 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 (Standard), XML (alias for SQLXML).

Example

CODE

<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:

CODE

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