XQuery/XQuery SQL Module

Motivation

You would like to perform SQL queries from within your XQuery code.

Method

eXistdb provides a module for executing SQL queries.

Configuration Steps

1. Enable the module
2. Configure your connection string
3. Execute a test query

Enable the SQL Module

Your fist step is to enable the SQL Module. To do this you must uncomment the following lines from the conf.xml file in your EXIST_HOME directory:

<module class="org.exist.xquery.modules.sql.SQLModule"
   uri="http://exist-db.org/xquery/sql" />

In eXist 1.5 there is also an additional Oracle module that is undocumented.

<module class="org.exist.xquery.modules.oracle.OracleModule"
     uri="http://exist-db.org/xquery/oracle" />

Install the Driver library

The SQL Module uses JDBC for its database connectivity and as such for each database type that you wish to connect to, a JDBC Driver is required. JDBC Drivers should be placed in EXIST_HOME/lib/user.

After this is done you must restart your server. You should now see the additional SQL Module documentation in your function list and the Driver class(es) used in the connection function calls should be found.

Execute the Query

In order to execute the query there are two steps you must take:

1. get a connection to the database
2. execute the query

There are five different functions to get a connection to the database but only one function to execute the query.

The connection string allows you to connect to the correct server with the appropriate username and password.

In its most basic form, the format of the get-connection function is the following:

  sql:get-connection('JavaClass', 'JDBC-Connection-URL')

This format assumes you can put the login and password to the database directly in the JDBC connection URL.

If you can not do this, the format of the connection string with a username and password is:

  sql:get-connection('JavaClass', 'JDBC-Connection-URL', 'username', 'password')

Note that some systems also put the username and password in the JDBC connection string.

For example in MySQL the string might be:

  sql:get-connection("com.mysql.jdbc.Driver", 'jdbc:mysql://localhost/db1', 'mysql-user-name', 'mysql-password')

In Oracle the string might be

  sql:get-connection('oracle.jdbc.OracleDriver', 'jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE", 'jdbc-connection-string', 'mysql-user-name', 'mysql-password')

let $connection := sql:get-connection("com.mysql.jdbc.Driver", 'jdbc:mysql://localhost/db1', 'mysql-user-name', 'mysql-password')
let $q1 := "select * from table1"
return sql:execute( $connection, $q1, fn:true() )

References

eXistdb Documentation