1. Zorba 3.0 Documentation
  2. Modules
  3. Data Connectors
  4. SQLite

http://zorba.io/modules/sqlite

View as XML or JSON.

This module provides functionality to extract, add and modify data from SQLite databases.

Function Summary

clear-params ($pstmnt as xs:anyURI) as empty-sequence() external

Set all parameters to null inside a prepared statement.
close-prepared ($pstmnt as xs:anyURI) as empty-sequence() external

Close and free resources associated to a prepared statement.
commit ($conn as xs:anyURI) as xs:anyURI external

Commits all pending update operations in this SQLite database.
connect ($db-name as xs:string) as xs:anyURI external

Connect to a SQLite database.
connect ($db-name as xs:string, $options as object()?) as xs:anyURI external

Connect to a SQLite database with optional options.
execute-query-prepared ($pstmnt as xs:anyURI) as object()* external

Execute a query (select command) over an already connected SQLite database object.
execute-query ($conn as xs:anyURI, $sqlstr as xs:string) as object()* external

Executes a query (select command) over an already opened SQLite database object.
execute-update-prepared ($pstmnt as xs:anyURI) as xs:integer external

Execute a update command over an already connected SQLite database object.
execute-update ($conn as xs:anyURI, $sqlstr as xs:string) as xs:integer external

Executes an update command over an already opened SQLite database object.
is-connected ($conn as xs:anyURI) as xs:boolean external

Returns whether on not the passed SQLite database object is connected.
metadata ($pstmnt as xs:anyURI) as object() external

Returns the metadata associated to a given prepared SQLite statement.
prepare-statement ($conn as xs:anyURI, $stmnt as xs:string) as xs:anyURI external

Compiles a prepared statement based on an already connected SQLite database and a string that defines the sql command.
rollback ($conn as xs:anyURI) as xs:anyURI external

Rollbacks all pending update operations in this SQLite database.
set-boolean ($pstmnt as xs:anyURI, $param-num as xs:integer, $val as xs:boolean) as empty-sequence() external

Binds a boolean to a placeholder inside a prepared statement.
set-null ($pstmnt as xs:anyURI, $param-num as xs:integer) as empty-sequence() external

Set a null to a placeholder inside a prepared statement.
set-numeric ($pstmnt as xs:anyURI, $param-num as xs:integer, $val as xs:anyAtomicType) as empty-sequence() external

Binds a double variable to a placeholder inside a prepared statement.
set-string ($pstmnt as xs:anyURI, $param-num as xs:integer, $val as xs:string) as empty-sequence() external

Binds a string variable to a placeholder inside a prepared statement.
set-value ($pstmnt as xs:anyURI, $param-num as xs:integer, $val as item()) as empty-sequence() external

Binds a value to a placeholder inside a prepared statement using the same type as the item given.

Functions

clear-params#1

declare  %an:sequential function s:clear-params($pstmnt as xs:anyURI) as empty-sequence() external
Set all parameters to null inside a prepared statement.

Parameters

pstmnt as xs:anyURI
the prepared statement already compiled as xs:anyURI.

Returns

empty-sequence()
nothing.

close-prepared#1

declare  %an:sequential function s:close-prepared($pstmnt as xs:anyURI) as empty-sequence() external
Close and free resources associated to a prepared statement.

Parameters

pstmnt as xs:anyURI
the prepared statement to be closed.

Returns

empty-sequence()
nothing.

commit#1

declare  %an:sequential function s:commit($conn as xs:anyURI) as xs:anyURI external
Commits all pending update operations in this SQLite database.

Parameters

conn as xs:anyURI
the SQLite database object as xs:anyURI.

Returns

xs:anyURI
the passed SQLite object.

connect#1

declare  %an:nondeterministic function s:connect($db-name as xs:string) as xs:anyURI external
Connect to a SQLite database.

Parameters

db-name as xs:string
the SQLite database name to be opened as xs:string.

Returns

xs:anyURI
the SQLite database object as xs:anyURI.

connect#2

declare  %an:nondeterministic function s:connect($db-name as xs:string, $options as object()?) as xs:anyURI external
Connect to a SQLite database with optional options.

All options are true/false values. Available options are: open-read-only, open-create, open-no-mutex, and open-shared-cache. The options are of the form: 

 {
   "open_read_only" : true,
   "open_create     : false,
   ...
 }

Parameters

db-name as xs:string
the SQLite database name to be opened as xs:string.
options as object()
a JSON object containing SQLite connection options.

Returns

xs:anyURI
the SQLite database object as xs:anyURI.

execute-query-prepared#1

declare  %an:sequential function s:execute-query-prepared($pstmnt as xs:anyURI) as object()* external
Execute a query (select command) over an already connected SQLite database object.

Parameters

pstmnt as xs:anyURI
the query command to be executed as xs:anyURI.

Returns

object()*
a sequence of JSON objects representing the query results.

execute-query#2

declare  %an:nondeterministic function s:execute-query($conn as xs:anyURI, $sqlstr as xs:string) as object()* external
Executes a query (select command) over an already opened SQLite database object.

Parameters

conn as xs:anyURI
an already opened SQLite database object as xs:anyURI.
sqlstr as xs:string
the query to be executed as xs:string.

Returns

object()*
a sequence of JSON objects describing each row returned.

execute-update-prepared#1

declare  %an:sequential function s:execute-update-prepared($pstmnt as xs:anyURI) as xs:integer external
Execute a update command over an already connected SQLite database object.

Parameters

pstmnt as xs:anyURI
the update command to be executed as xs:anyURI.

Returns

xs:integer
an integer that represents the amount of rows affected.

execute-update#2

declare  %an:sequential function s:execute-update($conn as xs:anyURI, $sqlstr as xs:string) as xs:integer external
Executes an update command over an already opened SQLite database object.

Parameters

conn as xs:anyURI
an already opened SQLite database object as xs:anyURI.
sqlstr as xs:string
the update command to be executed as xs:string.

Returns

xs:integer
the amount of rows modified by such update command.

is-connected#1

declare  %an:nondeterministic function s:is-connected($conn as xs:anyURI) as xs:boolean external
Returns whether on not the passed SQLite database object is connected.

Parameters

conn as xs:anyURI
the SQLite database object as xs:anyURI.

Returns

xs:boolean
true if the given SQLite database object is connected, false otherwise.

metadata#1

declare  %an:nondeterministic function s:metadata($pstmnt as xs:anyURI) as object() external
Returns the metadata associated to a given prepared SQLite statement.

SQLite metadata is returned in the following form: 

 {
   "columns" :
       [{
           "name"          : <column name>,
           "table"         : <table name>,
           "database"      : <database name>,
           "type"          : <type name>,
           "collation"     : [BINARY|NOCASE|RTRIM],
           "nullable"      : [true|false],
           "primary key"   : [true|false],
           "autoincrement" : [true|false]
       }*]
 }

Parameters

pstmnt as xs:anyURI
the sql command as xs:anyURI from which metadata will be extracted.

Returns

object()
a object() with the associated the metadata.

prepare-statement#2

declare  %an:nondeterministic function s:prepare-statement($conn as xs:anyURI, $stmnt as xs:string) as xs:anyURI external
Compiles a prepared statement based on an already connected SQLite database and a string that defines the sql command.

You can use '?' in the sql command as placeholders so you will be able to bind variables later to such places.

Parameters

conn as xs:anyURI
the SQLite database object as xs:anyURI.
stmnt as xs:string
the sql command as xs:string.

Returns

xs:anyURI
a xs:anyURI object representing the prepared statement.

rollback#1

declare  %an:sequential function s:rollback($conn as xs:anyURI) as xs:anyURI external
Rollbacks all pending update operations in this SQLite database.

Parameters

conn as xs:anyURI
the SQLite database object as xs:anyURI.

Returns

xs:anyURI
the passed SQLite object.

set-boolean#3

declare  %an:sequential function s:set-boolean($pstmnt as xs:anyURI, $param-num as xs:integer, $val as xs:boolean) as empty-sequence() external
Binds a boolean to a placeholder inside a prepared statement.

Parameters

pstmnt as xs:anyURI
the prepared statement already compiled as xs:anyURI.
param-num as xs:integer
the placeholder position to be set.
val as xs:boolean
the boolean to be bind in such placeholder.

Returns

empty-sequence()
nothing.

set-null#2

declare  %an:sequential function s:set-null($pstmnt as xs:anyURI, $param-num as xs:integer) as empty-sequence() external
Set a null to a placeholder inside a prepared statement.

Parameters

pstmnt as xs:anyURI
the prepared statement already compiled as xs:anyURI.
param-num as xs:integer
the placeholder position to be set.

Returns

empty-sequence()
nothing.

set-numeric#3

declare  %an:sequential function s:set-numeric($pstmnt as xs:anyURI, $param-num as xs:integer, $val as xs:anyAtomicType) as empty-sequence() external
Binds a double variable to a placeholder inside a prepared statement.

Parameters

pstmnt as xs:anyURI
the prepared statement already compiled as xs:anyURI.
param-num as xs:integer
the placeholder position to be set.
val as xs:anyAtomicType
a xs:double, xs:integer or xs:decimal to be bind in such placeholder.

Returns

empty-sequence()
nothing.

set-string#3

declare  %an:sequential function s:set-string($pstmnt as xs:anyURI, $param-num as xs:integer, $val as xs:string) as empty-sequence() external
Binds a string variable to a placeholder inside a prepared statement.

Parameters

pstmnt as xs:anyURI
the prepared statement already compiled as xs:anyURI.
param-num as xs:integer
the placeholder position to be set.
val as xs:string
a string to be bind in such placeholder.

Returns

empty-sequence()
nothing.

set-value#3

declare  %an:sequential function s:set-value($pstmnt as xs:anyURI, $param-num as xs:integer, $val as item()) as empty-sequence() external
Binds a value to a placeholder inside a prepared statement using the same type as the item given.

Parameters

pstmnt as xs:anyURI
the prepared statement already compiled as xs:anyURI.
param-num as xs:integer
the placeholder position to be set.
val as item()
the value to be bind in such placeholder.

Returns

empty-sequence()
nothing.