PreparedStatement: SQL mit Werten

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

With X4 BPMS release 5.0 or higher, you can alternatively execute SQL statements using named parameters, see NamedPreparedStatement: SQL with Named Parameters 1.

Input

HTML/XML

<any element name>
   <preparedstatement resulttag="result element name" timezone="time zone" 
    format="date format">
      <sql>  
         <!--SQL statement-->
      </sql>
      <columns>
         <column mode="in" name="any NC name" 
          index="placeholder index" type="data type" 
          timezone="time zone" format="date format">
         </column>
      </columns>
      <rowset>
         <row>
            <Name><!--value--></Name>
         </row> 
      </rowset> 
   </preparedstatement> 
</any element name>

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

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.

It contains at least one <sql/> 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<1 ]]> 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 <column/> element:

It can be used as often as required (0-n times)
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 attribute name specifies any NC name for a parameter.
The attribute index specifies an integer placeholder index for the parameter defined within the attribute name.
The optional attribute type specifies the data type (e. g. INTEGER or BLOB; Default: VARCHAR).
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.

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

It can be used as often as required (0-n times)
It contains at least one element <row/> controlling the statements' execution.

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

It can contain any number of elements with the column name or the column alias set in the SQL statement.
The elements inside contain the respective value.

Please note

The attributes mode and format are required parameters up till X4 Server Revision 31469; in newer version they are optional.
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 and VARCHAR (default), XML (alias for SQLXML).

Example

HTML/XML

<Rootelement>
   <preparedstatement resulttag="abc" timezone="MEZ"
      format="dd.MM.yyyy">
      <sql>  
      <![CDATA[SELECT * FROM JMS_USERS WHERE USERID=?]]>
      </sql>
      <columns>
         <column mode="in" name="id" index="1"
            type="INTEGER">
         </column>
      </columns>
      <rowset>
         <row>
            <id>23</id>
         </row>
      </rowset>
   </preparedstatement>
</Rootelement>

Output

If the SQL statement was executed successfully, the adapter 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>