CallableStatement: Stored Procedures

The JDBC Iterator executes stored procedures via JDBC callable statements with dynamical input data, if they are deposited in the following input XML structure.

Input

HTML/XML

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

The following rules apply to each <callablestatement/> 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 data 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; see http://download.oracle.com/javase/1,5.0/docs/guide/jdbc/getstart/callablestatement.html); Default: in.
The attribute name specifies an arbitrary 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 (allowed data types see note below; default: VARCHAR).
The attribute name specifies an arbitrary name for a parameter.
The optionale Attribut 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 an.

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 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>
   <callablestatement resulttag="abc" timezone="MEZ"
      format="dd.MM.yyyy">
      <sql>  
      <![CDATA[ {call getData(?)} ]]>
      </sql>
      <columns>
         <column mode="out" name="id" index="1"
            type="INTEGER">
         </column>
      </columns>
      <rowset>
         <row>
            <id>TestUser</id>
         </row>
      </rowset>
   </callablestatement>
</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>