SQL Module

This XQuery Module contains functions to access relational databases from XQuery using SQL. With this module, you can execute query, update and prepared statements, and the result sets are returned as sequences of XML elements representing tuples. Each element has children representing the columns returned by the SQL statement.

This module uses JDBC to connect to a SQL server. Hence, your JDBC driver will need to be added to the classpath, too. If you work with the full distributions of BaseX, you can copy the driver into the lib directory. To connect to MySQL, for example, download the Connector/J Driver and extract the archive into this directory.

=Conventions=

All functions and errors in this module are assigned to the  namespace, which is statically bound to the sql prefix.

=Functions=

sql:close
=Examples=

Direct queries
A simple select statement can be executed as follows:

 let $id := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse") return sql:execute($id, "SELECT * FROM coffees WHERE price < 10")

The result may look like:

  French_Roast 49 9.5 15 30   French_Roast_Decaf</sql:column> 49</sql:column> 7.5</sql:column> 10</sql:column> 14</sql:column> </sql:row>

Prepared Statements
A prepared select statement can be executed in the following way:

<pre class="brush:xquery"> (: Establish a connection :) let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse") (: Obtain a handle to a prepared statement :) let $prep := sql:prepare($conn, "SELECT * FROM coffees WHERE price < ? AND cof_name = ?") (: Values and types of prepared statement parameters :) let $params := <sql:parameters> <sql:parameter type='double'>10</sql:parameter> <sql:parameter type='string'>French_Roast</sql:parameter> </sql:parameters> (: Execute prepared statement :) return sql:execute-prepared($prep, $params)

SQLite
The following expression demonstrates how SQLite can be addressed using the Xerial SQLite JDBC driver:

<pre class="brush:xquery"> (: Initialize driver :) sql:init("org.sqlite.JDBC"), (: Establish a connection :) let $conn := sql:connect("jdbc:sqlite:database.db") return ( (: Create a new table :)  sql:execute($conn, "drop table if exists person"),  sql:execute($conn, "create table person (id integer, name string)"),  (: Run 10 updates :)  for $i in 1 to 10  let $q := "insert into person values(" || $i || ", '" || $i || "')"  return sql:execute($conn, $q),  (: Return table contents :)  sql:execute($conn, "select * from person") )

=Errors=

=Changelog=


 * Version 9.0


 * Updated: sql:execute, sql:execute-prepared: Return update count for updating statements. $options argument added.
 * Updated: Connection ids are URIs now.
 * Updated: error codes updated; errors now use the module namespace


 * Version 7.5


 * Updated: prepared statements are now executed via sql:execute-prepared

The module was introduced with Version 7.0.