ddtek:sql-insert, ddtek:sql-update, ddtek:sql-delete. The DataDirect XQuery® update functionality requires extensions to the XQuery processing model for function declaration, XQuery expressions, and snapshot semantics.
Learn to: insert a single record in a database table, update records in a database table, and delete records in a database table.
This built-in function inserts a single record in a database table.
The syntax is:
declare updating function ddtek:sql-insert(table as xs:string,column as xs:string,value as item()*,...) external;where:
table is the database table in which to insert the record. The semantics of table are equivalent to those for fn:collection();
column is the column of the database table in which to insert a value.
value is the value to insert into the specified column.
column and value are a pair in a variable argument list. If column is specified without value, an error is raised. You can specify multiple values for this pair, as shown in the example.
The following example inserts a new record with three columns into the holdings table. The columns and their values are
userid=Minollo, stockticker=TIVO, and shares=200.
ddtek:sql-insert("holdings", "userid", "Minollo", "stockticker", "TIVO","shares", 200)This built-in function updates records in a database table.
The syntax is:
declare updating function ddtek:sql-update( row as element()*, column as xs:string, value as item()*, ...) external;where:
row identifies the records in the database table to update. Each item in the sequence must be a row element of the database table returned by a previous fn:collection() call.
column is the column of the database table to update.
value is the new value for the specified column.
column and value are a pair in a variable argument list. If column is specified without value, an error is raised.
The following example updates a record in the holdings table, the record where the userid column equals Minollo and the stockticker column equals PRGS. In this record, the shares column is updated to 500.
ddtek:sql-update( collection("holdings")/holdings[userid="Minollo" and stockticker="PRGS"], "shares", 500)This built-in function deletes records in a database table.
The syntax is:
declare updating function ddtek:sql-delete( row as element()*) external;where:
row identifies the records to be deleted. Each item in the sequence must be a row element of the database table returned by a previous fn:collection() call.
The following example deletes all of the records in the holdings database table where the userid column equals Minollo.
ddtek:sql-delete(collection("holdings")/holdings[userid = "Minollo"])To support updates, DataDirect XQuery extends the XQuery processing model, as discussed in this section.
The syntax of an XQuery function declaration is extended to include an optional keyword, updating, as shown in the following BNF syntax for a function declaration:
FunctionDecl := "declare" "updating"? "function" QName"(" ParamList? ") ("as" SequenceType)?(EnclosedExpr | "external")If the keyword "updating" is not specified, the semantics are as specified in XQuery 1.0. If the keyword "updating" is specified, the semantics are as follows:
Here is an example of a declaration for a user-defined updating function:
declare updating function local:delete($rows as element()*) {ddtek:sql-delete($rows)};local:delete(collection('test')/test)The basic building block of XQuery is an expression. Because of the update capability of DataDirect XQuery, two types of expressions exist for XQueries when using DataDirect XQuery:
An expression is either updating or non-updating; therefore, an XQuery does either an update or returns a result, not both. The following is an example of an updating expression in the Return clause of a FLWOR expression:
for $u in collection("users")//usersreturnddtek:sql-insert ("users2", "userid", $u/userid, name", $u/name)Updating expressions can occur only in the following XQuery expressions:
All other XQuery expressions cannot contain an operand that is an updating expression; if it does, a static error is raised.
To support updates of relational databases, DataDirect XQuery extends the semantics of a FLWOR expression in the following ways:
The following example shows a FLWOR expression that is an updating expression:
for $u in collection("USERS")//USERSreturnddtek:sql-insert ("USERS2", "USERID", $u/USERID, "NAME", $u/NAME)To support updates of relational databases, DataDirect XQuery extends the semantics of a typeswitch expression in the following ways:
The following example shows a Typeswitch expression that is an updating expression:
typeswitch($node)case $a as element() return ddtek:sql-insert("holdings", "userid",$a/name, "stockticker", $a/ticker, "shares", $a/amount)default return ddtek:sql-insert("holdings", "userid", $node, "stockticker","PRGS", "shares", 200)To support updates of relational databases, DataDirect XQuery extends the semantics of a conditional expression in the following ways:
For example, if you are unsure whether an existing row needs to be updated with the new quantity, or if a new row needs to be created, you can use a simple conditional expression:
let $previousHolding := collection("holdings")/holdings[userid = "minollo" and stockticker = "PRGS"]returnif ($previousHolding) thenddtek:sql-update($previousHolding,"shares", $previousHolding/shares + 500)elseddtek:sql-insert("holdings", "userid", "Minollo","stockticker", "PRGS", "shares", 100)To support updates of relational databases, DataDirect XQuery extends the semantics of a comma expression in the following way:
If any operand of a comma expression is an updating expression, the comma expression is an updating expression. In this case, each of the operands must contain either:
To support updates of relational databases, DataDirect XQuery extends the semantics of a parenthesized expression in the following way: If any operand of a parenthesized expression is an updating expression, the parenthesized expression is an updating expression.
Parentheses can be used to enforce a particular evaluation order in expressions that contain multiple operators.
The concept of snapshot semantics is introduced with the new functionality of updating relational database tables through DataDirect XQuery. DataDirect XQuery evaluates all updating and non-updating expressions of an XQuery and creates a virtual "snapshot" of the queried XDM instances before any updates are applied to the underlying database table, which is referred to as snapshot semantics. Then, this snapshot is used to execute all non-updating expressions before any updating expressions are executed. For example, if you have an XQuery that defines a non-updating expression, an updating expression, and then another non-updating expression, you will not see the update of the data for the second non-updating expression because both of the non-updating expressions are executed before the updating expression is executed. Snapshot semantics is supported on a per query basis.
Follow along with an example to learn how to execute an updating expression in XQJ using an XQExpression object.
You can execute updating expressions using either XQExpression or XQPreparedExpression objects. The result of an updating query is always an empty sequence.
The following example executes an updating expression in XQJ using an XQExpression object. The updating expression inserts data into the holdings database table.
// import the XQJ classesimport com.ddtek.xquery3.*;import com.ddtek.xquery3.xqj.DDXQDataSource;// establish a connection to a relational data source// specify the URL and the user ID and passwordDDXQDataSource ds = new DDXQDataSource();ds.setJdbcUrl("jdbc:xquery:sqlserver://server1:1433;databaseName=stocks");XQConnection conn = ds.getConnection("myuserid", "mypswd");// create an expression object that is used to execute a queryXQExpression xqExpression = conn.createExpression();// the queryString es = "ddtek:sql-insert('holdings'," +"'userid','Minollo','stockticker','TIVO','shares',200)";// execute the queryXQResultSequence result = xqExpression.executeQuery(es);// free all resourcesresult.close();xqExpression.close();conn.close();
This section describes how DataDirect XQuery supports transactions, transaction isolation levels, and distributed transactions.
A transaction consists of one or more updating XQueries that have been executed, completed, and then either committed or rolled back. By default, a DataDirect XQuery connection (a new XQConnection object) is in auto-commit mode. Auto-commit causes a commit after each XQuery is evaluated.
To disable auto-commit, specify false as the argument value for setAutoCommit(), which is a method of the XQConnection interface. For example:
...DDXQDataSource ds = new DDXQDataSource();ds.setJdbcUrl("jdbc:xquery:sqlserver://server1:1433;databaseName=stocks");XQConnection conn = ds.getConnection("myuserid", "mypswd");conn.setAutoCommit(false);...When auto-commit is disabled, the application must either commit each transaction explicitly or roll back each transaction explicitly. When a connection is closed, DataDirect XQuery, by default, rolls back the active transaction.
To perform commits and rollbacks, use the commit() and rollback() methods, respectively, of XQConnection.
DataDirect XQuery supports the isolation levels as defined in the JDBC interface java.sql.Connection, which are as follows:
The database to which you are connecting may not support all of these isolation levels, as detailed in following table.
| Database | Read Committed |
Read Uncommitted |
Repeatable Read |
Serializable | None |
| DB2 | X (default) | X | X | X | X |
| Oracle | X (default) | X | |||
| SQL Server | X (default) | X | X | X | |
| Sybase | X (default) | X | X | X |
The names of the DB2 isolation levels do not map one-to-one to the names of the JDBC isolation levels. The following table maps the JDBC isolation levels to the appropriate DB2 isolation levels.
| JDBC Isolation Level | DB2 Isolation Level |
| Read Committed | Cursor Stability |
| Read UnCommitted | Uncommitted Read |
| Repeatable Read | Read Stability |
| Serializable | Repeatable Read |
| None | No Commit * |
| * Supported for DB2 for iSeries versions that do not enable journaling. | |
To set an isolation level for a single connection, specify the appropriate value for the JdbcTransactionIsolationLevel property of DDXQDataSource.
To set an isolation level for multiple connections, specify the appropriate value for the TransactionIsolationLevel property of DXQJDBCConnection.
NOTE: Once a connection is made, the transaction isolation level cannot be changed for that connection (XQConnection object).
DataDirect XQuery does not support distributed transactions. However, it is possible to have a single DataDirect XQuery connection (XQConnection object) with multiple underlying JDBC connections and perform updates if the updates target only one of the JDBC data sources. It is, also, possible during the lifetime of an XQConnection object to update two different JDBC data sources if this is done in separate transactions (not in a single transaction).