oracle driver in xslt and no admin

[buberle]

Dear all,

i need to connect to a oracle database and want to use xslt with saxon. i wrote an extension configuration already, but saxon cannot find an appropriate driver.

Usually, i would copy my oracle-driver-jar into the oxygen's installation dir. But i have no admin rights on this machine, so this wouldn't work. (but this is the correct way, right?)

Can I use the installDir/lib/oracleConnector.jar ? or can i set an additional classpath in my user space?

Thanks

[adrian]

Hi,

installDir/lib/oracleConnector.jar is an Oxygen specific connector for the Oracle driver, but it still requires the actual Oracle JDBC driver. So this connector won't be of any use to you.

You can find an Oracle JDBC driver here:
JDBC Driver Downloads

I found an article by Andrew Welch that can get you started on the XSLT side:
Connecting to Oracle from XSLT

BTW, you don't have to copy the driver into the Oxygen installation folder, you can simply add the jar libraries to the Extensions section from the transformation scenario editing dialog.

Regards,
Adrian

[buberle]

Thanks Adrian.

I found an article by Andrew Welch that can get you started on the XSLT side:
Connecting to Oracle from XSLT

Unfortunately, Andrew's article is misleading, because the parameters have changed meanwhile, see http://www.saxonica.com/documentation/s ... /intro.xml

So, my files are

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>

and

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="xs" version="2.0" xmlns:sql="http://saxon.sf.net/sql"

    extension-element-prefixes="sql">

    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>

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

    <xsl:param name="database" select="'jdbc:oracle:thin:@1.2.3.4:1521/mysid'"/>

    <xsl:param name="user" select="'USERNAME'"/>

    <xsl:param name="password" select="'PASSWORD'"/>



    <xsl:template match="/" name="main">

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

        <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:extension-element-prefixes="sql">

                <xsl:fallback>

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

                </xsl:fallback>

            </sql:connect>

        </xsl:variable>

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

            <!-- Let's skip queries here for brevity -->

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

    </xsl:template>

</xsl:stylesheet>

Strange enough, I get the following contradictional results:

• 1. I get an 'Unknown extension instruction' error; the transformation fails in general
  2. The error jumps to <xsl:message>Connecting to database ...
  3. the fallback is never called, even if i give wrong credentials
  4. The <xsl:message>Connected ... is shown.

Has anybody any idea?

[adrian]

Hi,

I see now that the article refers to Saxon 8 which had a slightly different SQL extension.

Try to comment the rest of the XSLT code to debug the scenario until you get one part of it working (sql:connect). If you don't, I see that Saxon SQL behaves very strange when it fails to connect and then reaches sql:close.

Regards,
Adrian