XQuery/XQuery SQL Module

MotivationEdit

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

MethodEdit

The eXist system provides a standards module for executing SQL queries.

Configuration StepsEdit

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

Enable the SQL ModuleEdit

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" />

After this is done you must restart your server. You should now see the additional SQLModule documentation in your function list.

Execute the QueryEdit

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() )
Last modified on 14 April 2011, at 16:42