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/custom directory. JDBC drivers are available for many relational databases, for example:

• PostgreSQL
• MariaDB
• MySQL
• SQLite

Conventions

All functions and errors are in the http://basex.org/modules/sql namespace, to which the sql prefix is statically bound.

Functions

sql:init

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

sql:connect

sql:connect(
  $url       as xs:string,
  $username  as xs:string?  := (),
  $password  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-batch

Added: New function.

sql:execute-batch(
  $id       as xs:anyURI,
  $params   as (element(sql:parameters)|array(*))*  := (),
  $options  as map(*)?                              := {}
) as xs:integer*

let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
let $prep := sql:prepare($conn, "INSERT INTO coffees (name, price) VALUES (?, ?)")
return sql:execute-batch($prep, (
  [ "Espresso", 2.5e0 ],
  [ "Latte", 3.0e0 ],
  [ "Mocha", 3.5e0 ]
))

sql:execute-prepared

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

sql:execute-prepared($prep, [ 10, 'French_Roast' ])

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,
  $options    as map(*)?    := {}
) 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)

The same parameters can be supplied more concisely as an array, with the SQL types inferred from the values:

let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
let $prep := sql:prepare($conn, "SELECT * FROM coffees WHERE price < ? AND cof_name = ?")
return sql:execute-prepared($prep, [ 10e0, 'French_Roast' ])

SQLite

The following expression demonstrates how SQLite can be addressed:

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

• Added: sql:execute-batch: executes a prepared statement over multiple parameter sets.
• Updated: sql:execute, sql:prepare: generated-keys option added.
• Updated: sql:execute, sql:execute-prepared: null option added.
• Updated: sql:execute-prepared: parameters can be supplied as an array.

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