Bureaucrats, editor, reviewer, Administrators
13,554
edits
Changes
Jump to navigation
Jump to search
m
Text replacement - "syntaxhighlight" to "pre"
{| width='100%'
|- valign="top"
| width='120' | '''SignaturesSignature'''|{{Func|<pre>sql:init|( $class as xs:string|) as empty-sequence()}}</pre>
|- valign="top"
| '''Summary'''
{| width='100%'
|- valign="top"
| width='120' | '''SignaturesSignature'''|{{Func|sql:connect|$url as xs:string|xs:anyURI}}<br/ pre>{{Func|sql:connect|( $url as xs:string, $username as xs:string?, $password as xs:string?|xs:anyURI}}<br/ >{{Func|sql:connect|$url as xs:string, $username as xs:string?, $password as xs:string?, $options as map(*)?| := map { }) as xs:anyURI}}<br/ pre>
|- valign="top"
| '''Summary'''
| '''Examples'''
|Connects to an SQL Server and sets autocommit to {{Code|true}}:
<syntaxhighlight pre lang="'xquery"'>
sql:connect('dbc:sqlserver://DBServer', map { 'autocommit': true() })
</syntaxhighlightpre>
|}
{| width='100%'
|- valign="top"
| width='120' | '''SignaturesSignature'''|{{Func|sql:execute|$id as xs:anyURI, $statement as xs:string|item()*}}<br/pre>{{Func|sql:execute|( $id as xs:anyURI, $statement as xs:string, $options as map(*)?| := map { }) as item()*}}</pre>
|- valign="top"
| '''Summary'''
{| width='100%'
|- valign="top"
| width='120' | '''SignaturesSignature'''|{{Func|sql:execute-prepared|$id as xs:anyURI, $params as element(sql:parameters)|item()*}}<br/pre>{{Func|sql:execute-prepared|( $id as xs:anyURI, $params as element(sql:parameters), $options as map(*)?| := map { }) as item()*}}</pre>
|- valign="top"
| '''Summary'''
| This function executes a prepared statement with the specified {{Code|$id}}. The output format is identical to {{Function||sql:execute}}. 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/ >
<syntaxhighlight pre lang="'xquery"'>
element sql:parameters {
element sql:parameter {
}+
}?
</syntaxhighlightpre>
With {{Code|$options}}, the following parameter can be set:
* {{Code|timeout}}: query execution will be interrupted after the specified number of seconds.
{| width='100%'
|- valign="top"
| width='120' | '''SignaturesSignature'''|{{Func|<pre>sql:prepare|( $id as xs:anyURI, $statement as xs:string|) as xs:anyURI}}</pre>
|- valign="top"
| '''Summary'''
{| width='100%'
|- valign="top"
| width='120' | '''SignaturesSignature'''|{{Func|<pre>sql:commit|( $id as xs:anyURI|) as empty-sequence()}}</pre>
|- valign="top"
| '''Summary'''
{| width='100%'
|- valign="top"
| width='120' | '''SignaturesSignature'''|{{Func|<pre>sql:rollback|( $id as xs:anyURI|) as empty-sequence()}}</pre>
|- valign="top"
| '''Summary'''
{| width='100%'
|- valign="top"
| width='120' | '''SignaturesSignature'''|{{Func|<pre>sql:close|( $id as xs:anyURI|) as empty-sequence()}}</pre>
|- valign="top"
| '''Summary'''
A simple select statement can be executed as follows:
<syntaxhighlight pre lang="'xquery"'>
let $id := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
return sql:execute($id, "SELECT * FROM coffees WHERE price < 10")
</syntaxhighlightpre>
The result may look like:
<syntaxhighlight pre lang="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>
</syntaxhighlightpre>
==Prepared Statements==
A prepared select statement can be executed in the following way:
<syntaxhighlight pre lang="'xquery"'>
(: Establish a connection :)
let $conn := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
(: Execute prepared statement :)
return sql:execute-prepared($prep, $params)
</syntaxhighlightpre>
==SQLite==
The following expression demonstrates how SQLite can be addressed with the [https://bitbucket.org/xerial/sqlite-jdbc/ Xerial SQLite JDBC driver]:
<syntaxhighlight pre lang="'xquery"'>
(: Initialize driver :)
sql:init("org.sqlite.JDBC"),
sql:execute($conn, "select * from person")
)
</syntaxhighlightpre>
=Errors=
