XQuery/XQuery SQL Module

From Wikibooks, open books for an open world
Jump to: navigation, search

Motivation[edit]

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

Method[edit]

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

Configuration Steps[edit]

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

Enable the SQL Module[edit]

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 Query[edit]

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() )