Friday, September 28, 2007

Kernow 1.5.2

I've just uploaded the non-beta version of Kernow 1.5.2.

This version contains:

- French and German translations
- XQuery syntax highlighting and checking as-you-type
- Improved cancelling of Single File and Standalone tasks
- icon and splash screen
- An exe to launch it (for windows users)
- context menus
- comboboxes remember their selected index
- individual combobox entries can be removed by deleting the entry
- other small fixes

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.

Monday, September 03, 2007

Kernow 1.5.2 beta b2 available

I've just uploaded a new version of Kernow. This one was pretty much already available via Java Web Start, this makes it available via the normal download route.

New features/fixes:

- Added syntax highlighting and checking as-you-type to the XQuery Sandbox tab. Syntax highlighting's provided using Bounce's XMLEditorKit - I'm hoping to use Netbeans' nbEditorKit in a future version which will add line numbers, code completion etc. I've put together the checking-as-you-type and error highlighting using Saxon's error reporting. This is really cool, so I'm planning on doing an equivalent "XSLT Sandbox" soon... perhaps using Netbeans RCP. Not sure yet.

- Added an icon and splashsceen. These came about because JWS and the exe benefit from them. Are they any good? I'm not really a graphics person...

- Kernow.jar is now a proper executable jar, so you can double click it to run Kernow (if you're on a mac for example)

- It's all compiled using Java 1.5, again for mac users where 1.6 isn't supported yet.

It's available here: Kernow