Using MySQL Data in XPages

In todays world, data is not stored in a single place but in different systems and on different platforms. On possibility is a SQL database. Assume, you want to access this data and use it in your XPages application.

I have created a new project and contributed the code on OpenNTF.

The javascript lib contains a single function “getSQLData()” that establishes the connection and retrieves the data acording to the connection parameters and SQL statement you provide.

The resultset can then be used in a XPages DataTable control. Simply bind the control to the “getSQLData()” function. This also works in a repeat control or a combo box.

Use “Select * from people” to retrieve all columns in the table; you can also use “Select FIRSTNAME, LASTNAME from people” to return only specific values.

Here is some sample code for custom control containing a DataTable using the result from an SQL statement as source.

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="">
<xp:script src="/ssMysql.jss" clientSide="false" />
<xp:dataTable id="dataTable1" rows="30"
value="#{javascript:getSQLData();}" var="rs">
<xp:column id="column1">
<xp:span xp:key="header" style="font-weight:bold">FirstName</xp:span>
<xp:text escape="true" id="firstname">
<xp:column id="column2">
<xp:span xp:key="header" style="font-weight:bold">LastName</xp:span>
<xp:text escape="true" id="lastname">
<xp:column id="column3">
<xp:span xp:key="header" style="font-weight:bold">
<xp:text escape="true" id="computedField1">
<xp:column id="column4">
<xp:span xp:key="header" style="font-weight:bold">Age</xp:span>
<xp:text escape="true" id="computedField2">


And here is the code for the XPage

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="" xmlns:xc="">
<xc:ccSQLViewSample SQLQuery="Select * from people">
<xc:connection port="3306" db="xtest" password="password"
server="localhost" username="root" />

And finally here is the output in the browser

If you want to access data from a DB2 datasource instead of MySQL, simply use the appropriate driver.

4 thoughts on “Using MySQL Data in XPages

  1. Hello,

    Very useful! Some comments:

    1. I have run into out of memory issues in the past when attaching relatively big jar files within my NSF directly – especially when called from client-side agents. I have found that putting jar resources in ‘X:\Program Files\IBM\Lotus\Domino\jvm\lib\ext’ (for browser clients) solved those issues.
    2. Performance – you should consider using a connection pooling mechanism. I am using hibernate. Creating a brand new connection per request is very expensive!

    @Vince: instead of native Java servlets you may want to look at the following:
    1. (This is a new feature in 8.5.3)

  2. This is great stuff, had no problems with it, except it is not yet running on my dev server. It does not seem to return a resultset, I get a blank Xpage form.

    …other than that localhost version is smooth-sailing. The project is well-documented for trials, good going on that! Can’t wait to see more…


Comments are closed.