This document is also available in these non-normative formats: XML.
This proposal provides a SQL Client API for XPath 3.0. It defines extension functions to connect to relational Database Systems and to execute SQL statements to update data, fetch results etc.. The design is based on the JDBC API [JDBC 3.0]. It has been designed to be compatible with XQuery 3.0 and XSLT 3.0.
1 Introduction
1.1 Namespace conventions
1.2 Function Annotations
1.3 Error management
1.4 XML Schema Types
2 Connection Handling
2.1 sql:connect
2.2 sql:disconnect
2.3 sql:is-connected
2.4 sql:is-readonly
2.5 sql:readonly
3 Transactions
3.1 sql:commit
3.2 sql:rollback
3.3 sql:is-autocommit
3.4 sql:autocommit
3.5 sql:isolation-level
4 Simple Statements
4.1 sql:execute
4.2 sql:execute-query
4.3 sql:execute-query-xml
4.4 sql:execute-query-deterministic
4.5 sql:execute-update
5 Prepared Statements
5.1 sql:prepare-statement
5.2 sql:execute-prepared
5.3 sql:execute-query-prepared
5.4 sql:execute-update-prepared
The module defined by this document defines several functions all in the namespace
http://expath.org/ns/sql. In this document, the sql
prefix, when used, is bound to this namespace URI.
Error codes are defined in the namespace http://expath.org/ns/error. In
this document, the err prefix, when used, is bound to this namespace
URI.
The ann prefix is used for annotations.
The namespace bound to this prefix might vary between implementations and
therefore, is implementation dependent.
This does not affect the functionality.
The prefix xsi is bound to namespace
http://www.w3.org/2001/XMLSchema-instance.
Within this document we define XQuery functions eventually annotated by
%ann:sequential or %ann:nondeterministic. These
annotations can be used by processors for automatic optimization efforts.
However, these annotations might not be supported by any processor in which case
they MAY be ignored.
Error conditions are identified by a code (a QName). When such an error
condition is reached during the execution of the function, a dynamic error is thrown,
with the corresponding error code (as if the standard XPath function
error had been called).
Every SQL operation might raise an error. These errors are defined throughout this specification and summarized in B Summary of Error Conditions. However, not all of the potential error conditions have a distinct error code in this spec. Instead, any operation might raise an error [err:SQL001] with a descriptive error message for error conditions not explicitly mentioned. Additionally, if a SQL statement is the root of the error raised it will be thrown as error object.
Within this specification many structured XDM nodes are defined. Accordingly, we provide an [XML Schema of SQL Client defined types] that MAY be used to validate these nodes.
Non of the function signatures defined within this specification require arguments to be validated against the above mentioned schema. Yet, implementors MAY use the schema to validate arguments within the function implentation.
Opens a connection to a relational database.
%ann:sequentialsql:connect($connection-config aselement(sql:connect)) asxs:string
Returns a uuid identifying the connection that has been opened.
The implementing code determines from the $connection-config
either explicitly (interpreting the driver attribute) or
implicitly (using the type attribute) which driver it has to
load.
In detail, the $connection-config is specified as follows:
<sql:connect
database = string
type = string
driver? = string
user? = string
password? = string
ip? = string
port? = integer/>@database is the name of the database to connect to.
Can also be a system DSN in case of ODBC.
@type the type of the connection referred to as
subprotocol in a JDBC connection URL (e.g.
"jdbc:<subprotocol>:<subname>").
This attribute determines the database type to connect to and might
be interpreted by an implementation to implicitly load a specific
driver for the connection.
@driver the explicit Java driver to use for the
connection.
This attribute is optional because it is Java specific and therefore
not relevant in a C++ implementation.
Consequently, an implementation is free to ignore this attribute and
use a default driver determined implicitly by the type
attribute.
@user is the name of the authenticating user.
@password is the password of the authenticating user.
@ip tells the driver on which node the database can be
found.
Default is "localhost" or "127.0.0.1".
@port tells the driver on which port to connect to the
database.
Default is the default port of the database as specified by
type.
If the authentication failed error [err:SQL28000] will be raised.
If the attempt to connect to a database fails for any other reason error [err:SQL08001] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Given that all parameters are correct the following
sql:connect element will establish a connection to a MySQL
database if passed to the connect function.
A Java processor might interpret the driver attribute for loading the
"com.mysql.jdbc.Driver" class for the connection.
sql:connect(
<sql:connect
database = "mydb"
type = "mysql"
driver = "com.mysql.jdbc.Driver"
user = "jon"
password = "password"
ip = "123.456.123.456"
port = "4444" />
)Closes an open database connection.
%ann:sequentialsql:disconnect($connection-id asxs:string) asempty-sequence()
The connection to be closed is identified by $connection-id.
The function has to make sure that all prepared statements for this connection are properly closed.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the attempt to disconnect from a database fails for any other reason error [err:SQL01002] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Returns true if a connection is connected.
sql:is-connected($connection-id asxs:string) asxs:boolean
If the connection identified by $connection-id is open then
this function returns true; otherwise, the function returns
false.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Returns true if a connection is running in readonly mode.
sql:is-readonly($connection-id asxs:string) asxs:boolean
Returns true if the connection identified by
$connection-id is readonly, false otherwise.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Set readonly mode of a connection.
%ann:sequentialsql:readonly($connection-id asxs:string, $readonly asxs:boolean) asempty-sequence()
Sets the connection identified by $connection-id into
readonly mode if parameter $readonly is true.
Sets the connection identified by $connection-id into
read-write mode if parameter $readonly is false.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Commit current transaction of a connection.
%ann:sequentialsql:commit($connection-id asxs:string) asempty-sequence()
Commits the current transaction of the connection identified by
$connection-id.
In detail, any update made using the connection after the last commit of
this connection will be commited.
If autocommit is turned on for the connection this function has no effect.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Rollback the current transaction of a connection.
%ann:sequentialsql:rollback($connection-id asxs:string) asempty-sequence()
Rollback the current transaction of the connection identified by
$connection-id.
In detail, any update made using the connection after the last commit of
this connection will be rolled back.
If autocommit is turned on for the connection this function has no effect.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Returns true if autocommit is turned on for a connection.
sql:is-autocommit($connection-id asxs:string) asxs:boolean
If autocommit property is turned on for the connection identified by
$connection-id this function will return true;
otherwise it returns false.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Turn autocommit on or off for a connection.
%ann:sequentialsql:autocommit($connection-id asxs:string, $autocommit asxs:boolean) asempty-sequence()
Set autocommit property for the connection identified by
$connection-id.
Passing true as $autocommit parameter enables
automatic commits; otherwise, passing false turns it off.
By default autocommit is turned off for every opened connection.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Get/set the current isolation level of a connection.
sql:isolation-level($connection-id asxs:string) asxs:string
%ann:sequentialsql:isolation-level($connection-id asxs:string, $isolation-level asxs:string) asempty-sequence()
The one-argument version of the function returns a string representing
the current transaction isolation level of the connection identified by
$connection-id.
If isolation levels are not supported by the connected database the
function returns "NOT_SUPPORTED".
The two-argument version of the function set the transaction isolation
level of the connection identified by $connection-id.
This may result in an error being raised:
If transaction isolation levels are not supported in general or
the passed isolation level is not supported
[err:SQL40003].
The following string values stand for the different transaction isolation
levels (allowed values for $isolation-level):
"READ_COMMITTED"
"READ_UNCOMMITTED"
"REPEATABLE_READ"
"SERIALIZABLE"
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
If transaction isolation levels in general are not supported by the
connected database error [err:SQL40003] will be thrown
for the two-argument version; This error is also raised if the
isolation level passed by $isolation-level is
not supported.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Executes any kind of SQL statement provided as $sql string.
%ann:sequentialsql:execute($connection-id asxs:string, $sql asxs:string) asitem()*
In case of an updating SQL statement (e.g. INSERT statement)
this function returns an xs:integer as affected-rows-counter.
No sql:metadata element (as defined below) is returned.
In case of a SQL statement fetching a result (e.g. SELECT
statement) this function returns a sequence of element nodes.
More in detail, the first node returned contains the metadata of the
fetched result and the following nodes contain the actual fetched data.
Firstly, we define the metadata node (first node in a sequence of returned element nodes) as follows:
<sql:metadata>
<sql:column
name = string
type = string
schema = string
table = string
autoincrement? = boolean
nullable? = boolean/> *
</sql:metadata>@name is the name of the column.
@type is the raw SQL type of the database.
@schema is the schema name of the column's table.
@table is the name of the column's table.
@autoincrement is true if this column is automatically
maintained. The default is false if this attribut is not explicitly
set.
@nullable is true if the colums can contain NULL values.
The default is false if this attribute is not explicitly set.
Secondly, we define the data nodes (any node following the metadata node; these nodes contain the actual data) as follows:
<sql:tuple> <sql:element xsi:nil? = boolean> value ? </sql:element> * </sql:tuple> *
sql:tuple represents a row of the returned SQL result.
It contains a set of ordered data elements.
Each data element contains data of one column field.
The order of the elements is appropriate to the columns in the
metadata.
@xsi:nil is set to true if the value of this data
element is NULL. Otherwise, this element contains data, e.g. an
empty string, even if it is empty.
value is the actual data value of a column field.
In almost any case the data is represented as string in a plain text
node (no automatic mapping of SQL types to [XQuery and XPath Data Model (XDM) 3.0]
types; see notes).
If the type of the column is XML an implementation MAY parse the
value of the data field and return it as respective XDM node.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Function call:
sql:execute($connection, "SELECT firstname, nickname, address FROM employees WHERE office='Palo Alto'")
Result:
<sql:metadata>
<sql:column name="firstname" type="varchar(256)" schema="xyz" table="employees"/>
<sql:column name="nickname" type="varchar(256)" schema="xyz" table="employees" nullable="true" />
<sql:column name="address" type="XML" schema="xyz" table="employees"/>
</sql:metadata>
<sql:tuple>
<sql:element>John</sql:element>
<sql:element>Can-Do-Jo</sql:element>
<sql:element>
<address>
<street>2011 El Camino Real</street>
<city>Palo Alto</city>
<state>CA</state>
<zip>94306</zip>
</address>
</sql:element>
</sql:tuple>
<sql:tuple>
<sql:element>Jane</sql:element>
<sql:element xsi:nil="true"/>
<sql:element>
<address>
<street>2012 El Camino Real</street>
<city>Palo Alto</city>
<state>CA</state>
<zip>94306</zip>
</address>
</sql:element>
</sql:tuple>In the above result the address column is of type XML. Therefore, it is parsed by the implementation and returned as XDM element node instead of as string in a text node. Also, note that the nickname column field for "Jane" is NULL.
This specification considered automatic mapping of SQL results into XML as proposed by SQL/XML (for example [XML-Related Specifications (SQL/XML)], [Shape FOR XML AUTO results using SQL views]). Yet, because the extent and depth of the SQL/XML specification prevents simple, practical implementations, we recommend a different schema for XML results of a SQL statement (see above).
Consequently, we suggest NO automatic data conversions from SQL types into
XML datatypes (e.g. SQL date into xs:dateTime).
Instead, the proposed result format returns the database data in plain raw
serialized string format embedded into a text node together with the raw
type name of the database in the according sql:column metadata
element.
The only exception is the XML type which MAY be automatically parsed.
Executes any read-only SQL statement provided as $sql string.
%ann:nondeterministicsql:execute-query($connection-id asxs:string, $sql asxs:string) aselement()*
Executes only non-updating SQL statements, e.g. SELECT
statements.
Returns a sequence of elements starting with a sql:metadata
element followed by sql:tuple elements as defined in
4.1 sql:execute.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Executes any SQL statement provided as $sql string
which returns well-formed XML fragments.
%ann:nondeterministicsql:execute-query-xml($connection-id asxs:string, $sql asxs:string) aselement()*
Executes only non-updating SQL statements, e.g. SELECT
statements that return XML data.
The returned result is a sequence of parsed XML nodes returned by the
statement.
The parsed nodes are NOT prepended by a sql:metadata
element and are NOT wrapped in sql:tuple elements as
defined in 4.1 sql:execute.
Results of the SQL statement MUST return a SINGLE column containing well-formed XML fragments; Otherwise, error [err:FODC0006] is raised. The column containing the result does NOT have to be of raw XML type (as defined in the database), it may also be of type text or CLOB. In any case the implementation of this function MUST try to parse the result that is returned by the database.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
If the result of the SQL statement can not be parsed, i.e. is not well-formed XML error [err:FODC0006] will be raised
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
The first example uses XQuery to query an IBM DB2 database to create an XML result. Function call:
sql:execute-query-xml($connection,
"xquery
let $all := db2-fn:sqlquery('SELECT address FROM employees
WHERE office=''Palo Alto''')
for $street in distinct-values($all//street/text())
return <street>{$street}</street>"
)Result:
<street>2011 El Camino Real</street> <street>2012 El Camino Real</street>
Alternatively, the following example uses SQL/XML functions to create a single column of XML element results. Function call:
sql:execute-query-xml($connection,
"SELECT XMLELEMENT( name 'employee',
XMLELEMENT( name 'firstname', e.firstname),
XMLELEMENT( name 'nickname', e.nickname))
FROM employees e WHERE office='Palo Alto'"
)Result:
<employee> <firstname>John</firstname> <nickname>Can-Do-Jo</nickname> </employee> <employee> <firstname>Jane</firstname> <nickname /> </employee>
Another valid option is to use string operations to create well-formed XML. Example statement sent to an Oracle 11g:
sql:execute-query-xml($connection, "SELECT '<test>hello world</test>' AS result FROM DUAL" )
Result:
<test>hello world</test>
Note, that none of the above results has a prepended
sql:metadata element.
Executes deterministic read-only SQL statements provided as $sql
string.
sql:execute-query-deterministic($connection-id asxs:string, $sql asxs:string) aselement()*
Executes only non-updating SQL statements, e.g. SELECT
statements. The results of the statement are deterministic whithin the
current snapshot. [Definition: A
snapshot is a scope within which underlying XDM instances
are fixed, i.e. updates are pending; The snapshot ends when pending
updates or other side effects are applied]. Within XQuery
Processors not supporting these snapshot semantics this function behaves
exactly as 4.2 sql:execute-query. Returns a sequence of
elements starting with a sql:metadata element followed by
sql:tuple elements as defined in 4.1 sql:execute.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Executes only updating SQL statements provided as $sql
string.
%ann:sequentialsql:execute-update($connection-id asxs:string, $sql asxs:string) asxs:integer
Executes only updating SQL statements provided as $sql
string, e.g. INSERT or UPDATE statements.
Returns an xs:integer as affected-rows-counter.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Creates a prepared statement for multiple executions.
sql:prepare-statement($connection-id asxs:string, $sql asxs:string) asxs:string
Returns a UUID identifying the prepared SQL statement. Questionmarks can be used as parameter placeholders.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Preparing a SQL statement with two placeholders:
sql:prepare-statement($connection, "SELECT * FROM users WHERE id=? AND age>?")
Executes SQL statements prepared with 5.1 sql:prepare-statement
%ann:sequentialsql:execute-prepared($prepared-statement asxs:string, $parameters aselement(sql:parameter)*) asitem()*
Executes any kind of prepared SQL statement (updating and non-updating). Statements are prepared using 5.1 sql:prepare-statement.
In case of an updating SQL statement (e.g. INSERT statement)
this function returns an xs:integer as affected-rows-counter.
No sql:metadata element is returned.
In case of a SQL statement fetching a result (e.g. SELECT
statement) this function returns a sequence of elements starting with a
sql:metadata element followed by sql:tuple
elements as defined in
4.1 sql:execute.
The parameters are passed to the function as ordered sequence of
sql:parameter elements.
The position within this sequence determines the position of the
placeholder ('?') in the original prepared statement it is assigned to.
We define the sql:parameter element as follows:
<sql:parameter type = string> value ? </sql:parameter>
@type is generic type specifier string as defined by
[JDBC Generic SQL Types], e.g. "DATE", "BIGINT", "NULL".
value the value for the placeholder.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
sql:execute-prepared(
$prepared-statement,
(
<sql:parameter type="DATE">{ current-date() }</sql:parameter>
<sql:parameter type="VARCHAR ">hello</sql:parameter>
<sql:parameter type="NULL" />
<sql:parameter type="SMALLINT ">1</sql:parameter>
<sql:parameter type="BLOB">{ $binary }</sql:parameter>
<sql:parameter type="SQLXML"><hello/></sql:parameter>
)
)Executes a non-updating SQL statement prepared with 5.1 sql:prepare-statement
%ann:nondeterministicsql:execute-query-prepared($prepared-statement asxs:string, $parameters aselement(sql:parameter)*) aselement()*
Executes prepared non-updating SQL statement. Statements are prepared using 5.1 sql:prepare-statement.
The result of this function is a sequence of elements starting with a
sql:metadata element followed by sql:tuple
elements as defined in
4.1 sql:execute.
The parameters are passed to the function as ordered sequence of
sql:parameter elements.
See definition of sql:parameter in
5.2 sql:execute-prepared.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.
Executes an updating SQL statement prepared with 5.1 sql:prepare-statement
%ann:sequentialsql:execute-update-prepared($prepared-statement asxs:string, $parameters aselement(sql:parameter)*) asitem()
Executes prepared updating SQL statement. Statements are prepared using 5.1 sql:prepare-statement.
The result of this function is an xs:integer as
affected-rows-counter.
No sql:metadata element (as defined below) is returned.
The parameters are passed to the function as ordered sequence of
sql:parameter elements.
See definition of sql:parameter in
5.2 sql:execute-prepared.
If the connection does not exist error [err:SQL08003] will be raised.
If the connection identifier is invalid error [err:SQL2E000] will be raised.
If the connection is closed or was lost for any reason error [err:SQL08000] will be raised.
In case of any other error condition [err:SQL001] will be raised with a meaningful, descriptive error message.