Difference between revisions of "SQL Module"
m (→Connections: fixed typo, I guess you meant *j*dbc) |
m (→Direct queries: changed highlighting to xquery) |
||
Line 20: | Line 20: | ||
The parameter <code>$connection</code> specifies the connection handle to be used and the parameter <code>$statement</code> - the statement to be executed. If a query statement is executed, the result set is returned as a sequence of <code><sql:tuple/></code> 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: | The parameter <code>$connection</code> specifies the connection handle to be used and the parameter <code>$statement</code> - the statement to be executed. If a query statement is executed, the result set is returned as a sequence of <code><sql:tuple/></code> 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: | ||
− | <pre class="brush: | + | <pre class="brush:xquery"> |
let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse") | let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse") | ||
return sql:execute($conn, "SELECT * FROM coffees WHERE price < 10") | return sql:execute($conn, "SELECT * FROM coffees WHERE price < 10") |
Revision as of 09:22, 6 September 2011
The SQL Module contains XQuery 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 attributes representing the columns returned by the SQL statement. All functions dealing with access to relational databases use the sql
prefix, which is linked to the http://www.basex.org/sql
namespace. The module will be officially supported with the upcoming Version 6.8 of BaseX.
Contents
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: jdbc:<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)