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="http://www.ibm.com/xsp/core">
<xp:this.resources>
<xp:script src="/ssMysql.jss" clientSide="false" />
</xp:this.resources>
<xp:dataTable id="dataTable1" rows="30"
value="#{javascript:getSQLData();}" var="rs">
<xp:column id="column1">
<xp:this.facets>
<xp:span xp:key="header" style="font-weight:bold">FirstName</xp:span>
</xp:this.facets>
<xp:text escape="true" id="firstname">
<xp:this.value><![CDATA[#{javascript:rs[1]}]]></xp:this.value>
</xp:text>
</xp:column>
<xp:column id="column2">
<xp:this.facets>
<xp:span xp:key="header" style="font-weight:bold">LastName</xp:span>
</xp:this.facets>
<xp:text escape="true" id="lastname">
<xp:this.value><![CDATA[#{javascript:rs[2]}]]></xp:this.value>
</xp:text>
</xp:column>
<xp:column id="column3">
<xp:this.facets>
<xp:span xp:key="header" style="font-weight:bold">
Country
</xp:span>
</xp:this.facets>
<xp:text escape="true" id="computedField1">
<xp:this.value><![CDATA[#{javascript:rs[3]}]]></xp:this.value>
</xp:text>
</xp:column>
<xp:column id="column4">
<xp:this.facets>
<xp:span xp:key="header" style="font-weight:bold">Age</xp:span>
</xp:this.facets>
<xp:text escape="true" id="computedField2">
<xp:this.value><![CDATA[#{javascript:rs[4]}]]></xp:this.value>
</xp:text>
</xp:column>
</xp:dataTable>

</xp:view>

And here is the code for the XPage

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core" xmlns:xc="http://www.ibm.com/xsp/custom">
<xc:ccSQLViewSample SQLQuery="Select * from people">
<xc:this.connection>
<xc:connection port="3306" db="xtest" password="password"
server="localhost" username="root" />
</xc:this.connection>
</xc:ccSQLViewSample>
</xp:view>

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.