Changes

Jump to navigation Jump to search

SQL Module (edit)

Revision as of 13:31, 2 July 2020

267 bytes removed ,  13:31, 2 July 2020
→‎SQLite
=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/>
|-
| '''Errors'''
|{{Error|init|XQuery Errors#SQL Functions Errors}} the specified driver is not found.
|}
|-
| width='120' | '''Signatures'''
|{{Func|sql:connect|$url as xs:string|xs:anyURI}}<br/ >{{Func|sql:connect|$url as xs:string, $user as xs:string, $password as xs:string|xs:anyURI}}<br/ >{{Func|sql:connect|$url as xs:string, $user as xs:string, $password as xs:string, $options as map(xs:string, item()*)?|xs:anyURI}}<br/ >
|-
| '''Summary'''
|-
| '''Errors'''
|{{Error|error|XQuery Errors#SQL Functions Errors}} an SQL exception occurs, e.g. missing JDBC driver or not existing relationoccurred when connecting to the database.
|-
| '''Examples'''
|Connects to an SQL Server and sets autocommit to {{Code|true}}:
<pre classsyntaxhighlight lang="brush:xquery">
sql:connect('dbc:sqlserver://DBServer', map { 'autocommit': true() })
</presyntaxhighlight>
|}
==sql:execute==
 
{{Mark|Updated with Version 9.0}}: Return update count for updating statements.
{| width='100%'
|-
| width='120' | '''Signatures'''
|{{Func|sql:execute|$id as xs:anyURI, $statement as xs:string|item()*}}<br/>{{Func|sql:execute|$id as xs:anyURI, $statement as xs:string, $options as map(*)?|item()*}}
|-
| '''Summary'''
* If an update statement was executed, the number of updated rows will be returned as integer.
* Otherwise, an XML representation of all results will be returned.
With {{Code|$options}}, the following parameter can be set:
* {{Code|timeout}}: query execution will be interrupted after the specified number of seconds.
|-
| '''Errors'''
|{{Error|error|XQuery Errors#SQL Functions Errors}} an error occurred while executing 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|timeout|#Errors}} query execution exceeded timeout.<br/ >
|}
==sql:execute-prepared==
 
{{Mark|Updated with Version 9.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:* with the specified {{Code|$id}}. The output format is identical to [[#sql:execute|sql:execute]].* The parameter {{Code|$id}} refers to the prepared statement.* The optional parameter {{Code|$params}} is an element {{Code|<sql:parameters/>}} representing the parameters for a prepared statement along with their types and values. The following schema shall be used:<br/ ><pre classsyntaxhighlight lang="brush:xquery">
element sql:parameters {
element sql:parameter {
}+
}?
</presyntaxhighlight>With {{Code|$options}}, the following parameter can be set:* {{Code|timeout}}: query execution will be interrupted after the specified number of seconds.
|-
| '''Errors'''
|{{Error|errorattribute|XQuery Errors#SQL Functions Errors}} an SQL exception occurs, e.g. not existing relation is retrieved.<br/ >attribute different from {{ErrorCode|id|XQuery Errors#SQL Functions Errorstype}} the specified connection does not exist.<br/ >and {{ErrorCode|parameters|XQuery Errors#SQL Functions Errorsnull}} the number of is set for a {{Code|<sql:parameter/>}} elements in element.<br/ >{{CodeError|error|<sql:parameters/>#Errors}} differs from the number of placeholders in the prepared statementan error occurred while executing SQL.<br/ >{{Error|BXSQ0004id|XQuery Errors#SQL Functions Errors}} the type of a parameter for a prepared statement is specified connection does not specifiedexist.<br/ >{{Error|attributeparameters|XQuery Errors#SQL Functions Errors}} an attribute different from {{Code|no parameter type}} and specified.<br/>{{CodeError|null}} is set for a {{Codetimeout|<sql:parameter/>#Errors}} elementquery execution exceeded timeout.<br/ >{{Error|type|XQuery Errors#SQL Functions Errors}} the value of a parameter is from type date, time or timestamp and its value is in an invalid cannot be converted to the specified format.<br/ >
|}
|-
| '''Errors'''
|{{Error|error|XQuery Errors#SQL Functions Errors}} an error occurred while executing SQL exception occurs.<br/ >{{Error|id|XQuery Errors#SQL Functions Errors}} the specified connection does not exist.<br/ >
|}
|-
| '''Errors'''
|{{Error|error|XQuery Errors#SQL Functions Errors}} an error occurred while executing SQL exception occurs.<br/ >{{Error|id|XQuery Errors#SQL Functions Errors}} the specified connection does not exist.<br/ >
|}
|-
| '''Errors'''
|{{Error|error|XQuery Errors#SQL Functions Errors}} an error occurred while executing SQL exception occurs.<br/ >{{Error|id|XQuery Errors#SQL Functions Errors}} the specified connection does not exist.<br/ >
|}
|-
| '''Errors'''
|{{Error|error|XQuery Errors#SQL Functions Errors}} an error occurred while executing SQL exception occurs.<br/ >{{Error|id|XQuery Errors#SQL Functions Errors}} the specified connection does not exist.<br/ >
|}
A simple select statement can be executed as follows:
<pre classsyntaxhighlight lang="brush:xquery">
let $id := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
return sql:execute($id, "SELECT * FROM coffees WHERE price < 10")
</presyntaxhighlight>
The result may look like:
<pre classsyntaxhighlight lang="brush:xml">
<sql:row xmlns:sql="http://basex.org/modules/sql">
<sql:column name="cof_name">French_Roast</sql:column>
<sql:column name="total">14</sql:column>
</sql:row>
</presyntaxhighlight>
==Prepared Statements==
A prepared select statement can be executed in the following way:
<pre classsyntaxhighlight lang="brush:xquery">
(: Establish a connection :)
let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
(: Execute prepared statement :)
return sql:execute-prepared($prep, $params)
</presyntaxhighlight>
==SQLite==
The following expression demonstrates how SQLite can be addressed using with the [httphttps://bitbucket.org/xerial/sqlite-jdbc / Xerial SQLite JDBC driver]:
<pre classsyntaxhighlight lang="brush:xquery">
(: Initialize driver :)
sql:init("org.sqlite.JDBC"),
sql:execute($conn, "select * from person")
)
</presyntaxhighlight>
=Errors=
 
{{Mark|Updated with Version 9.0}}:
{| class="wikitable" width="100%"
|{{Code|error}}
|An SQL exception occurred.
|-
|{{Code|id}}
|A connection does not exist.
|-
|{{Code|init}}
|A database driver is not found.
|-
|{{Code|idparameters}}|A connection does not existNo parameter type specified.
|-
|{{Code|parameterstimeout}}|Wrong number of {{Code|&lt;sql:parameter/&gt;}} elements, or parameter type is not specifiedQuery execution exceeded timeout.  
|-
|{{Code|type}}
|A The value of a parameter is from type date, time or timestamp and its value is in an invalid cannot be converted to the specified format.
|}
;Version 9.0
 * Updated: [[#sql:execute|sql:execute]], [[#sql:execute-prepared|sql:execute-prepared]]: Return update count for updating statements. {{Code|$options}} argument added.
* Updated: Connection ids are URIs now.
* Updated: error codes updatesupdated; errors now use the module namespace
;Version 7.5
 
* Updated: prepared statements are now executed via [[#sql:execute-prepared|sql:execute-prepared]]
The module was introduced with Version 7.0.
CG
Bureaucrats, editor, reviewer, Administrators
13,550

edits

Retrieved from "http://docs.basex.org/wiki/Special:MobileDiff/13493...15137"

Navigation menu