XQuery/XQuery SQL Module
Motivation
[edit | edit source]You would like to perform SQL queries from within your XQuery code.
Method
[edit | edit source]eXistdb provides a module for executing SQL queries.
Configuration Steps
[edit | edit source]- Enable the module
- Configure your connection string
- Execute a test query
Enable the SQL Module
[edit | edit source]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
[edit | edit source]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
[edit | edit source]In order to execute the query there are two steps you must take:
- get a connection to the database
- 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() )