Changes

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 {{Code|lib}} directory. To connect to MySQL, for example, download the [https://dev.mysql.com/downloads/connector/j/ Connector/J Driver] and extract the archive into this directory. =Conventions= {{Mark|Updated with Version 9.0}}:  All functions and errors in this module are assigned to the <code><nowiki>http://basex.org/modules/sql</nowiki></code> namespace, which is statically bound to the {{Code|sql}} prefix.<br/> =Functions= ==sql:init== {| width='100%'|-| width='120' | '''Signatures'''|{code{Func|sql:init|$class as xs:string|empty-sequence()}}|-| '''Summary'''|This function initializes a JDBC driver specified via {{Code|$class}}. This step might be superfluous if the SQL database is not embedded.<br/ >|-| '''Errors'''|{{Error|init|passXQuery Errors#SQL Functions Errors}} option addedthe specified driver is not found.|} ==sql:connect==

|{{Func|xquerysql:evalconnect|$query url as xs:string|item()*xs:anyURI}}<br />{{Func|xquerysql:evalconnect|$query url as xs:string, $user as xs:string, $bindings password as map(*)?xs:string|item()*xs:anyURI}}<br />{{Func|xquerysql:evalconnect|$query url as xs:string, $bindings user as map(*)?xs:string, $options password as map(xs:string, $options as map(*)|xs:string)|item()*anyURI}}<br />

|Evaluates the supplied This function establishes a connection to a relational database and returns a connection id. The parameter {{Code|$queryurl}} string as XQuery expression is the URL of the database and returns shall be of the resulting items.form: {{Code|jdbc:<driver name>:[//<brserver>[/<database>The evaluated query has its own query context]]}}. If a returned node is stored in a databasethe parameters {{Code|$user}} and {{Code|$password}} are specified, a main-memory copy will be returned they are used as result, because credentials for connecting to the referenced database is closed after query execution . The {{Code|$options}} parameter can be used to set connection options.|-| '''Errors'''|{{Error|error|XQuery Errors#SQL Functions Errors}} an SQL exception occurs, e.g. missing JDBC driver or not existing relation.|-| '''Examples'''|Connects to an SQL Server and will not be accessible anymoresets autocommit to {{Code|true}}:<pre class="brush:xquery">sql:connect('dbc:sqlserver://DBServer', map { 'autocommit': true() })</pre>|} ==sql:execute== {{Mark|Updated with Version 9.0}}: Return update count for updating statements. {{Code|$options}} argument added. {| width='100%'|-| width='120' | '''Signatures'''|{{Func|sql:execute|$id as xs:anyURI, $statement as xs:string|item()*}}<br />Variables and context items can be declared via {{Func|sql:execute|$id as xs:anyURI, $statement as xs:string, $options as map(*)|item()*}}|-| '''Summary'''| This function executes an SQL {{Code|$statement}}, using the connection with the specified {{Code|$bindingsid}}. The specified keys must be QNames or stringsreturned result depends on the kind of statement:* If a key is a QNamean update statement was executed, it the number of updated rows will be directly adopted returned as variable nameinteger.* It a key is a stringOtherwise, it may an XML representation of all results will be prefixed with a dollar signreturned. Namespace Currently, {{Code|$options}} parameter can be specified using used to set connection options.With {{Code|$options}}, the [httpfollowing parameter can be set:* {{Code|timeout}}://wwwquery execution will be interrupted after the specified number of seconds.|-| '''Errors'''|{{Error|error|XQuery Errors#SQL Functions Errors}} an SQL exception occurs, e.g.jclarknot existing relation is retrieved.com<br/xml>{{Error|id|XQuery Errors#SQL Functions Errors}} the specified connection does not exist.<br/xmlns>|} ==sql:execute-prepared== {{Mark|Updated with Version 9.htm Clark Notation]0}}: Return update count for updating statements. {| width='100%'|-| width='120' | '''Signatures'''|{{Func|sql:execute-prepared|$id as xs:anyURI, $params as element(sql:parameters)|item()*}}<br/>{{Func|sql:execute-prepared|$id as xs:anyURI, $params as element(sql:parameters), $options as map(*)|item()*}}|-| '''Summary'''| This function executes a prepared statement:* If the specified string The output format is empty, the value will be bound identical to the context item[[#sql:execute|sql:execute]].* The parameter {{Code|$optionsid}} parameter contains evaluation options:refers to the prepared statement.* The optional parameter {{Code|permission$params}}is an element {{Code|<sql: parameters/>}} representing the query will parameters for a prepared statement along with their types and values. The following schema shall be evaluated with used:<br/ >With {{Code|$options}}, the specified permissions (see [[User Management]]).following parameter can be set:

* <pre class="brush:xquery">element sql:parameters { element sql:parameter { attribute type { "int" | "string" | "boolean" | "date" | "double" | "float" | "short" | "time" | "timestamp" | "sqlxml" }, attribute null { "true" | "false" }?, text }+}?</pre>|-| '''Errors'''|{{Error|attribute|XQuery Errors#SQL Functions Errors}} an attribute different from {{Code|type}} and {{Code|memorynull}}is set for a {{Code|<sql: query execution will be interrupted if parameter/>}} element.<br/ >{{Error|error|XQuery Errors#SQL Functions Errors}} an SQL exception occurs, e.g. not existing relation is retrieved.<br/ >{{Error|id|XQuery Errors#SQL Functions Errors}} the specified connection does not exist.<br/ >{{Error|parameters|XQuery Errors#SQL Functions Errors}} wrong number of megabytes will {{Code|<sql:parameter/>}} elements, or parameter type is not specified.<br/>{{Error|type|XQuery Errors#SQL Functions Errors}} the value of a parameter cannot be exceeded. This check works best if only one process is running at converted to the same timespecified format. Moreover|} ==sql:prepare== {| width='100%'|-| width='120' | '''Signatures'''|{{Func|sql:prepare|$id as xs:anyURI, please note that this option enforces garbage collection$statement as xs:string|xs:anyURI}}|-| '''Summary'''|This function prepares an SQL {{Code|$statement}}, so it will take some additional timeusing the specified connection {{Code|$id}}, and it requires GC returns the id reference to this statement. The statement is a string with one or more '?' placeholders. If the value of a field has to be enabled in your JVM.* set to {{Code|NULL}}, then the attribute {{Code|base-urinull}}of the {{Code|<sql: set [https:parameter//www>}} element must be {{Code|true}}.w3|-| '''Errors'''|{{Error|error|XQuery Errors#SQL Functions Errors}} an SQL exception occurs.org<br/TR>{{Error|id|XQuery Errors#SQL Functions Errors}} the specified connection does not exist.<br/xquery>|} ==sql:commit== {| width='100%'|-31/#dt| width='120' | '''Signatures'''|{{Func|sql:commit|$id as xs:anyURI|empty-staticsequence()}}|-base-uri base-uri property] for | '''Summary'''| This function commits the changes made to a relational database, using the query. This URI will be used when resolving relative URIs by functions such as specified connection {{Code|fn:doc$id}}.* |-| '''Errors'''|{{Error|error|XQuery Errors#SQL Functions Errors}} an SQL exception occurs.<br/ >{Code{Error|id|passXQuery Errors#SQL Functions Errors}}the specified connection does not exist.<br/ >|} ==sql: passes on the original error info rollback== {| width='100%'|-| width='120' | '''Signatures'''|{{Func|sql:rollback|$id as xs:anyURI|empty-sequence(line and column number, optional file uri). By default}}|-| '''Summary'''| This function rolls back the changes made to a relational database, this option is using the specified connection {{Code|false$id}}.

|{{Error|updateerror|XQuery Errors#SQL Functions Errors}} the query contains [[XQuery Update#Updating Expressions|updating expressions]]an SQL exception occurs.<br/>{{Error|permissionid|XQuery Errors#SQL Functions Errors}} insufficient permissions for evaluating the queryspecified connection does not exist.<br/>|} ==sql:close== {{Error|timeoutwidth='100%'|-| width='120' | '''Signatures'''|#Errors}} query execution exceeded timeout.<br/>{{ErrorFunc|sql:close|limit$id as xs:anyURI|#Errorsempty-sequence()}} query execution exceeded memory limit.<br/>|-| '''Summary'''| This function closes a database connection with the specified {{Error|nestedCode|#Errors$id}} nested query evaluation is not allowed.<br/>Any other error Opened connections will automatically be closed after the XQuery expression has been evaluated, but in order to save memory, it is always recommendable to close connections that may occur while evaluating the queryare not used anymore.

| '''ExamplesErrors'''|* {{CodeError|error|xquery:eval("1+3")XQuery Errors#SQL Functions Errors}} returns an SQL exception occurs.<br/ >{{CodeError|id|4XQuery Errors#SQL Functions Errors}}the specified connection does not exist.<br />|} =Examples= ==Direct queries== A simple select statement can be executed as follows: <pre class="brush:xquery">let $id := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")return sql:execute($id, "SELECT * You can bind the context and eFROM coffees WHERE price < 10")</pre> The result may look like: <pre class="brush:xml"><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">9.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.gorg/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. operate on a certain database only5</sql:column> <sql:column name="sales">10</sql:column> <sql:column name="total">14</sql:column></sql:row><br /pre> ==Prepared Statements== A prepared select statement can be executed in the following way: <pre class='"brush:xquery'">xquery(: Establish a connection :)let $conn := sql:evalconnect("jdbc:postgresql://countrylocalhost:5432/coffeehouse")(: Obtain a handle to a prepared statement :)let $prep := sql:prepare($conn, map { "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: dbparameter> <sql:open(parameter type='factbookstring'>French_Roast</sql:parameter> </sql:parameters>(: Execute prepared statement :) }return sql:execute-prepared($prep, $params)

The following expression demonstrates how SQLite can be addressed using the [http://bitbucket.org/xerial/sqlite-jdbc Xerial SQLite JDBC driver]: <pre class="brush:xquery">(: Initialize driver :)sql:init("org.sqlite.JDBC"),(: Establish a connection :)let $conn := sql:connect("jdbc:sqlite:evaldatabase.db")return ( (: Create a new table :) sql:execute($conn, "drop table if exists person"declare namespace pref='URI';), declare variable sql:execute($prefconn, "create table person (id integer, name string)"), (:xml external;Run 10 updates :) for $i in 1 to 10 let $prefq :xml= "insert into person values(" || $i || ", '" || $i || "')" return sql:execute($conn, $q), map { '{URI}xml'(: Return table contents :) sql: 'XML' }execute($conn, "select * from person")

let $query {{Mark|Updated with Version 9.0}}: {| class="wikitable" width= "declare namespace pref100%"! width='URI'"110"|Code|Description|-|{{Code|attribute}}|An attribute different from {{Code|type}} and {{Code|null}} is set for a {{Code|&lt; declare variable $prefsql:xml externalparameter/&gt;}} element.|-|{{Code|error}}|An SQL exception occurred.|-|{{Code|id}}|A connection does not exist.|-|{{Code|init}} $pref:xml"|A database driver is not found.|-let $vars := map |{{ xs:QName('pref:xml'): 'XML' Code|parameters}}return xquery|Wrong number of {{Code|&lt;sql:eval($queryparameter/&gt;}} elements, $vars)or parameter type is not specified.|-|{{Code|type}}</pre>|The value of a parameter cannot be converted to the specified format.