Embedding SQL Statements in XDS Events

The following section includes information to help you embed SQL in XDS events.

All examples reference table usr below. The primary key generation method used to obtain primary key values is irrelevant to the examples in this section.

CREATE TABLE usr 
( 
    idu   INTEGER  NOT NULL, 
    fname VARCHAR2(64), 
    lname VARCHAR2(64), 
 
    CONSTRAINT pk_usr_idu PRIMARY KEY(idu)    
);

You can use embedded SQL in XDS events. In the same way that you can install database triggers on a table and cause side effects in a database, embedded SQL in XDS events acts as a virtual trigger with similar capabilities.

SQL is embedded in XDS events through the <jdbc:statement> and <jdbc:sql> elements. The <jdbc:statement> element can contain one or more <jdbc:sql> elements.

NOTE:  The namespace prefix jdbc used throughout this section is implicitly bound to the namespace urn:dirxml:jdbc when referenced outside of an XML document.

The following XML example shows an embedded SQL statement.

<input xmlns:jdbc="urn:dirxml:jdbc"> 
     <add class-name="usr"> 
         <add-attr name="lname"> 
             <value>Doe</value> 
         </add-attr> 
     </add> 
     <jdbc:statement> 
         <jdbc:sql>UPDATE indirect.usr SET fname = 'John'         </jdbc:sql> 
     </jdbc:statement> 
</input>

IMPORTANT:  Use namespace-prefixed elements and attributes to embed SQL. Otherwise, the driver will not recognize them. In the above example, the namespace is urn:dirxml:jdbc. The prefix is the identifier to the right of the xmlns identifier. In the above example, the prefix is jdbc. In practice, the prefix can be whatever you want it to be, as long as it is bound to the correct namespace.

Because the Subscriber channel resolves <add> events to one or more INSERT statements, the XML shown above resolves to:

SET AUTOCOMMIT OFF 
INSERT INTO indirect.usr(lname)VALUES('Doe'); 
COMMIT; --explicit commit 
UPDATE indirect.usr SET fname = 'John'; 
COMMIT; --explicit commit


Variable Substitution

Rather than require you to parse field values from an association, the Subscriber channel supports variable substitution in embedded SQL statements. For example:

<input xmlns:jdbc="urn:dirxml:jdbc"> 
    <modify class-name="usr"> 
        <association>idu=1,table=usr,schema=indirect 
        </association> 
        <modify-attr name="lname"> 
            <add-value> 
                <value>DoeRaeMe</value> 
            </add-value> 
        </modify-attr>	 
    </modify> 
    <jdbc:statement> 
           <jdbc:sql>UPDATE indirect.usr SET fname = 'John' WHERE 
                     idu = {$idu}</jdbc:sql> 
    </jdbc:statement> 
</input>

Variable placeholders must adhere to the XSLT attribute value template syntax {$field-name}. Also, the association element must precede the <jdbc:statement> element in the XDS document, or must be present as a child of the <jdbc:statement> element.

The field-name variable must refer to one of the naming RDN attribute names in the association value. The above example has only one naming attribute, idu.

An <add> event is the only event where an association element is not required to precede embedded SQL statements with variable substitution because the association has not been created yet. Additionally, any embedded SQL statements using variable substitution must follow, not precede, the <add> event. For example:

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

To prevent tracing of sensitive information, you can use {$$password} to refer to the contents of the immediately preceding <password> element within the same document.

<input xmlns:jdbc="urn:dirxml:jdbc"> 
     <add class-name="usr"> 
          <password>Doe{$idu}</password> 
          <add-attr name="lname"> 
              <value>Doe</value> 
          </add-attr> 
     </add> 
     <jdbc:statement> 
          <jdbc:sql>CREATE USER Doe IDENTIFIED BY  
                    {$$password}</jdbc:sql> 
     </jdbc:statement>	 
</input>

Furthermore, you can also refer to the driver's database authentication password specified by the Application Password parameter as {$$$driver-password} . See Application Password.


Statement Placement

In the same way that database triggers can fire before or after a triggering statement, embedded SQL can be positioned before or after the triggering XDS event. The following examples show how you can embed SQL before or after an XDS event.


Virtual Before Trigger

<input xmlns:jdbc"urn:dirxml:jdbc"> 
     <jdbc:statement>         <association>idu=1,table=usr,schema=indirect         </association>         <jdbc:sql>UPDATE indirect.usr SET fname = 'John' WHERE                    idu = {$idu}</JDBC:SQL> 
     </jdbc:statement> 
     <modify class-name="usr"> 
          <association>idu=1,table=usr,schema=indirect 
          </association> 
          <modify-attr name="lname"> 
               <remove-all-values/>  
               <add-value> 
                   <value>Doe</value> 
               </add-value> 
          </modify-attr> 
     </modify> 
</input>

This XML resolves to:

SET AUTOCOMMIT OFF 
UPDATE indirect.usr SET fname = 'John' WHERE idu = 1; 
COMMIT; --explicit commit 
UPDATE indirect.usr SET lname = 'Doe'  WHERE idu = 1; 
COMMIT; --explicit commit


Virtual After Trigger

<input xmlns:jdbc"urn:dirxml:jdbc"> 
     <modify class-name="usr"> 
          <association>idu=1,table=usr,schema=indirect 
          </association> 
          <modify-attr name="lname"> 
               <remove-all-values/>  
               <add-value> 
                   <value>Doe</value> 
               </add-value> 
          </modify-attr> 
     </modify> 
     <jdbc:statement>          <jdbc:sql>UPDATE indirect.usr SET fname = 'John' WHERE                     idu = {$idu}</jdbc:sql> 
     </jdbc:statement> 
</input>

This XML resolves to:

SET AUTOCOMMIT OFF 
UPDATE indirect.usr SET lname = 'Doe'  WHERE idu = 1; 
COMMIT; --explicit commit 
UPDATE indirect.usr SET fname = 'John' WHERE idu = 1; 
COMMIT; --explicit commit


Manual vs. Automatic Transactions

You can manually group embedded SQL and XDS events by using two custom attributes:


jdbc:transaction-type

This attribute has two values: manual and auto. By default, most XDS events of interest (<add>, <modify> and <delete>) are implicitly set to the manual transaction type. The manual setting enables XDS events to resolve to a transaction consisting of one or more SQL statement.

By default, embedded SQL events are set to auto transaction type because some SQL statements, such as DDL statements, cannot usually be included in a manual transaction.

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

This XML resolves to:

SET AUTOCOMMIT ON 
INSERT INTO indirect.usr(lname) VALUES('Doe'); 
-- implicit commit 
UPDATE indirect.usr SET fname = 'John' WHERE idu = 1; 
-- implicit commit


jdbc:transaction-id

The Subscriber channel ignores this attribute unless the element's jdbc:transaction-type attribute value defaults to or is explicitly set to manual. The following XML shows an example of a manual transaction:

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

This XML resolves to:

SET AUTOCOMMIT OFF 
INSERT INTO indirect.usr(lname) VALUES('Doe'); 
UPDATE indirect.usr SET fname = 'John' WHERE idu = 1; 
COMMIT; -- explicit commit


Transaction Isolation Level

In addition to grouping statements, you can use transactions to preserve the integrity of data in a database. Transactions can lock data to prevent concurrent access or modification. The isolation level of a transaction determines how locks are set. Usually, the default isolation level that the driver uses is sufficient and should not be altered.

The custom attribute jdbc:isolation-level allows you to adjust the isolation transaction level if necessary. The java.sql.Connection parameter defines five possible values in the interface. See java.sql.Connection.

The driver's default transaction isolation level is read committed unless overridden by a descriptor file. In manual transactions, place the jdbc:isolation-level attribute on the first element in the transaction. This attribute is ignored on subsequent elements. For example:

<input xmlns:jdbc="urn:dirxml:jdbc"> 
    <add class-name="usr" jdbc:transaction-id="0" 
                          jdbc:isolation-level="serializable"> 
        <add-attr name="lname"> 
            <value>Doe</value> 
        </add-attr> 
    </add> 
    <jdbc:statement jdbc:transaction-type="manual" 
                    jdbc:transaction-id="0"> 
        <jdbc:sql>UPDATE indirect.usr SET fname = 'John'  
                  WHERE idu = {$idu}</jdbc:sql> 
    </jdbc:statement> 
</input>

This XML resolves to:

SET AUTOCOMMIT OFF 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
INSERT INTO indirect.usr(lname) VALUES('Doe'); 
UPDATE indirect.usr SET fname = 'John' WHERE idu = 1; 
COMMIT; -- explicit commit


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:

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:

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


SQL Queries

To fully support the query capabilities of a database and avoid the difficulty of translating native SQL queries into an XDS format, the driver supports native SQL query processing. You can embed select statements in XDS documents in exactly the same way as any other SQL statement.

For example, assume that the table usr has the following contents:

idu fname lname

1

John

Doe

The XML document below would result in an output document containing a single result set.

<input xmlns:jdbc="urn:dirxml:jdbc"> 
    <jdbc:statement jdbc:type="query"> 
        <jdbc:sql>SELECT * FROM indirect.usr</jdbc:sql> 
    </jdbc:statement> 
</input>	
<output xmlns:jdbc="urn:dirxml:jdbc"> 
    <jdbc:result-set jdbc:number-of-rows="1"> 
        <jdbc:row jdbc:number="1"> 
            <jdbc:column jdbc:name="idu" 
                         jdbc:position="1" 
                         jdbc:type="java.sql.Types.BIGINT 
                <jdbc:value>l</jdbc:value> 
            </jdbc:column>			 
            <jdbc:column jdbc:name="fname"  
                         jdbc:position="2" 
                         jdbc:type="java.sql.Types.VARCHAR> 
                <jdbc:value>John</jdbc:value> 
            </jdbc:column> 
            <jdbc:column jdbc:name="lname" 
                         jdbc:position="3"                         jdbc:type="java.sql.Types.VARCHAR> 
                <jdbc:value>Doe</jdbc:value> 
            </jdbc:column> 
        </jdbc:row> 
    </jdbc:result-set> 
    <status level="success"/> 
</output>

SQL queries always produce a single <jdbc:result-set> element whether or not the result set contains any rows. If the result set is empty, the jdbc:number-of-rows attribute is set to zero.

You can embed more than one query in a document. SQL queries don't require that the referenced tables/views are visible to the driver. However, XDS queries do.


Data Definition Language (DDL) Statements

Generally, it is not possible to run a Data Definition Language (DDL) statement in a database trigger because most databases do not allow mixed DML and DDL transactions. Although virtual triggers do not overcome this transactional limitation, they do allow DDL statements to be executed as a side-effect of an XDS event.

For example:

<input xmlns:jdbc="urn:dirxml:jdbc"> 
    <add class-name="usr"> 
        <add-attr name="lname"> 
            <value>Doe</value> 
        </add-attr> 
    </add> 
    <jdbc:statement> 
        <jdbc:sql>CREATE USER indirect IDENTIFIED BY novell        </jdbc:sql> 
    </jdbc:statement> 
</input>

This XML resolves to:

SET AUTOCOMMIT OFF 
INSERT INTO indirect.usr(lname) VALUES('Doe'); 
COMMIT; -- explicit commit 
SET AUTOCOMMIT ON 
CREATE USER indirect IDENTIFIED BY novell; 
-- implicit commit

Using the jdbc:transaction-id and jdbc:transaction-type attributes to group DML and DDL statements into a single transaction causes the transaction to be rolled back on most databases. Because DDL statements are generally executed as separate transactions, it is possible that the insert statement in the above example might succeed and the create user statement might roll back.

It is not possible, however, that the insert statement fail and the create user statement succeed. The Subscriber channel stops executing chained transactions at the point where the first transaction is rolled back.


Logical Operations

Because it is not generally possible to mix DML and DDL statements in a single transaction, a single event can consist of one or more transactions. You can use the jdbc:op-id and jdbc:op-type to group multiple transactions together into a single logical operation. When so grouped, all members of the operation are handled as a single unit with regard to status. If one member has an error, all members return the same status level. Similarly, all members share the same status type.

<input xmlns:jdbc="urn:dirxml:jdbc"> 
    <add class-name="usr" jdbc:op-id="0"  
                          jdbc:op-type="password-set-operation"> 
        <add-attr name="lname"> 
            <value>Doe</value> 
        </add-attr> 
        <password>Doe{$idu}</password> 
    </add> 
    <jdbc:statement jdbc:op-id="0"> 
        <jdbc:sql>CREATE USER Doe IDENTIFIED BY {$$password} 
        </jdbc:sql> 
    </jdbc:statement> 
</input>

The jdbc:op-type attribute is ignored on all elements except the first element in a logical operation.


Best Practices

For performance reasons, it is better to call a single stored procedure/function that contains multiple SQL statements than to embed multiple statements in an XDS document.

In the following examples, the single stored procedure/function is preferred.

Single Stored Procedure

<input xmlns:jdbc="urn:dirxml:jdbc"> 
    <add class-name="usr"> 
        <add-attr name="fname"> 
            <value>John</value> 
        </add-attr> 
        <add-attr name="lname"> 
            <value>Doe</value> 
        </add-attr> 
    </add> 
    <jdbc:statement> 
        <jdbc:sql>CALL PROCEDURE set_name('John', 'Doe')</jdbc:sql> 
    </jdbc:statement> 
</input>

Embedded Multiple Statements

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

The syntax used to call stored procedures/functions varies by database. For additional information, see Stored Procedure and Function JDBC Call Syntaxes.