Advanced-01:Querying SQL Databases from XQuery (SQLModule)

This howto explains how you may query or update SQL databases from XQuery returning the results as XML nodesets. This tutorial makes use of the sql extension module for eXist; this is available in eXist from 2006-09-25.

1.1 eXist Configuration

Firstly you need to configure eXist to load the additional module, you will need to add the following to the xquery/builtin-modules node of conf.xml (which can be found in EXIST_HOME -
view sourceprint?
1.

NB - eXist will need to be restarted for this change to take effect.

1.2 JDBC Drivers

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.

1.3 The SQL module

The SQL module provides two main functions - get-connection() and execute().
1.3.1 get-connection()

Used for opening a connection to the database. The connection persists for the lifetime of the executing query. There are two implementations -

view sourceprint?

1.get-connection($jdbcClass, $jdbcConnection)

2.get-connection($jdbcClass, $jdbcConnection, $dbUser, $dbPassword)

jdbcClass is the JDBC Driver Class, e.g. for MySQL this would be "com.mysql.jdbc.Driver"

jdbcConnection is the JDBC Connection String. e.g. for a MySQL server running on the local machine with a database called "pies" this would be - "jdbc:mysql://localhost/pies"

dbUser is the database/schema user's username

dbPassword is the password for the database/schema user

The get-connection() function returns an xs:long which is the id of the open database connection.

1.3.2 execute()

Executes either a query or update against the database. The implementation is -
view sourceprint?
1.execute($connection, $sql, $useColumnNames)

connection is the connection id obtained from get-connection()

sql is the SQL statement to execute

useColumnNames is an xs:boolean value indicating whether the resultant XML should use the Column Names as the node names.

The execute() function returns a Node representing the SQL results. If the SQL query was an update then an update count is returned.

1.4 Example: Querying a SQL Database

xquery version "1.0";
declare namespace sql="http://exist-db.org/xquery/sql";
let $connection := sql:get-connection("com.mysql.jdbc.Driver", "jdbc:mysql://localhost /pies", "root", "")
return
sql:execute($connection, "select * from pieFillings;", fn:true())

1.5 Example: Updating a SQL Database

view sourceprint?
1.xquery version "1.0";

2.declare namespace sql="http://exist-db.org/xquery/sql";

3.let $connection := sql:get-connection("com.mysql.jdbc.Driver", "jdbc:mysql://localhost /pies", "root", "") return

4.sql:execute($connection, "insert into pieFillings (filling, cost) values ('apple', 1.0);", fn:false())

Notes(I collect it from comments)

-> This points used to run simple sql query in eXist db..

Copy the jdbc driver e.g mysql-connector-java-3.1.14-bin.jar into _HOME/lib/user and restart the server.

Copy and paste the 1.4 example code into a text file (e.g. sqltest.xql) and save it in a directory beneath exist. I use _HOME/webapp/xquery/tests

Change the database connection settings and SQL query as necessary

Point your browser to (in my case) http://localhost:8080/exist/xquery/tests/sqltest.xql

And you should get the data back. View source and you will the XML tags.