Wednesday, September 12, 2007

Connecting to Oracle from XSLT

Today I generated a report by connecting directly to an Oracle database from XSLT, and thought I'd share the basic stylesheet. I used Saxon's SQL extension, which is available when saxon8-sql.jar is on the classpath. As I was connecting to Oracle, I also needed to put ojdcb14.jar on the classpath.

Here's the stylesheet in it's most basic form, formatted for display in this blog.

The important things to note here are:

- The sql prefix is bound to "/net.sf.saxon.sql.SQLElementFactory"
- The driver is "oracle.jdbc.driver.OracleDriver"
- The connection string format is "jdbc:oracle:thin:@1.2.3.4:1234:sid" (note the colon between thin and @ - I missed that first time round) where the IP, port and sid are placeholders for the real values
- remember that saxon8-sql.jar and ojdbc14.jar needed to be on the classpath

<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="/net.sf.saxon.sql.SQLElementFactory"
exclude-result-prefixes="xs"
extension-element-prefixes="sql">

<xsl:output indent="yes"/>

<xsl:param name="driver"
select="'oracle.jdbc.driver.OracleDriver'"
as="xs:string"/>

<xsl:param name="database"
select="'jdbc:oracle:thin:@123.123.123.123:1234:sid'"
as="xs:string"/>

<xsl:param name="user" select="'un'" as="xs:string"/>
<xsl:param name="password" select="'pw'" as="xs:string"/>

<xsl:variable name="connection"
as="java:java.sql.Connection"
xmlns:java="http://saxon.sf.net/java-type">

<sql:connect driver="{$driver}" database="{$database}"
user="{$user}" password="{$password}"/>
</xsl:variable>

<xsl:template match="/" name="main">
<root>
<sql:query connection="$connection"
table="some_table"
column="*"
row-tag="row"
column-tag="col"/>
</root>
</xsl:template>

</xsl:stylesheet>

The result of this transform outputs XML in the form:

<root>
<row>
<col>data1</col>
<col>data2</col>
<col>data3</col>
<col>data4</col>
</row>
....
</root>

where <root> is the wrapper element, and <row> and <col> are the element names specified in the <sql:query> element.

And that's it - connecting to an Oracle database from within XSLT.

3 comments:

siva512 said...

Hello!
I am unable to login using this.plz help out me i am trying from long time.
getting below error message:

SXSQ0003: JDBC Connection Failure: oracle.jdbc.driver.OracleDriver

et.sf.saxon.trans.DynamicError: JDBC Connection Failure: oracle.jdbc.driver.OracleDriver
at net.sf.saxon.expr.Expression.dynamicError(Expression.java:780)
at net.sf.saxon.sql.SQLConnect$ConnectInstruction.evaluateItem(SQLConnect.java:131)
at net.sf.saxon.expr.SimpleExpression.iterate(SimpleExpression.java:227)
at net.sf.saxon.expr.Atomizer.iterate(Atomizer.java:96)
at net.sf.saxon.expr.UntypedAtomicConverter.iterate(UntypedAtomicConverter.java:130)
at net.sf.saxon.expr.ItemChecker.iterate(ItemChecker.java:125)

RameshBlog said...

I am also facing the same problem. can any one help us.

Andrew Welch said...

I would recommend joining the Saxon mailing list and asking on there:

https://lists.sourceforge.net/lists/listinfo/saxon-help

For general XSLT issue, join the "xsl-list" (google it)