11.7 Statement Type

The Subscriber channel executes embedded SQL statements, but it doesn’t understand them. The JDBC 1 interface defines several methods for executing different types of SQL statements. The following table contains these methods:

Table 11-1 Methods for Executing SQL Statements

Statement Type

Method Executed

SELECT

java.sql.Statement.executeQuery(String query):java.sql.ResultSet

INSERT

java.sql.Statement.executeUpdate(String update):int

UPDATE

java.sql.Statement.executeUpdate(String update):int

DELETE

java.sql.Statement.executeUpdate(String update):int

CALL or EXECUTE SELECT INSERT UPDATE DELETE

java.sql.Statement.execute(String sql):boolean

The simplest solution is to map all SQL statements to the java.sql.Statement.execute(String sql):boolean method. By default, the Subscriber channel uses this method.

Some third-party drivers, particularly Oracle’s JDBC drivers, incorrectly implement the methods used to determine the number of result sets that this method generates. Consequently, the driver can get caught in an infinite loop leading to high CPU utilization. To circumvent this problem, you can use the jdbc:type attribute on any <jdbc:statement> element to map the SQL statements contained in it to the following methods instead of the default method:

The jdbc:type attribute has two values: update and query. For INSERT, UPDATE, or DELETE statements, set the value to update. For SELECT statements, set the value to query. In the absence of this attribute, the driver maps all SQL statements to the default method. If placed on any element other than <jdbc:statement>, this attribute is ignored.

Recommendations:

The following XML shows an example of the jdbc:type attribute. The attribute is in bold text.

<input xmlns:jdbc="urn:dirxml:jdbc">
    <add class-name="usr">
        <add-attr name="lname">
            <value>Doe</value>
        </add-attr>
    </add>
    <jdbc:statement jdbc:type="update">
        <jdbc:sql>UPDATE indirect.usr SET fname = ’John’ 
                  WHERE idu = {$idu}</jdbc:sql>
    </jdbc:statement>
</input>