Home Services Partners Company

Updating Relational Data

Introduction

DataDirect XQuery® supports XQuery updates to relational database tables from inside an XQuery by providing three built-in XQuery update functions. These functions are: ddtek:sql-insertddtek:sql-updateddtek:sql-delete. The DataDirect XQuery® update functionality requires extensions to the XQuery processing model for function declaration, XQuery expressions, and snapshot semantics.

ddtek:SQL - Insert, Update, Delete

Learn to: insert a single record in a database table, update records in a database table, and delete records in a database table.


ddtek:sql-insert

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)
ddtek:sql-update

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)
ddtek:sql-delete

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"])

Extensions to the XQuery Processing Model

To support updates, DataDirect XQuery extends the XQuery processing model, as discussed in this section.


Extension to Function Declaration

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:

  • The XQuery expression must be an updating expression.
  • The function declaration must be declared without "as SequenceType" or the sequence type must be an empty sequence. This means that an updating function cannot return a value.
  • The function declaration cannot be declared as external (except for the built-in functions).

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)
Extensions to XQuery Expressions

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:

  • Updating expressions, which can modify the state of an existing node
  • Non-updating expressions, which cannot modify the state of an existing node

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")//users
return
ddtek:sql-insert ("users2", "userid", $u/userid, name", $u/name)

Updating expressions can occur only in the following XQuery expressions:

  • FLWOR expressions
  • Typeswitch expressions
  • Conditional expressions
  • Comma expressions
  • Parenthesized expressions

All other XQuery expressions cannot contain an operand that is an updating expression; if it does, a static error is raised.

FLWOR Expressions

To support updates of relational databases, DataDirect XQuery extends the semantics of a FLWOR expression in the following ways:

  • The For, Let, Where, and Order By clauses of a FLWOR expression cannot contain updating expressions.
  • If the Return clause contains a non-updating expression, the FLWOR expression is a non-updating expression.
  • If the Return clause contains an updating expression, the FLWOR expression is an updating expression.

The following example shows a FLWOR expression that is an updating expression:

for $u in collection("USERS")//USERS
return
ddtek:sql-insert ("USERS2", "USERID", $u/USERID, "NAME", $u/NAME)
Typeswitch Expressions

To support updates of relational databases, DataDirect XQuery extends the semantics of a typeswitch expression in the following ways:

  • The operand of a typeswitch expression cannot contain an updating expression.
  • If any branch contains an updating expression, the typeswitch expression is an updating expression. In this case, all of its branches must contain either:
    • An updating expression
    • An empty expression ()
    • A call to fn:error()

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)
Conditional Expressions

To support updates of relational databases, DataDirect XQuery extends the semantics of a conditional expression in the following ways:

  • The test expression cannot contain an updating expression.
  • If any branch of the conditional expression contains an updating expression, the conditional expression is an updating expression. In this case, both of its branches must contain either:
    • An updating expression
    • An empty expression ()
    • A call to fn:error()

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"]
return
if ($previousHolding) then
ddtek:sql-update($previousHolding,
"shares", $previousHolding/shares + 500)
else
ddtek:sql-insert("holdings", "userid", "Minollo",
"stockticker", "PRGS", "shares", 100)
Comma Expressions

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:

  • An updating expression
  • An empty expression ()
  • A call to fn:error()
Parenthesized Expressions

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.

Snapshot Semantics

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.

Executing Updates in XQJ

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 classes
import 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 password
DDXQDataSource 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 query
XQExpression xqExpression = conn.createExpression();
// the query
String es = "ddtek:sql-insert('holdings'," +
"'userid','Minollo','stockticker','TIVO','shares',200)";
// execute the query
XQResultSequence result = xqExpression.executeQuery(es);
// free all resources
result.close();
xqExpression.close();
conn.close();

 

Understanding the Transactional Behavior of DataDirect XQuery

This section describes how DataDirect XQuery supports transactions, transaction isolation levels, and distributed transactions.


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.

Transaction Isolation Levels

DataDirect XQuery supports the isolation levels as defined in the JDBC interface java.sql.Connection, which are as follows:

  • java.sql.Connection.TRANSACTION_READ_UNCOMMITTED (Read Uncommitted). Locks are obtained on modifications to the database and held until end of transaction (EOT). Reading from the database does not involve any locking.
  • java.sql.Connection.TRANSACTION_READ_COMMITTED (Read Committed). Locks are acquired for reading and modifying the database. Locks are released after reading but locks on modified objects are held until EOT.
  • java.sql.Connection.TRANSACTION_REPEATABLE_READ (Repeatable Read). Locks are obtained for reading and modifying the database. Locks on all modified objects are held until EOT. Locks obtained for reading data are held until EOT. Locks on nonmodified access structures (such as indexes and hashing structures) are released after reading.
  • java.sql.Connection.TRANSACTION_SERIALIZABLE (Serializable). All data read or modified is locked until EOT. All access structures that are modified are locked until EOT. Access structures used by the query are locked until EOT.
  • java.sql.Connection.TRANSACTION_NONE (None). Transactions are not supported.

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

Distributed Transactions

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

TUTORIAL

Updating Relational Data

jdbc

DataDirect
XQuery Connectors

XQuery Integration to Various Data Sources

community

DataDirect
XQuery Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers