SQL Functions

This 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 http://basex.org/modules/sql namespace, which is statically bound to the sql prefix.

Functions

sql:init

sql:init(
  $class  as xs:string
) as empty-sequence()

sql:connect

sql:connect(
  $url      as xs:string,
  $options  as map(*)?    := {}
) as xs:anyURI

sql:connect('jdbc:sqlserver://DBServer', { 'autocommit': true() })

sql:execute

sql:execute(
  $id         as xs:anyURI,
  $statement  as xs:string,
  $options    as map(*)?    := {}
) as item()*

sql:execute-prepared

sql:execute-prepared(
  $id       as xs:anyURI,
  $params   as element(sql:parameters)?  := (),
  $options  as map(*)?                   := {}
) as item()*

element sql:parameters {
  element sql:parameter {
    attribute type {
      "bigdecimal" | "boolean" | "byte" | "date" | "double" | "float" |
      "int" | "long" | "short" | "sqlxml" | "string" | "time" | "timestamp"
    },
    attribute null { "true" | "false" }?,
    text
  }+
}?

sql:prepare

sql:prepare(
  $id         as xs:anyURI,
  $statement  as xs:string
) as xs:anyURI

sql:commit

sql:commit(
  $id  as xs:anyURI
) as empty-sequence()

sql:rollback

sql:rollback(
  $id  as xs:anyURI
) as empty-sequence()

sql:close

sql:close(
  $id  as xs:anyURI
) as empty-sequence()

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:

<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="cof_name">French_Roast</sql:column>
  <sql:column name="sup_id">49</sql:column>
  <sql:column name="price">3.5</sql:column>
  <sql:column name="sales">15</sql:column>
  <sql:column name="total">30</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="cof_name">French_Roast_Decaf</sql:column>
  <sql:column name="sup_id">49</sql:column>
  <sql:column name="price">7.5</sql:column>
  <sql:column name="sales">10</sql:column>
  <sql:column name="total">14</sql:column>
</sql:row>

Prepared Statements

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

(: 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 with the Xerial SQLite JDBC driver:

(: 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

• Updated: sql:connect: Credentials have become optional.

• Updated: sql:execute-prepared, additional types added

• 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

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

• Added: New module added.