The following section includes information to help you embed SQL in XDS events.
All examples reference the following indirect.usr table.
CREATE TABLE indirect.usr
(
idu INTEGER NOT NULL,
fname VARCHAR2(64),
lname VARCHAR2(64),
CONSTRAINT pk_usr_idu PRIMARY KEY(idu)
);
Embedded SQL allows you to embed SQL statements in XDS-formatted XML documents. You can use embedded SQL statements in conjuction with XDS events or stand-alone. When embedded SQL statements are used stand-alone, embedded SQL processing does not require that the driver know anything about tables/view in the target database. As such, the driver can run in schema-unaware mode. See Synchronization Filter. When using embedded SQL stand-alone, you must establish associations manually. The driver won’t establish them for you.
When used in conjunction with XDS events, embedded SQL can act as a virtual database trigger. In the same way that you can install database triggers on a table and cause side effects in a database when certain SQL statements are executed, embedded SQL can cause side effects in a database in response to certain XDS events.
You can accomplish the following by embedding SQL in XDS events:
Create database users or roles.
Manage user passwords
You can set, check or modify user passwords.
Manage database user or role privileges.
The sample configuration file JDBCv2.xml demonstrates how to create database users, manage user passwords, and manage user privileges as a side effect of XDS events. To enable database user account management, set the Global Configuration Variable (GCV) user-ddl to true. Embedded SQL examples are contained in the User DDL Command Transformation style sheet on the Subscriber channel.
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.
The namespace prefix jdbc used throughout this section is implicitly bound to the namespace urn:dirxml:jdbc when referenced outside of an XML document.
You must use namespace-prefixed embedded SQL elements and attributes. Otherwise, the driver will not recognize them. In all examples in this section, the prefix used is jdbc. In practice, the prefix can be whatever you want it to be, as long as it is bound to the namespace value urn:dirxml:jdbc.
The following XML example illustrates how to use and properly namespace-prefix embedded SQL elements. In the following example, the namespace declaration and namespace prefixes are bolded:
<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>
The following XML example illustrates how to use the <jdbc:statement> and <jdbc:sql> elements and their interpretation. In the following example, embedded SQL elements are bolded:
<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>
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
Rather than require you to parse field values from an association, the Subscriber channel supports token substitution in embedded SQL statements. In the following examples, tokens and the values they reference are bolded:
<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>
Token placeholders must adhere to the XSLT attribute value template syntax {$field-name}. Also, the referenced association element must precede the <jdbc:statement> element in the XDS document, or must be present as a child of the <jdbc:statement> element. Alternatively, instead of copying the association element as child of the <jdbc:statement> element, you copy the src-entry-id of the element containing the association element onto the <jdbc:statement> element. Both approaches are bolded in the following examples:
<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>
<association>idu=1,table=usr,schema=indirect</association>
<jdbc:sql>UPDATE indirect.usr SET fname = ’John’ WHERE
idu = {$idu}</jdbc:sql>
</jdbc:statement>
</input>
<input xmlns:jdbc="urn:dirxml:jdbc">
<modify class-name="usr" src-entry-id="0">
<association>idu=1,table=usr,schema=indirect</association>
<modify-attr name="lname">
<add-value>
<value>DoeRaeMe</value>
</add-value>
</modify-attr>
</modify>
<jdbc:statement src-entry-id="0">
<jdbc:sql>UPDATE indirect.usr SET fname = ’John’ WHERE
idu = {$idu}</jdbc:sql>
</jdbc:statement>
</input>
The {$field-name} token must refer to one of the naming RDN attribute names in the association value. The above examples have 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 tokens because the association has not been created yet. Additionally, any embedded SQL statements using tokens 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 the {$$password} token to refer to the contents of the immediately preceding <password> element within the same document. In the following example, the password token and the value it refers to are bolded:
<input xmlns:jdbc="urn:dirxml:jdbc">
<add class-name="usr">
<password>some password</password>
<add-attr name="fname">
<value>John</value>
</add-attr>
<add-attr name="lname">
<value>Doe</value>
</add-attr>
</add>
<jdbc:statement>
<jdbc:sql>CREATE USER jdoe 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. Named password substitution is not yet supported.
Just as with association elements, the referenced password element must precede the <jdbc:statement> element in the XDS document or must be present as a child of the <jdbc:statement> element. Alternatively, instead of copying the password element as child of the <jdbc:statement> element, you copy the src-entry-id of the element containing the password element onto the <jdbc:statement> element. Both approaches are bolded in the following examples:
<input xmlns:jdbc="urn:dirxml:jdbc">
<add class-name="usr">
<password>some password</password>
<add-attr name="fname">
<value>John</value>
</add-attr>
<add-attr name="lname">
<value>Doe</value>
</add-attr>
</add>
<jdbc:statement>
<password>some password</password>
<jdbc:sql>CREATE USER jdoe IDENTIFIED BY
{$$password}</jdbc:sql>
</jdbc:statement>
</input>
<input xmlns:jdbc="urn:dirxml:jdbc">
<add class-name="usr" src-entry-id="0">
<password>some password</password>
<add-attr name="fname">
<value>John</value>
</add-attr>
<add-attr name="lname">
<value>Doe</value>
</add-attr>
</add>
<jdbc:statement src-entry-id="0">
<jdbc:sql>CREATE USER jdoe IDENTIFIED BY
{$$password}</jdbc:sql>
</jdbc:statement>
</input>
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.
<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
<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
You can manually group embedded SQL and XDS events by using two custom attributes:
jdbc:transaction-type
jdbc:transaction-id
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. In the following example, the attribute is in bold text.
<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
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. The attribute is in bold text.
<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
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.
none
read uncommitted
read committed
repeatable read
serializable
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. In the following example. the attribute is in bold text.
<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
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 5-24 Methods for Executing SQL Statements
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:
java.sql.Statement.executeQuery(String query):java.sql.ResultSet
java.sql.Statement.executeUpdate(String update):int
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:
Place the jdbc:type=”query” attribute value on all SELECT statements.
Place the jdbc:type=”update” attribute value on all INSERT, UPDATE, and DELETE statements.
Place no attribute value on stored procedure/function calls.
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>
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:
The XML document below results 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 in the synchronization schema be visible to the driver. However, XDS queries do.
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="fname">
<value>John</value>
</add-attr>
<add-attr name="lname">
<value>Doe</value>
</add-attr>
</add>
<jdbc:statement>
<jdbc:sql>CREATE USER jdoe IDENTIFIED BY novell</jdbc:sql>
</jdbc:statement>
</input>
This XML resolves to:
SET AUTOCOMMIT OFF INSERT INTO indirect.usr(fname, lname) VALUES(’John’, ’Doe’); COMMIT; -- explicit commit SET AUTOCOMMIT ON CREATE USER jdoe 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 driver stops executing chained transactions at the point where the first transaction is rolled back.
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="fname">
<value>John</value>
</add-attr>
<add-attr name="lname">
<value>Doe</value>
</add-attr>
<password>Doe{$idu}</password>
</add>
<jdbc:statement jdbc:op-id="0">
<jdbc:sql>CREATE USER jdoe 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.
Initially setting a password is usually accomplished by creating a database user account. Assuming an <add> event is generated on the Subscriber channel, the following is an example of the output generated by XSLT style sheets that implement password set as a side effect of an XDS <add> event:
<input xmlns:jdbc="urn:dirxml:jdbc">
<add class-name="usr" jdbc:op-id="0"
jdbc:op-type="password-set-operation">
<add-attr name="fname">
<value>John</value>
</add-attr>
<add-attr name="lname">
<value>Doe</value>
</add-attr>
<password>Doe{$idu}</password>
</add>
<jdbc:statement jdbc:op-id="0">
<jdbc:sql>CREATE USER jdoe IDENTIFIED BY {$$password}
</jdbc:sql>
</jdbc:statement>
</input>
The <add> event is logically bound to the CREATE USER DDL statement by the jdbc:op-id and jdbc:op-type attributes.
The User DDL Command Transformation style sheet in the JDBCv2.xml sample configuration file contains sample XSLT templates that bind user account creation DDL statements to <add> events for all databases that support them.
Initially setting a password is usually accomplished by altering an existing database user account. Assuming a <modify-password> event is generated on the Subscriber channel, the following is an example of the output generated by XSLT style sheets that implement modify-password:
<input xmlns:jdbc="urn:dirxml:jdbc">
<modify-password jdbc:op-id="0"
jdbc:op-type="password-set-operation">
<password>new password</password>
</modify-password>
<jdbc:statement jdbc:op-id="0">
<jdbc:sql>ALTER USER jdoe IDENTIFIED BY {$$password}
</jdbc:sql>
</jdbc:statement>
</input>
The <modify-password> event is logically bound to the ALTER USER DDL statement by the jdbc:op-id and jdbc:op-type attributes.
The User DDL Command Transformation style sheet in the JDBCv2.xml sample configuration contains sample XSLT templates that bind password maintenance DDL statements to <modify-password> events for all databases that support them.
Unlike password set, check object password does not require embedded SQL statements or attributes. Only a user account name is required. This could be obtained from an association value (assuming that associations are being maintained manually), a directory attribute, or a database field. If stored in the directory or database, a query must be issued to retrieve the value.
The JDBCv2.xml sample configuration file stores database user account names in database fields.
NOTE:Some databases, such as Sybase Adpative Server Enterprise and Microsoft SQL Server, differentiate between user account names and login account names. Therefore, you might need to store two names, not just one.
To implement check object password, append a dest-dn attribute value to the <check-object-password> event. In the following example, the dest-dn attribute is bolded:
<input xmlns:jdbc="urn:dirxml:jdbc">
<check-object-password dest-dn="jdoe">
<password>whatever</password>
</check-object-password>
</input>
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 or 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>
Multiple Embedded 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 or functions varies by database. For additional information, see Stored Procedure and Function JDBC Call Syntaxes.