http://www.zorba-xquery.com/modules/jdbc

View as XML or JSON.

: This module contains functions to connect to any JDBC datasource : using jvm-util module to handle Java interaction. : : @author Rodolfo Ochoa : @project DB Drivers/JDBC

Function Summary


                                        
                                        affected-rows
                                        ($dataset-id as xs:anyURI) as xs:integer external

: Return the number of affected rows of a particular DataSet.


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

: Clears the current parameter values immediately.


                                        
                                        close-dataset
                                        ($dataset-id as xs:anyURI) as empty-sequence() external

: Closes and free resources from a particular DataSet.


                                        
                                        close-prepared
                                        ($prepared-statement as xs:anyURI) as empty-sequence() external

: Closes and frees from memory any prepared SQL statement created with jdbc:prepare-statement : : @param $prepared-statement The identifier to a prepared statement.


                                        
                                        commit
                                        ($connection-id as xs:anyURI) as empty-sequence() external

: Commit current transaction from an active connection.


                                        
                                        connect
                                        ($connection-config as object()) as xs:anyURI external

: Opens a connection to a database.


                                        
                                        connect
                                        ($connection-config as object(), $options as object()?) as xs:anyURI external

: Opens a connection to a database with specified options.


                                        
                                        connection-options
                                        ($connection-id as xs:anyURI) as object() external

: Returns a set with options for a specified connection.


                                        
                                        execute-prepared
                                        ($prepared-statement as xs:anyURI) as xs:anyURI external

: Executes SQL statements prepared with 5.


                                        
                                        execute-query-prepared
                                        ($prepared-statement as xs:anyURI) as object()* external

: Executes a non-updating SQL statement prepared with 5.


                                        
                                        execute-query
                                        ($connection-id as xs:anyURI, $sql as xs:string) as object()* external

: Executes non-updating SQL statements.


                                        
                                        execute-update-prepared
                                        ($prepared-statement as xs:anyURI) as xs:integer external

: Executes an updating SQL statement prepared with 5.


                                        
                                        execute-update
                                        ($connection-id as xs:anyURI, $sql as xs:string) as xs:integer external

: Executes updating SQL statements.


                                        
                                        execute
                                        ($connection-id as xs:anyURI, $sql as xs:string) as xs:anyURI external

: Executes any kind of SQL statement.


                                        
                                        is-connected
                                        ($connection-id as xs:anyURI) as xs:boolean external

: Verify if a connection is still active.


                                        
                                        metadata
                                        ($dataset-id as xs:anyURI) as object() external

: Return the metadata of the result of a particular DataSet.


                                        
                                        parameter-metadata
                                        ($prepared-statement as xs:anyURI) as object() external

: Retrieves the number, types and properties of the prepared statement parameters.


                                        
                                        prepare-statement
                                        ($connection-id as xs:anyURI, $sql as xs:string) as xs:anyURI external

: Creates a prepared statement for multiple executions with diferent values.


                                        
                                        result-set
                                        ($dataset-id as xs:anyURI) as object()* external

: This function returns a sequence of objects representing the rows of data from a non-updating query.


                                        
                                        rollback
                                        ($connection-id as xs:anyURI) as empty-sequence() external

: Rollback the current transaction of a connection.


                                        
                                        set-boolean
                                        ($prepared-statement as xs:anyURI, $parameter-index as xs:integer, $value as xs:boolean) as empty-sequence() external

: Set the value of the designated parameter with the given value, this function will assign only boolean values.


                                        
                                        set-null
                                        ($prepared-statement as xs:anyURI, $parameter-index as xs:integer) as empty-sequence() external

: Set the value of the designated parameter with the given value, this function will assign only null values if possible.


                                        
                                        set-numeric
                                        ($prepared-statement as xs:anyURI, $parameter-index as xs:decimal, $value as xs:anyAtomicType) as empty-sequence() external

: Set the value of the designated parameter with the given value, this function will assign only numeric values.


                                        
                                        set-string
                                        ($prepared-statement as xs:anyURI, $parameter-index as xs:integer, $value as xs:string) as empty-sequence() external

: Set the value of the designated parameter with the given value, this function will assign only string values.


                                        
                                        set-value
                                        ($prepared-statement as xs:anyURI, $parameter-index as xs:decimal, $value as xs:anyAtomicType) as empty-sequence() external

: Set the value of the designated parameter with the given value, : this function will assign any value you send : and it will try to cast to the correct type.


                                        
                                        tables
                                        ($connection-id as xs:anyURI) as object()*

: : Return the list of tables from a connection : : @param $connection-id The identifier to a connection.


                                        
                                        tables
                                        ($connection-id as xs:anyURI, $catalog as xs:string?, $schema as xs:string?, $table as xs:string?) as object()* external

: Return the list of tables from a connection : : @param $connection-id The identifier to a connection.

Variable Summary

Functions

affected-rows#1

declare  function jdbc:affected-rows($dataset-id as xs:anyURI) as xs:integer external

: Return the number of affected rows of a particular DataSet. : : @param $dataset-id The identifier to a DataSet. : : @error SQL008 DataSet doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Returns an xs:integer with the number of affected rows.

Parameters

dataset-id as xs:anyURI

Returns

xs:integer

clear-params#1

declare  %an:sequential function jdbc:clear-params($prepared-statement as xs:anyURI) as empty-sequence() external

: Clears the current parameter values immediately. : : @param $prepared-statement The identifier to a prepared statement. : : @error SQL003 Prepared statement doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns an empty-sequence()

Parameters

prepared-statement as xs:anyURI

Returns

empty-sequence()

close-dataset#1

declare  %an:sequential function jdbc:close-dataset($dataset-id as xs:anyURI) as empty-sequence() external

: Closes and free resources from a particular DataSet. : : @param $dataset-id The identifier to a DataSet. : : @error SQL008 DataSet doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns an empty-sequence()

Parameters

dataset-id as xs:anyURI

Returns

empty-sequence()

close-prepared#1

declare  %an:sequential function jdbc:close-prepared($prepared-statement as xs:anyURI) as empty-sequence() external

: Closes and frees from memory any prepared SQL statement created with jdbc:prepare-statement : : @param $prepared-statement The identifier to a prepared statement. : : @error SQL003 Prepared statement doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns an empty-sequence()

Parameters

prepared-statement as xs:anyURI

Returns

empty-sequence()

commit#1

declare  %an:sequential function jdbc:commit($connection-id as xs:anyURI) as empty-sequence() external

: Commit current transaction from an active connection. : : @param $connection-id The identifier to the connection to be commited. : : @error SQL08003 Connection doesn't exist : @error SQL08000 Connection is closed : @error SQL001 Descriptive error, see error in attached message : : @return This function returns an empty-sequence()

Parameters

connection-id as xs:anyURI

Returns

empty-sequence()

connect#1

declare  %an:sequential function jdbc:connect($connection-config as object()) as xs:anyURI external

: Opens a connection to a database. : Returns a URI identifying the connection that has been opened. The implementing code determines from the $connection-config either explicitly (interpreting the driver attribute) or implicitly (using the type attribute) which driver it has to load. : : @param $connection-config json object that has the host and user informations. : @option "url" URL of the server, this option must be specified and should be declared according to JDBC specification. : @option "user" username for the server, this is optional. : @option "password" password for the server, this is optional. : : @error SQL28000 Authentication failed. : @error SQL08001 Connection error. : @error SQL40003 Isolation level not supported. : @error SQL001 Descriptive error, see attached message. : : @return Return an identifier that represents the connection to the server. : : Connection coonfiguration example: : { "url" : "jdbc:mysql://localhost/", : "user" : "root", : "password" : "" } :

Parameters

connection-config as object()

Returns

xs:anyURI

connect#2

declare  %an:sequential function jdbc:connect($connection-config as object(), $options as object()?) as xs:anyURI external

: Opens a connection to a database with specified options. : Returns a URI identifying the connection that has been opened. The implementing code determines from the $connection-config either explicitly (interpreting the driver attribute) or implicitly (using the type attribute) which driver it has to load. : : @param $connection-config json object that has the host and user informations. : @option "url" URL of the server, this option must be specified and should be declared according to JDBC specification. : @option "user" username for the server, this option is optional. : @option "password" password for the server, this option is optional. : @param $options json object that specifies the connection options. : @option "autocommit" The created connection will have autocommit turned on if the value the attribute is set to "true". : @option "readonly" The created connection will be readonly if the value of the attribute is set to "true". : @option "isolation-level" The created connection will have the specified transaction isolation level, : the following string typed in-scope variables represent the different transaction isolation levels : that this attribute can be set to: : - $jsql:READ-COMMITTED : - $jsql:READ-UNCOMMITTED : - $jsql:REPEATABLE-READ : - $jsql:SERIALIZABLE : if no isolation level is provided by the user the connection will be created with the default : isolation level of the database. : : : @error SQL28000 Authentication failed. : @error SQL08001 Connection error. : @error SQL40003 Isolation level not supported. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an identifier that represents the connection to the server. : : Connection options example: : { "autocommit" : false, : "readonly"? : true, : "isolation-level"? : $jdbc:READ-COMMITTED } :

Parameters

connection-config as object()

options as object()

Returns

xs:anyURI

connection-options#1

declare  function jdbc:connection-options($connection-id as xs:anyURI) as object() external

: Returns a set with options for a specified connection. : : @param $connection-id The identifier to the connection to be verify. : : @error SQL08003 Connection doesn't exist : @error SQL08000 Connection is closed : @error SQL001 Descriptive error, see error in attached message : : @return Returns and object with the connection options. : The returned options are equal to the options specified in function jdbc:connect. : Consequently, the options are specified as follows: : { "autocommit" : xs:boolean, : "readonly" : xs:boolean, : "isolation-level" : xs:string } :

Parameters

connection-id as xs:anyURI

Returns

object()

execute-prepared#1

declare  %an:sequential function jdbc:execute-prepared($prepared-statement as xs:anyURI) as xs:anyURI external

: Executes SQL statements prepared with 5.1 jsql:prepare-statement with values set : and returns an identifier to a Dataset. : : @param $prepared-statement The identifier to a prepared statement. : : @error SQL003 Prepared statement doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an identifier that represents a DataSet.

Parameters

prepared-statement as xs:anyURI

Returns

xs:anyURI

execute-query-prepared#1

declare  function jdbc:execute-query-prepared($prepared-statement as xs:anyURI) as object()* external

: Executes a non-updating SQL statement prepared with 5.1 jsql:prepare-statement. : : @param $prepared-statement The identifier to a prepared statement. : : @error SQL003 Prepared statement doesn't exist. : @error SQL005 The prepared statement is an updating query. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an object with the result data rows from the query processed with the parameter values provided, : the data rows are defined as follows: : { column:value* }* : Every row is represented by an object of column-value representation of the returned SQL result.

Parameters

prepared-statement as xs:anyURI

Returns

object()*

execute-query#2

declare  function jdbc:execute-query($connection-id as xs:anyURI, $sql as xs:string) as object()* external

: Executes non-updating SQL statements. : : @param $connection-id The identifier to an active connection. : @param $sql The query string to be executed. : : @error SQL08003 Connection doesn't exist. : @error SQL08000 Connection is closed. : @error SQL005 The statement is Updating type. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an object with the result data rows from the query provided, : the data rows are defined as follows: : { column:value* }* : Every row is represented by an object of column-value representation of the returned SQL result. :

Parameters

connection-id as xs:anyURI

sql as xs:string

Returns

object()*

execute-update-prepared#1

declare  function jdbc:execute-update-prepared($prepared-statement as xs:anyURI) as xs:integer external

: Executes an updating SQL statement prepared with 5.1 jsql:prepare-statement. : : @param $prepared-statement The identifier to a prepared statement. : : @error SQL003 Prepared statement doesn't exist. : @error SQL006 The prepared statement is a non-updating query. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Returns an xs:integer with the number of affected rows.

Parameters

prepared-statement as xs:anyURI

Returns

xs:integer

execute-update#2

declare  function jdbc:execute-update($connection-id as xs:anyURI, $sql as xs:string) as xs:integer external

: Executes updating SQL statements. : : @param $connection-id The identifier to an active connection. : @param $sql The query string to be executed. : : @error SQL08003 Connection doesn't exist. : @error SQL08000 Connection is closed. : @error SQL005 The statement is Read-only type. : @error SQL001 Descriptive error, see error in attached message. : : @return Returns an xs:integer with the number of affected rows.

Parameters

connection-id as xs:anyURI

sql as xs:string

Returns

xs:integer

execute#2

declare  %an:sequential function jdbc:execute($connection-id as xs:anyURI, $sql as xs:string) as xs:anyURI external

: Executes any kind of SQL statement. : : @param $connection-id The identifier to an active connection. : @param $sql The query string to be executed. : : @error SQL08003 Connection doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an identifier that represents a DataSet.

Parameters

connection-id as xs:anyURI

sql as xs:string

Returns

xs:anyURI

is-connected#1

declare  function jdbc:is-connected($connection-id as xs:anyURI) as xs:boolean external

: Verify if a connection is still active. : : @param $connection-id The identifier to the connection to be verify. : : @error SQL08003 Connection doesn't exist : @error SQL001 Descriptive error, see error in attached message : : @return Returns true if connected.

Parameters

connection-id as xs:anyURI

Returns

xs:boolean

metadata#1

declare  function jdbc:metadata($dataset-id as xs:anyURI) as object() external

: Return the metadata of the result of a particular DataSet. : : @param $dataset-id The identifier to a DataSet. : : @error SQL008 DataSet doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns the metadata associated with an executed DataSet. More in detail, it returns information about column names, types, and whether a column can contain a null value. : The metadata information can only be returned for DataSets that have been executed explicitly using the jsql:execute function. : : The metadata node returned by this function is defined as follows: : { : "columns": [ { : "name": xs:string, : "type": xs:string, : "autoincrement"? = xs:boolean, : "nillable"? = xs:boolean } * ] : } : @option "name" The name of the column. : @option "type" The SQL type of the column. : @option "autoincrement" is true if this column is automatically maintained. : @option "nillable" If the colums can contain NULL values this attribute will be set to true. : : If the query is an updating query, then the result object will return the number of affected rows like: : { "affectedrows": xs:integer }

Parameters

dataset-id as xs:anyURI

Returns

object()

parameter-metadata#1

declare  function jdbc:parameter-metadata($prepared-statement as xs:anyURI) as object() external

: Retrieves the number, types and properties of the prepared statement parameters. : : @param $prepared-statement The identifier to a prepared statement. : : @error SQL003 Prepared statement doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns the parameters metadata associated with a prepared statement. : In other words, it returns information about the column name associated with the parameter, the type, etc. : The metadata node returned by this function is defined as follows: : { : columns: [{ : "name": xs:string, : "type": xs:string : }] : } : @option "name" The name of the column. : @option "type" The SQL type of the column.

Parameters

prepared-statement as xs:anyURI

Returns

object()

prepare-statement#2

declare  %an:sequential function jdbc:prepare-statement($connection-id as xs:anyURI, $sql as xs:string) as xs:anyURI external

: Creates a prepared statement for multiple executions with diferent values. : : @param $connection-id The identifier to an active connection. : @param $sql The query string to be executed. : : @error SQL08003 Connection doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an identifier that represents the prepared statement. : : Example: : jsql:prepare-statement($connection, "SELECT * FROM users WHERE id=? AND age>?")

Parameters

connection-id as xs:anyURI

sql as xs:string

Returns

xs:anyURI

result-set#1

declare  function jdbc:result-set($dataset-id as xs:anyURI) as object()* external

: This function returns a sequence of objects representing the rows of data from a non-updating query. : : @param $dataset-id The identifier to a DataSet. : : @error SQL008 DataSet doesn't exist. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an object with the result data rows from the DataSet provided, : the data rows are defined as follows: : { column:value* }* : Every row is represented by an object of column-value representation of the returned SQL result.

Parameters

dataset-id as xs:anyURI

Returns

object()*

rollback#1

declare  %an:sequential function jdbc:rollback($connection-id as xs:anyURI) as empty-sequence() external

: Rollback the current transaction of a connection. : : @param $connection-id The identifier to the connection to be rollbacked. : : @error SQL08003 Connection doesn't exist : @error SQL08000 Connection is closed : @error SQL001 Descriptive error, see error in attached message : : @return This function returns an empty-sequence()

Parameters

connection-id as xs:anyURI

Returns

empty-sequence()

set-boolean#3

declare  %an:sequential function jdbc:set-boolean($prepared-statement as xs:anyURI, $parameter-index as xs:integer, $value as xs:boolean) as empty-sequence() external

: Set the value of the designated parameter with the given value, this function will assign only boolean values. : : @param $prepared-statement The identifier to a prepared statement. : @param $parameter-index The index from the parameter to be set. : @param $value The value to be set. : : @error SQL003 Prepared statement doesn't exist. : @error SQL007 Parameter casting error. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns an empty-sequence()

Parameters

prepared-statement as xs:anyURI

parameter-index as xs:integer

value as xs:boolean

Returns

empty-sequence()

set-null#2

declare  %an:sequential function jdbc:set-null($prepared-statement as xs:anyURI, $parameter-index as xs:integer) as empty-sequence() external

: Set the value of the designated parameter with the given value, this function will assign only null values if possible. : : @param $prepared-statement The identifier to a prepared statement. : @param $parameter-index The index from the parameter to be set. : : @error SQL003 Prepared statement doesn't exist. : @error SQL007 Parameter casting error. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns an empty-sequence()

Parameters

prepared-statement as xs:anyURI

parameter-index as xs:integer

Returns

empty-sequence()

set-numeric#3

declare  %an:sequential function jdbc:set-numeric($prepared-statement as xs:anyURI, $parameter-index as xs:decimal, $value as xs:anyAtomicType) as empty-sequence() external

: Set the value of the designated parameter with the given value, this function will assign only numeric values. : : @param $prepared-statement The identifier to a prepared statement. : @param $parameter-index The index from the parameter to be set. : @param $value The value to be set. : : @error SQL003 Prepared statement doesn't exist. : @error SQL007 Parameter casting error. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns an empty-sequence()

Parameters

prepared-statement as xs:anyURI

parameter-index as xs:decimal

value as xs:anyAtomicType

Returns

empty-sequence()

set-string#3

declare  %an:sequential function jdbc:set-string($prepared-statement as xs:anyURI, $parameter-index as xs:integer, $value as xs:string) as empty-sequence() external

: Set the value of the designated parameter with the given value, this function will assign only string values. : : @param $prepared-statement The identifier to a prepared statement. : @param $parameter-index The index from the parameter to be set. : @param $value The value to be set. : : @error SQL003 Prepared statement doesn't exist. : @error SQL007 Parameter casting error. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns an empty-sequence()

Parameters

prepared-statement as xs:anyURI

parameter-index as xs:integer

value as xs:string

Returns

empty-sequence()

set-value#3

declare  %an:sequential function jdbc:set-value($prepared-statement as xs:anyURI, $parameter-index as xs:decimal, $value as xs:anyAtomicType) as empty-sequence() external

: Set the value of the designated parameter with the given value, : this function will assign any value you send : and it will try to cast to the correct type. : : @param $prepared-statement The identifier to a prepared statement. : @param $parameter-index The index from the parameter to be set. : @param $value The value to be set. : : @error SQL003 Prepared statement doesn't exist. : @error SQL007 Parameter casting error. : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return This function returns an empty-sequence()

Parameters

prepared-statement as xs:anyURI

parameter-index as xs:decimal

value as xs:anyAtomicType

Returns

empty-sequence()

tables#1

declare  %an:sequential function jdbc:tables($connection-id as xs:anyURI) as object()*

: : Return the list of tables from a connection : : @param $connection-id The identifier to a connection. : : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an object with the result data rows from the query provided, : the data rows are defined as follows: : { column:value* }* : Every row is represented by an object of column-value representation of the returned SQL result.

Parameters

connection-id as xs:anyURI

Returns

object()*

tables#4

declare  %an:sequential function jdbc:tables($connection-id as xs:anyURI, $catalog as xs:string?, $schema as xs:string?, $table as xs:string?) as object()* external

: Return the list of tables from a connection : : @param $connection-id The identifier to a connection. : @param $catalog A filter of the catalog name of the tables. : Send empty-sequence for all tables. : @param $schema A filter of the schema name of the tables. : Send empty-sequence for all tables. : @param $table A filter of the name of the tables. : Send empty-sequence for all tables. : : @error SQL08000 Connection is closed. : @error SQL001 Descriptive error, see error in attached message. : : @return Return an object with the result data rows from the query provided, : the data rows are defined as follows: : { column:value* }* : Every row is represented by an object of column-value representation of the returned SQL result. :

Parameters

connection-id as xs:anyURI

catalog as xs:string

schema as xs:string

table as xs:string

Returns

object()*

Variables

$jdbc:NOT-SUPPORTED as item()*

: This variable represents the NOT-SUPPORTED level for Isolation Levels in $options for 2.2 connect function.

$jdbc:READ-COMMITTED as item()*

: This variable represents the READ-COMMITTED level for Isolation Levels in $options for 2.2 connect function.

$jdbc:READ-UNCOMMITTED as item()*

: This variable represents the READ-UNCOMMITTED level for Isolation Levels in $options for 2.2 connect function.

$jdbc:REPEATABLE-READ as item()*

: This variable represents the REPEATABLE-READ level for Isolation Levels in $options for 2.2 connect function.

$jdbc:SERIALIZABLE as item()*

: This variable represents the SERIALIZABLE level for Isolation Levels in $options for 2.2 connect function.