Changes

All functions in this module are assigned to the <code>{{Code|http://basex.org/modules/sql</code> }} namespace, which is statically bound to the <code>{{Code|sql</code> }} prefix.<br/>All errors are assigned to the <code>{{Code|http://basex.org/errors</code> }} namespace, which is statically bound to the <code>{{Code|bxerr</code> }} prefix.

|<code><b>{{Func|sql:init</b>(|$class as xs:string) as |empty-sequence()</code>}}

|This function initializes a JDBC driver specified via <code>{{Code|$class</code>}}. This step might be superfluous if the SQL database is not embedded.<br/ >

|<b>[[{{Error|BXSQ0007|XQuery Errors#SQL Functions Errors|BXSQ0007]]</b> is raised if }} the specified driver class is not found.

|<code><b>{{Func|sql:connect</b>(|$url as xs:string) as |xs:integer</code>}}<br/ ><code><b>{{Func|sql:connect</b>(|$url as xs:string, $user as xs:string, $password as xs:string) as |xs:integer</code>}}<br/ ><code><b>{{Func|sql:connect</b>(|$url as xs:string, $user as xs:string, $password as xs:string, $options as item()) as |xs:integer</code>}}<br/ >

|This function establishes a connection to a relational database. As a result a connection handle is returned. The parameter <code>{{Code|$url</code> }} is the URL of the database and shall be of the form: <code>{{Code|jdbc:<driver name>:[//<server>[/<database>]</code>}}. If the parameters <code>{{Code|$user</code> }} and <code>{{Code|$password</code> }} are specified, they are used as credentials for connecting to the database. The parameter <code>{{Code|$options</code> }} can be used to set connection options, e.g. auto-commit mode. It can be specified as:* <code>{{Code|element(sql:options):</code> <code>}} {{Code|&lt;sql:options/&gt;</code> }} must be used as root element, and the options are specified as child nodes, with the element name representing the key and the text node representing the value:<br //><code>{{Code|&lt;sql:options&gt;<br/>&nbsp;&nbsp;&lt;sql:autocommit&gt;true&lt;/sql:autocommit&gt;<br/>&nbsp;&nbsp;...<br/>&lt;/sql:options&gt;</code>}}* [[Map Module|map structure]]: all options can be directly represented as key/value pairs:<br /><code>{{Code|map { "autocommit" := "true", ... </code>}}}<br/>

|<b>[[{{Error|BXSQ0001|XQuery Errors#SQL Functions Errors|BXSQ0001]]</b> is raised if }} an SQL exception occurs, e.g. missing JDBC driver or not existing relation.

|<code><b>{{Func|sql:execute</b>(|$connection as xs:integer, $item as item()) as |element()*</code>}}

| This function executes a query, update or prepared statement. The parameter <code>{{Code|$id</code> }} specifies either a connection handle or a prepared statement handle. The parameter <code>{{Code|$item</code> }} is either a string representing an SQL statement or an element <code>{{Code|<sql:parameters/></code> }} representing the parameters for a prepared statement along with their types and values. In case of the latter, the following schema shall be used:<br/ >

|<b>[[{{Error|BXSQ0001|XQuery Errors#SQL Functions Errors|BXSQ0001]]</b> is raised if }} an SQL exception occurs, e.g. not existing relation is retrieved.<br/ ><b>[[{{Error|BXSQ0002|XQuery Errors#SQL Functions Errors|BXSQ0002]]</b> is raised if }} a wrong connection handle or prepared statement handle is passed.<br/ ><b>[[{{Error|BXSQ0003|XQuery Errors#SQL Functions Errors|BXSQ0003]]</b> is raised if }} the number of <code>{{Code|<sql:parameter/></code> }} elements in <code>{{Code|<sql:parameters/></code> }} differs from the number of placeholders in the prepared statement.<br/ ><b>[[{{Error|BXSQ0004|XQuery Errors#SQL Functions Errors|BXSQ0004]]</b> is raised if }} the type of a parameter for a prepared statement is not specified.<br/ ><b>[[{{Error|BXSQ0005|XQuery Errors#SQL Functions Errors|BXSQ0005]]</b> is raised if }} an attribute different from <code>{{Code|type</code> }} and <code>{{Code|null</code> }} is set for a <code>{{Code|<sql:parameter/></code> }} element.<br/ ><b>[[{{Error|BXSQ0006|XQuery Errors#SQL Functions Errors|BXSQ0006]]</b> is raised if }} a parameter is from type date, time or timestamp and its value is in an invalid format.<br/ >

|<code><b>{{Func|sql:prepare</b>(|$connection as xs:integer, $statement as xs:string) as |xs:integer</code>}}

|This function prepares a statement and returns a handle to it. The parameter <code>{{Code|$connection</code> }} indicates the connection handle to be used. The parameter <code>{{Code|$statement</code> }} is a string representing an SQL statement with one or more '?' placeholders. If the value of a field has to be set to <code>{{Code|NULL</code>}}, then the attribute <code>{{Code|null</code> }} of the element <code>{{Code|<sql:parameter/></code> }} has to be <code>{{Code|true</code>}}.

|<b>[[{{Error|BXSQ0001|XQuery Errors#SQL Functions Errors|BXSQ0001]]</b> is raised if }} an SQL exception occurs.<br/ ><b>[[{{Error|BXSQ0002|XQuery Errors#SQL Functions Errors|BXSQ0002]]</b> is raised if }} a wrong connection handle is passed.<br/ >

|<code><b>{{Func|sql:commit</b>(|$connection as xs:integer) as |empty-sequence()</code>}}

| This function commits the changes made to a relational database. <code>{{Code|$connection</code> }} specifies the connection handle.

|<b>[[{{Error|BXSQ0001|XQuery Errors#SQL Functions Errors|BXSQ0001]]</b> is raised if }} an SQL exception occurs.<br/ ><b>[[{{Error|BXSQ0002|XQuery Errors#SQL Functions Errors|BXSQ0002]]</b> is raised if }} a wrong connection handle is passed.<br/ >

|<code><b>{{Func|sql:rollback</b>(|$connection as xs:integer) as |empty-sequence()</code>}}

| This function rolls back the changes made to a relational database. <code>{{Code|$connection</code> }} specifies the connection handle.

|<b>[[{{Error|BXSQ0001|XQuery Errors#SQL Functions Errors|BXSQ0001]]</b> is raised if }} an SQL exception occurs.<br/ ><b>[[{{Error|BXSQ0002|XQuery Errors#SQL Functions Errors|BXSQ0002]]</b> is raised if }} a wrong connection handle is passed.<br/ >

|<code><b>{{Func|sql:close</b>(|$connection as xs:integer) as |empty-sequence()</code>}}

| This function closes a connection to a relational database. <code>{{Code|$connection</code> }} specifies the connection handle.

|<b>[[{{Error|BXSQ0001|XQuery Errors#SQL Functions Errors|BXSQ0001]]</b> is raised if }} an SQL exception occurs.<br/ ><b>[[{{Error|BXSQ0002|XQuery Errors#SQL Functions Errors|BXSQ0002]]</b> is raised if }} a wrong connection handle is passed.<br/ >

|<code>{{Code|BXSQ0001</code>}}

|<code>{{Code|BXSQ0002</code>}}

|<code>{{Code|BXSQ0003</code>}}|The number of <code>{{Code|&lt;sql:parameter/&gt;</code> }} elements in <code>{{Code|&lt;sql:parameters/&gt;</code> }} differs from the number of placeholders in the prepared statement.

|<code>{{Code|BXSQ0004</code>}}

|<code>{{Code|BXSQ0005</code>}}|An attribute different from <code>{{Code|type</code> }} and <code>{{Code|null</code> }} is set for a <code>{{Code|&lt;sql:parameter/&gt;</code> }} element.

|<code>{{Code|BXSQ0006</code>}}

|<code>{{Code|BXSQ0007</code>}}