SQL Module

From BaseX Documentation
Revision as of 12:20, 5 September 2011 by Rosishadura (talk | contribs)
Jump to navigation Jump to search

Introduction

The SQL Module allows access to 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 attributes representing the columns returned by the SQL statement. All functions dealing with access to relational databases are located in the namespace sql.

Connections

A connection to a relational database can be established using the function sql:connect. As a result a connection handle is returned. The possible signatures are:

sql:connect($url as xs:string) as xs:int
sql:connect($url as xs:string, $auto-commit as xs:boolean) as xs:int
sql:connect($url as xs:string, $auto-commit as xs:boolean, $user as xs:string, $password as xs:string) as xs:int

The parameter $url is the URL of the database and shall be of the form: odbc:<driver name>:<server> [/<database>. The parameter $auto-commit is used to indicate if the auto-commit mode shall be activated or not. Its default value is true. If the parameters $user and $password are specified, they are used as credentials for connecting to the database.

Executing Queries

Once a connection is established, the returned connection handle can be used to execute queries on the database. Our SQL module supports both direct queries and prepared statements.

Direct queries

A query or an update statement can be executed using the function sql:execute:

sql:execute($connection as xs:int, $statement as xs:string) as element()*

The parameter $connection specifies the connection handle to be used and the parameter $statement - the statement to be executed. If a query statement is executed, the result set is returned as a sequence of <sql:tuple/> elements, each one representing a tuple. In case of an update statement an empty sequence is returned. For example, a simple select statement can be executed on the following way:

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

The result will look like:

<sql:tuple xmlns:sql="http://www.basex.org/sql" cof_name="French_Roast" sup_id="49" price="9.5" sales="15" total="30"/>
<sql:tuple xmlns:sql="http://www.basex.org/sql" cof_name="French_Roast_Decaf" sup_id="49" price="7.5" sales="10" total="14"/>
<sql:tuple xmlns:sql="http://www.basex.org/sql" cof_name="Colombian_Decaf" sup_id="101" price="8.75" sales="6" total="12" date="2010-10-10 13:56:11.0"/>

Prepared Statements

In order to execute a prepared statement, first a prepared statement handle has to be created. This can be done with the function sql:prepare:

sql:prepare($connection as xs:int, $statement as xs:string) as xs:int

The parameter $connection indicates the connection handle to be used. The parameter $statement is a string representing an SQL statement with one or more '?' placeholders. The prepared statement can then be executed using the following signature of the sql:execute function:

sql:execute($prepared-statement as xs:int, $parameters as xs:element) as element()*

The parameter $prepared-statement is the handle to the prepared statement created via sql:prepare. $parameters specifies the parameters of the prepared statement and must contain exactly the same number of <parameter/> elements as the number of placeholders in the statement. It shall follow the schema:

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

For example, a prepared select statement can be executed in the following way:

let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
let $prep := sql:prepare($conn, "SELECT * FROM coffees WHERE price < ? AND cof_name = ?")
let $params := <sql:parameters>
                <sql:parameter type='double'>10</sql:parameter>
                <sql:parameter type='string'>French_Roast</sql:parameter>
               </sql:parameters>
return sql:execute($prep, $params)

If a the value of a field has to be set to NULL, then the attribute null of the element <sql:parameter/> has to be true.

Commit

Changes made to a relational database using a connection handle $connection can be committed using the function sql:commit:

sql:commit($connection as xs:int)

Rollback

Changes made to a relational database using a connection handle $connection can be rolled back using the function sql:rollback:

sql:rollback($connection as xs:int)

Closing a connection

A connection to a relational database can be closed using the sql:close function:

sql:close($connection as xs:int)