Saxon SQL Extension

[Rumplestiltzkin]

I have some data in a MySQL database that I'd like to query from within an XSL stylesheet. In theory, I should be able to use the Saxon SQL extension, but I can't get it to work. I have a database connection established in <oXygen/> to the database and that works just fine. I'm currently using v10.3 and using the Saxon-B 9.1.0.7 XSLT engine.

I have declared a sql namespace prefix and include it as an extension as follows:

Code: Select all

<xsl:stylesheet ... xmlns:sql="java:/net.sf.saxon.sql.SQLElementFactory" exclude-result-prefixes="#all" extension-element-prefixes="sql" version="2.0">

and have the following in the body of the stylesheet:

Code: Select all

<sql:connect driver="com.mysql.jdbc.Driver" database="jdbc:mysql://mysql-server:3306" user="myuser" password="mypassword">

   <xsl:fallback>

       <xsl:message terminate="yes">Connection to MySQL failed.</xsl:message>

   </xsl:fallback>

</sql:connect>

I also downloaded the latest driver JAR file from MySQL (mysql-connector-java-5.0.8-bin.jar) and put it in the %Oxygen Install Directory%/lib/connectors directory.

Incidentally, the MySQL site says the driver class in this JAR is org.gjt.mm.mysql.Driver, but substituting that in the driver attribute value on the sql:connect makes no difference.

No matter what I do, the fallback always gets executed.

Does the Saxon SQL extension work at all in <oXygen/>? What am I doing wrong?

Stephen

[adrian]

Hello,

Oxygen doesn't bundle the JAR that provides the Saxon SQL extension: saxon9-sql.jar

But you can add it yourself to the <Oxygen-installation-folder>/lib folder and restart Oxygen.

The jar is in the Saxon-B zip archive which can be found here (Saxon-B -> Download for Java):
http://saxon.sourceforge.net/

Regards,
Adrian

PS:
Make sure you specify the correct driver: org.gjt.mm.mysql.Driver

[Rumplestiltzkin]

Cool. Thanks for your help. All is now working as it should be.

Incidentally, is this the only way to get data out of an RDMS in XSL (with <oXygen/>) or is there a better alternative?

Stephen

[adrian]

Hi,

For XSL over a RDMS I believe there aren't other alternatives in Oxygen.

Regards,
Adrian

[Rumplestiltzkin]

So this was working just dandy; then I upgraded to <oXygen/> 11 and it's all broken again.

The documentation on Saxonica indicates that now I have to associate the java net.sf.saxon.options.sql.SQLElementFactory class with a XML namespace - I've chosen the default of http://saxon.sf.net/sql. Being a java bunny, I searched the <oXygen/> documentation for guidance and sadly found none.

Help?

Stephen

[Rumplestiltzkin]

BTW, I have tried adding an extension at the transformation level and adding a saxon cofiguration file (also at the transformation level), but I can't get either of those to work either.

[adrian]

Hello,

Oxygen 11.x bundles Saxon-HE/PE/EE 9.2. So first you will need the saxon9-sql.jar from Saxon-EE 9.2(same place http://saxon.sourceforge.net/).

The second problem is extensions(including SQL) are no longer supported in Saxon-HE(the equivalent of Saxon-B), so you have to use Saxon-PE or EE as the transformer in the scenario.

Then, as you have observed, the sql prefix namespace URI has to be changed

Code: Select all

xmlns:sql="java:/net.sf.saxon.option.sql.SQLElementFactory"

.

Now, to be honest, I wasn't able to make this work in Oxygen 11.1 or even in the command line(you need a separate Saxon-PE/EE license for the latter). It keeps failing in the stylesheet at sql:connect.

I'll let you know if I manage to make this work.

Regards,
Adrian

[Rumplestiltzkin]

I'd looked for separate SQL extension JAR files in both the HE and PE downloads and, not finding any, assumed it was all bundled together now.

As far as getting this to work, the mechanism has changed between Saxon 9.1 and 9.2:

To use the SQL extension elements in a stylesheet, you need to define a namespace prefix (for example "sql") in the extension-element-prefixes attribute of the xsl:stylesheet element, and to map this prefix to a namespace URI, conventionally http://saxon.sf.net/sql. This namespace must be bound in the Configuration to the class net.sf.saxon.options.sql.SQLElementFactory. This binding can be done either by calling Configuration.setExtensionElementNamespace(), or by means of an entry in the configuration file.

I've done the first bit, i.e.

Code: Select all

xmlns:saxon="http://saxon.sf.net/" xmlns:sql="http://saxon.sf.net/sql" extension-element-prefixes="saxon sql"

The second bit (the last sentence) is what I can't work out how to do in an <oXygen/> context.

An alternative might be for me to return to using Saxon B 9.1.0.7. Is this feasible? Would I simply overwrite the saxon9 JAR files or is it (much ) more complicated?

[adrian]

Hello,

I'm sorry for the late reply.

Found the way to make it work with Saxon-PE 9.2:

Create a configuration file, saxonConfig.xml, with this content:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<configuration xmlns="http://saxon.sf.net/ns/configuration" edition="PE">

    <global allowExternalFunctions="true" versionOfXml="1.0"/>

    <xslt>

        <extensionElement namespace="http://net.sf.saxon/sql"

            factory="net.sf.saxon.option.sql.SQLElementFactory"/>

    </xslt>

</configuration>

In the transformation scenario select Saxon-PE and press the Advanced options(cogwheel icon) button. Check the Use configuration file option and browse for the saxonConfig.xml mentioned above.

Now it should work with xmlns:sql="http://saxon.sf.net/sql".

Let me know if this resolved the problem.

Regards,
Adrian

PS:
You can't simply overwrite the saxon jar files from [oxygen-installation-dir]/lib. Actually there's nothing to overwrite since they have different names(saxon9.jar vs saxon9ee.jar). But they will step on each other's toes if they are both in that folder.

On the other hand you can configure Saxon-B 9.1 as a custom XSLT engine in Oxygen(Options -> Preferences -> XML / XSLT-FO-XQuery / Custom Engines). Create a new XSLT engine, choose a name(don't use 'Saxon-B' as the name because Oxygen fallbacks to Saxon-PE automatically), choose as the working directory the folder where you placed the saxon jar and in the command line field write:
java -cp saxon9.jar;saxon9-sql.jar net.sf.saxon.Transform "-o:${out}" ${xmlu} ${xslu}

You will find this custom engine in the Transformer list when editing a transformation scenario.

[mrsweeg]

Hey there, I'm using oXygen 12 and I'm running into problems getting the sql extension to run. I've created the config file you suggested earlier and see that the saxonsql.jar is already bundled (or appears to be bundled) in the lib directory. The sql namespace I'm using is http://saxon.sf.net/sql based on what is defined in the config file. However, when all is said and done and I run the transform, I get sql:connect is not available and then SQL extensions are not installed. Any guideance you can provide would be great. thanks

[adrian]

Hi,

To what SQL server are you trying to connect? Are you using the appropriate driver?

The namespace doesn't really matter but you have to bind it to the appropriate factory:

Code: Select all

<extensionElement namespace="http://saxon.sf.net/sql"

            factory="net.sf.saxon.option.sql.SQLElementFactory"/>

Did you set the config file in the transformation scenario as described in my previous post?

e.g. Sample XSLT stylesheet for connecting to MYSQL

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

      xmlns:xs="http://www.w3.org/2001/XMLSchema"

      exclude-result-prefixes="#all"

      xmlns:sql="http://saxon.sf.net/sql" 

      extension-element-prefixes="sql"

      version="2.0">

    

    <xsl:output method="xml" indent="yes"/>

    

    <xsl:template match="/">

        <xsl:message>Connecting to database...</xsl:message>

        <xsl:variable name="connection" 

            as="java:java.lang.Object" xmlns:java="http://saxon.sf.net/java-type">

            <sql:connect driver="org.gjt.mm.mysql.Driver"

                database="jdbc:mysql://myhost:3306/qa" user="user" password="password">

                <xsl:fallback>

                    <xsl:message terminate="yes">Connection to MySQL failed.</xsl:message>

                </xsl:fallback>

            </sql:connect>

        </xsl:variable>

        

        <xsl:message>Connected...</xsl:message>

        

        <xsl:variable name="mytable-table">

            <sql:query connection="$connection" table="mytable" column="*" row-tag="row" column-tag="col"/> 

        </xsl:variable>

        

        <xsl:message>There are now <xsl:value-of select="count($mytable-table//row)"/> rows in table 'mytable'.</xsl:message>

        <new-book-table>

            <xsl:copy-of select="$mytable-table"/>

        </new-book-table>

        

        <sql:close connection="$connection"/>

    </xsl:template>

</xsl:stylesheet>

Regards,
Adrian

[mrsweeg]

Hey Adrian. Thanks for responding so quickly. I'm trying to connect to a MySQL database and using the com.mysql.jdbc.Driver. I did point the config to the configuration file you posted previously and I also used the xsl you sent to test the connection. In the message I get now that it has connected but then the transform fails with the report "Unknown extension instruction" which appears to be pointing to the <sql:query>.

[mrsweeg]

Adrian, still in a predicament with this.
I've downloaded the saxon9-sql.jar from Saxon-EE 9.2. The mysql connector is mysql-connector-java-5.1.13-bin.jar

I've got this as my namespace decl:

Code: Select all

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

  xmlns:xs="http://www.w3.org/2001/XMLSchema"

  exclude-result-prefixes="#all"

  xmlns:sql="http://saxon.sf.net/sql" 

  extension-element-prefixes="sql"

  version="2.0">

I've got this as my connection variable (obviously the host/database and credentials are filled in):

Code: Select all

<xsl:variable name="connection" 

      as="java:java.lang.Object" xmlns:java="http://saxon.sf.net/java-type"><sql:connect driver="com.mysql.jdbc.Driver"

        database="jdbc:mysql://host/database" user="someuser" password="somepass">        

<xsl:fallback>

          <xsl:message terminate="yes">Connection to MySQL failed.</xsl:message>

        </xsl:fallback>

      </sql:connect>

    </xsl:variable>

And I'm now getting the message that my connection has failed (which at least suggests that the extensions are getting read.)

[sorin_ristache]

Hello,

Try to access the same table from the same MySQL server in the Oxygen Database perspective and see if you get the same error:

1. Create a MySQL connection using the same MySQL URL, user name and password as in the XSLT stylesheet and based on the driver from mysql-connector-java-5.1.13-bin.jar. You create the connection from menu Options -> Preferences -> Data Sources.

2. Go to menu Window -> Open perspective -> Database.

3. Expand the tree rooted at the MySQL connection in the Data Source Explorer view and locate the table that you want to access from the XSLT stylesheet.

4. Right click on the tree node with the table name and select Edit.

If the rows of the table are displayed in the Table Explorer view you should be able to access the same MySQL table from the XSLT stylesheet if you use the same connection details. This worked for me and it should also work for you if you have the saxon9-sql.jar file in the [OXYGEN-INSTALL-FOLDER]/lib folder.

If you can edit the MySQL table in the Database perspective but you cannot access the same table from the XSLT stylesheet please send us your Saxon 9 configuration file and your XSLT stylesheet. We will modify the server URL, user name, password and table name to match our MySQL database and we will try to reproduce the error that you get.


Regards,
Sorin

[mrsweeg]

Thanks Sorin:
I ran the database connect and it all works fine; however I'm still getting errors with the transform.

Here is my config file:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<configuration xmlns="http://saxon.sf.net/ns/configuration" edition="PE">

    <global allowExternalFunctions="true" versionOfXml="1.0"/>

    <xslt>

        <extensionElement namespace="http://saxon.sf.net/sql"

            factory="net.sf.saxon.option.sql.SQLElementFactory"/>

    </xslt>

  </configuration>

And here is the XSLT:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

  xmlns:xs="http://www.w3.org/2001/XMLSchema"

  exclude-result-prefixes="#all"

  xmlns:sql="http://saxon.sf.net/sql" 

  extension-element-prefixes="sql"

  version="2.0">

  

  <xsl:output method="xml" indent="yes"/>

  

  <xsl:template match="/">

    <xsl:message>Connecting to database...</xsl:message>

    <xsl:variable name="connection" 

      as="java:java.lang.Object" xmlns:java="http://saxon.sf.net/java-type">

      <sql:connect driver="com.mysql.jdbc.Driver"

        database="jdbc:mysql://server/database" user="user" password="passwd">

        <xsl:fallback>

          <xsl:message terminate="yes">Connection to MySQL failed.</xsl:message>

        </xsl:fallback>

      </sql:connect>

    </xsl:variable>

    

    <xsl:message>Connected...</xsl:message>

    

    <xsl:variable name="mytable-table">

      <sql:query connection="$connection" table="users" column="*" row-tag="row" column-tag="col"/> 

    </xsl:variable>

    

    <xsl:message>There are now <xsl:value-of select="count($mytable-table//row)"/> rows in table 'mytable'.</xsl:message>

    <new-book-table>

      <xsl:copy-of select="$mytable-table"/>

    </new-book-table>

    

    <sql:close connection="$connection"/>

  </xsl:template>

</xsl:stylesheet>

The error I'm now getting is: JCBC Connection Failure: com.mysql.jdbc.Driver

Thanks for looking into this.

[adrian]

Hi,

You're using the old MySQL driver:
<sql:connect driver="com.mysql.jdbc.Driver"

Use the new one:
<sql:connect driver="org.gjt.mm.mysql.Driver"

Also, if you're still experiencing driver problems you may want to copy the driver jar(mysql-connector-java-5.1.13-bin.jar) to the lib folder from the Oxygen installation folder.

Regards,
Adrian

[mrsweeg]

Excellent, thanks so much to both of you. works like a charm.

[CRB]

I have the just successfully gone through setting up a MySQL connection using the preceeding instructions.

However, I am at a loss as to how to leverage the "limit" command of MySQL to limit the number of results returned. For example:

Code: Select all

SELECT * FROM `social`.`comments` limit 500

I see that Saxon has added an Execute command but am unsure of the syntax:

Code: Select all

<sql:execute statement="{$statement}" />

Appreciate any guidance,

C

[sorin_ristache]

Hello,

sql:execute is used mainly for calling a stored procedure on the database server (not the case of MySQL I think) or for executing a commit (the COMMIT WORK statement) or rollback (the ROLLBACK WORK statement) on the current connection (if the server supports transactions). It does not return a record set from the server. Why don't you use sql:query with an appropriate where attribute that filters out the rows that you do not want to get from the server?


Regards,
Sorin

[mrsweeg]

Quick question, I just downloaded oxygen 17.1 and with the XSLT I've been using for a while to select bits of data from a mysql database, appears that the driver no longer works; I get the error: SXSQ0003: Failed to load JDBC driver org.gjt.mm.mysql.Driver

Using Saxon 9.6.0.7 the xslt I've got to open the connection looks like this:

Code: Select all

<sql:connect driver="org.gjt.mm.mysql.driver" database="somedatabase" user="someuser"

                password="somepassword">

                <xsl:fallback>

                    <xsl:message terminate="yes">Connection to MySQL failed.</xsl:message>

                </xsl:fallback>

            </sql:connect>

I know the credentials are fine because I run the connection from Data Sources and it works fine. Any ideas? Thanks!

[adrian]

Hi,

This was 6 years ago, so not sure if this still works (will have to try later), but note that the driver name (Java class name) is case sensitive and there's a spelling error in yours (the d from driver should be capital D, 'Driver'):

org.gjt.mm.mysql.Driver

Regards,
Adrian

[mrsweeg]

Adrian, thanks for getting back to me. I realize this is from a long ago query but would be great if you could take a look. The "driver" v. "Driver" was actually input by me incorrectly in the post, not in the xslt I have. Just so you have all of the configuration info, I'm using Saxon-PE 9.6.0.7 and added the following configuration file:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<configuration xmlns="http://saxon.sf.net/ns/configuration" edition="PE">

    <global expandAttributeDefaults="false" allowExternalFunctions="true" versionOfXml="1.0"/>

    <xslt>

        <extensionElement namespace="http://saxon.sf.net/sql"

            factory="net.sf.saxon.option.sql.SQLElementFactory"/>

    </xslt>

  </configuration>

thanks!

[mrsweeg]

Adrian,
Nevermind, all set. I was missing the mysql.jar in the lib directory. Duh. Thanks for letting me waste your time!
Matthew

[arminn]

Hi there,

I am new to Oxygen and I was wondering whether someone could outline, where the configuration file needs to be created and where the MySQL connector should reside. So far, I have placed a saxonSqlConfig.xml into /frameworks/xslt/resources/ and the mysql-connector-java-5.1.39-bin.jar is in /lib/connectors/. Do I have to add an extension to my transformation scenario for the XSL stylesheet that tries to connect to the MySQL database?

I am getting an error at the moment that reads: XTDE1450: Unknown extension instruction. I am using Saxon-EE 9.6.0.7. Also I am getting a "sql:connect is not available".

Thanks,

Armin

[adrian]

Hi,

I would not recommend copying custom files to various Oxygen folders. You will eventually forget about them and when you upgrade Oxygen or move to a different machine, your transformations will most likely stop working.
Try to keep your files together with your Oxygen project (.xpr) file. Configure the transformation scenario to use the appropriate editor variables that indicate the project directory (${pd} for project directory path or ${pdu} for project directory URL). Oxygen will usually already help with this when it detects they are in the same folder.

Do I have to add an extension to my transformation scenario for the XSL stylesheet that tries to connect to the MySQL database?

Yes, you must add the mysql-connector-java-*.jar to the Extensions section from the XSL transformation scenario configuration.

"XTDE1450: Unknown extension instruction." usually means there's a mistake in your Saxon config file. Check out this discussion and compare the examples there with your config file.

Regards,
Adrian

[arminn]

Thanks Adrian. Do I add the config file also as an extension to my scenario? I added both, the connector and the config file, but I am still getting the same error.

Regards,

Armin

[adrian]

Hi,

No, the config file needs to be specified int the transformer engine configuration.
See my post from above/same thread.
Here it is for your convenience:

Found the way to make it work with Saxon-PE 9.2:

Create a configuration file, saxonConfig.xml, with this content:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<configuration xmlns="http://saxon.sf.net/ns/configuration" edition="PE">

    <global allowExternalFunctions="true" versionOfXml="1.0"/>

    <xslt>

        <extensionElement namespace="http://net.sf.saxon/sql"

            factory="net.sf.saxon.option.sql.SQLElementFactory"/>

    </xslt>

</configuration>

In the transformation scenario select Saxon-PE and press the Advanced options(cogwheel icon) button. Check the Use configuration file option and browse for the saxonConfig.xml mentioned above.

Now it should work with xmlns:sql="http://saxon.sf.net/sql".

If you want to use Saxon-EE instead of PE as it is described above, you need to select Saxon-EE and also specify in the configuration file @edition="EE".

Regards,
Adrian

[arminn]

Thanks Adrian! Getting a new error with the driver, but you helped me progress quite a bit. I will take it from here

Regards,

Armin

[adrian]

Hi,

There is one aspect that seems to have changed in recent versions of Saxon 9 since that post.
I see in the Saxon documentation that the SQL extension uses a different namespace in the configuration now ( http://saxon.sf.net/sql instead of http://net.sf.saxon/sql ):

Code: Select all

<extensionElement namespace="http://saxon.sf.net/sql" factory="net.sf.saxon.option.sql.SQLElementFactory"/>

Regards,
Adrian

[arminn]

Hi Adrian,

Thanks for your help. After following the thread back and glancing over my template, I can't find anything wrong with it. However, I am getting the following error: SXSQ0003: Failed to load JDBC driver com.mysql.jdbc.Driver. I have the connector jar as an extension and it is located in my project's directory.

Really struggling to get all of this hooked up.

Thanks,

Armin