administrator, Bureaucrats, editor, reviewer
41
edits
Changes
Jump to navigation
Jump to search
Created page with "==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..."
==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 <code>sql</code>.
==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:
<code><b>sql:connect</b>($url as xs:string) as xs:int</code><br/>
<code><b>sql:connect</b>($url as xs:string, $auto-commit as xs:boolean) as xs:int</code><br/>
<code><b>sql:connect</b>($url as xs:string, $auto-commit as xs:boolean, $user as xs:string, $password as xs:string) as xs:int</code>
The parameter $url is the URL of the database and shall be of the form: <code>odbc:<driver name>:[//<server>[/<database>]</code>. The parameter <code>$auto-commit</code> is used to indicate if the auto-commit mode shall be activated or not. Its default value is true. If the parameters <code>$user</code> and <code>$password</code> 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 <code>sql:execute</code>:
<code><b>sql:execute</b>($connection as xs:int, $statement as xs:string) as element()*</code>
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><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:xml">
let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
return sql:execute($conn, "SELECT * FROM coffees WHERE price < 10")
</pre>
The result will look like:
<pre class="brush:xml">
<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"/>
</pre>
===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 <code>sql:prepare</code>:
<code><b>sql:prepare</b>($connection as xs:int, $statement as xs:string) as xs:int</code>
The parameter <code>$connection</code> indicates the connection handle to be used. The parameter <code>$statement</code> is a string representing an SQL statement with one or more '?' placeholders. The prepared statement can then be execute using the following signature of the <code>sql:execute</code> function:
<code><b>sql:execute</b>($prepared-statement as xs:int, $parameters as xs:element) as element()*</code>
The parameter <code>$prepared-statement</code> is the handle to the prepared statement created via <code>sql:prepare</code>. <code>$parameters</code> specifies the parameters of the prepared statement and must contain exactly the same number of <code><parameter/></code> elements as the number of placeholders in the statement. It shall follow the schema:
<pre class="brush:xml">
element sql:parameters {
element sql:parameter {
attribute type { "int"|"string"|"boolean"|"date"|"double"|"float"|"short"|"time"|"timestamp" },
attribute null { "true"|"false" }?,
text }+ }?
</pre>
For example, a prepared select statement can be executed in the following way:
<pre class="brush:xml">
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)
</pre>
If a the value of a field has to be set to <code>NULL</code>, then the attribute <code>null</code> of the element <code><sql:parameter/></code> has to be <code>true</code>.
==Commit==
Changes made to a relational database using a connection handle <code>$connection</code> can be committed using the function <code>sql:commit</code>:
<code><b>sql:commit</b>($connection as xs:int)</code>
==Rollback==
Changes made to a relational database using a connection handle <code>$connection</code> can be rolled back using the function <code>sql:rollback</code>:
<code><b>sql:rollback</b>($connection as xs:int)</code>
==Closing a connection==
A connection to a relational database can be closed using the <code>sql:close</code> function:
<code><b>sql:close</b>($connection as xs:int)</code>
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 <code>sql</code>.
==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:
<code><b>sql:connect</b>($url as xs:string) as xs:int</code><br/>
<code><b>sql:connect</b>($url as xs:string, $auto-commit as xs:boolean) as xs:int</code><br/>
<code><b>sql:connect</b>($url as xs:string, $auto-commit as xs:boolean, $user as xs:string, $password as xs:string) as xs:int</code>
The parameter $url is the URL of the database and shall be of the form: <code>odbc:<driver name>:[//<server>[/<database>]</code>. The parameter <code>$auto-commit</code> is used to indicate if the auto-commit mode shall be activated or not. Its default value is true. If the parameters <code>$user</code> and <code>$password</code> 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 <code>sql:execute</code>:
<code><b>sql:execute</b>($connection as xs:int, $statement as xs:string) as element()*</code>
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><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:xml">
let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
return sql:execute($conn, "SELECT * FROM coffees WHERE price < 10")
</pre>
The result will look like:
<pre class="brush:xml">
<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"/>
</pre>
===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 <code>sql:prepare</code>:
<code><b>sql:prepare</b>($connection as xs:int, $statement as xs:string) as xs:int</code>
The parameter <code>$connection</code> indicates the connection handle to be used. The parameter <code>$statement</code> is a string representing an SQL statement with one or more '?' placeholders. The prepared statement can then be execute using the following signature of the <code>sql:execute</code> function:
<code><b>sql:execute</b>($prepared-statement as xs:int, $parameters as xs:element) as element()*</code>
The parameter <code>$prepared-statement</code> is the handle to the prepared statement created via <code>sql:prepare</code>. <code>$parameters</code> specifies the parameters of the prepared statement and must contain exactly the same number of <code><parameter/></code> elements as the number of placeholders in the statement. It shall follow the schema:
<pre class="brush:xml">
element sql:parameters {
element sql:parameter {
attribute type { "int"|"string"|"boolean"|"date"|"double"|"float"|"short"|"time"|"timestamp" },
attribute null { "true"|"false" }?,
text }+ }?
</pre>
For example, a prepared select statement can be executed in the following way:
<pre class="brush:xml">
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)
</pre>
If a the value of a field has to be set to <code>NULL</code>, then the attribute <code>null</code> of the element <code><sql:parameter/></code> has to be <code>true</code>.
==Commit==
Changes made to a relational database using a connection handle <code>$connection</code> can be committed using the function <code>sql:commit</code>:
<code><b>sql:commit</b>($connection as xs:int)</code>
==Rollback==
Changes made to a relational database using a connection handle <code>$connection</code> can be rolled back using the function <code>sql:rollback</code>:
<code><b>sql:rollback</b>($connection as xs:int)</code>
==Closing a connection==
A connection to a relational database can be closed using the <code>sql:close</code> function:
<code><b>sql:close</b>($connection as xs:int)</code>
