Using Saxon Extension in XSLT to connect to SQL server NullPointerException

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Using Saxon Extension in XSLT to connect to SQL server NullPointerException

Ogburn, Clarice

Hello,  I’m new to stylesheets and using the saxon processor.  Looking for help on how to use a stylesheet to connect to a SQL server database.   All the details are below. 

 

Scenario: Transforming XML to XML, but some fields I need to do a lookup in an existing database table to populate in the output XML. I'm using SQL Server 2014. I've downloaded the Microsoft JDBC driver version 4.1. The ODBC driver has been setup under Datasources. I'm able to connect to the database locally using my Windows Authentication. When I compile the stylesheet however, a "nullPointerexception" occurs. I'm not sure what I'm doing wrong or if there are additional configuration settings that need to be set on my PC.

Operating System: Windows 7 Saxon Version: 9.5.1.8 TCP/IP has been enabled in the Configuration Tool for SQL Server IDE used to create stylesheet is Stylus Studio.

Below is the code snippet from the stylehsheet

<?xml version='1.0' ?>

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

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

extension-element-prefixes="saxon sql"

 

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

xmlns:java="http://saxon.sf.net/java-type"

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

 

<xsl:variable name="count" select="0"/>

<xsl:variable name="date" select="current-date"/>

<xsl:variable name="time" select="current-time"/>

 

<!-- insert your database details here, or supply them in parameters -->

<xsl:param name="jdbc.driver" as="xs:string" select="string('com.microsoft.sqlserver.jdbc.SQLServerDriver')"/>

<xsl:param name="jdbc.database" as="xs:string" select="string('jdbc:sqlserver://localhost:1433;integratedSecurity=true;databaseName=master')"/> 

 

 

<xsl:template match="/">

 

<xsl:choose>

    <xsl:when test="element-available('sql:connect')">

        <xsl:variable name="sql.conn" as="java:java.sql.Connection">

            <sql:connect driver="{$jdbc.driver}" database="{$jdbc.database}">

            <xsl:fallback>

               <xsl:message terminate="yes"> SQL extensions are not installed</xsl:message>

            </xsl:fallback>

            </sql:connect>

            </xsl:variable>

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

    </xsl:when>

    <xsl:otherwise>

        <xsl:message terminate="yes">sql:connect element is not available</xsl:message>

    </xsl:otherwise>

</xsl:choose>

.......

 

 

 

Result is a "java.lang.NullPointerException"

java.lang.NullPointerException

at net.sf.saxon.expr.Expression.computeDependencies(Expression.java:979)

at net.sf.saxon.expr.Expression.computeStaticProperties(Expression.java:927)

at net.sf.saxon.expr.Expression.getDependencies(Expression.java:351)

at net.sf.saxon.expr.instruct.TraceExpression.getDependencies(TraceExpression.java:352)

at net.sf.saxon.expr.Expression.computeDependencies(Expression.java:979)

at net.sf.saxon.expr.Expression.computeStaticProperties(Expression.java:927)

at net.sf.saxon.expr.Expression.getDependencies(Expression.java:351)

at net.sf.saxon.expr.Expression.computeDependencies(Expression.java:979)

at net.sf.saxon.expr.Expression.computeStaticProperties(Expression.java:927)

at net.sf.saxon.expr.Expression.getCardinality(Expression.java:312)

at net.sf.saxon.style.SourceBinding.getInferredType(SourceBinding.java:484)

at net.sf.saxon.style.XSLLocalVariable.getRequiredType(XSLLocalVariable.java:61)

at net.sf.saxon.style.StyleElement.compileSequenceConstructor(StyleElement.java:1607)

at net.sf.saxon.style.XSLChoose.compile(XSLChoose.java:121)

at net.sf.saxon.style.StyleElement.compileSequenceConstructor(StyleElement.java:1634)

at net.sf.saxon.style.XSLTemplate.compileDeclaration(XSLTemplate.java:325)

at net.sf.saxon.style.PrincipalStylesheetModule.compileStylesheet(PrincipalStylesheetModule.java:773)

at net.sf.saxon.PreparedStylesheet.setStylesheetDocument(PreparedStylesheet.java:393)

at net.sf.saxon.PreparedStylesheet.prepare(PreparedStylesheet.java:236)

at net.sf.saxon.TransformerFactoryImpl.newTemplates(TransformerFactoryImpl.java:142)

at net.sf.saxon.TransformerFactoryImpl.newTransformer(TransformerFactoryImpl.java:97)

at com.exln.stylus.CSaxon8Driver.doProcessing(CSaxon8Driver.java:224)

at com.exln.stylus.CProcessorDriver.process(CProcessorDriver.java:116)

java.lang.RuntimeException

 


------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351&iu=/4140
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help 
Reply | Threaded
Open this post in threaded view
|

Re: Using Saxon Extension in XSLT to connect to SQL server NullPointerException

Michael Kay
It's crashing internally here, so that's a Saxon bug, but it may be related to the way Stylus Studio is configuring Saxon. We'll need to take a look at it.

See if the problem goes away when you change 

<xsl:variable name="sql.conn" as="java:java.sql.Connection">

to

<xsl:variable name="sql.conn" as="item()">

Michael Kay
Saxonica



------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351&iu=/4140
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help 
Reply | Threaded
Open this post in threaded view
|

Re: Using Saxon Extension in XSLT to connect to SQL server NullPointerException

Ogburn, Clarice
In reply to this post by Ogburn, Clarice

I’ve  updated the stylesheet with the following :

 

<xsl:variable name="sql.conn" as="item()">

 

The nullPointer exception is still occurring.  Copying the stack trace again, but I don’t think there is a change.

 

java.lang.NullPointerException

      at net.sf.saxon.expr.Expression.computeDependencies(Expression.java:979)

      at net.sf.saxon.expr.Expression.computeStaticProperties(Expression.java:927)

      at net.sf.saxon.expr.Expression.getDependencies(Expression.java:351)

      at net.sf.saxon.expr.instruct.TraceExpression.getDependencies(TraceExpression.java:352)

      at net.sf.saxon.expr.Expression.computeDependencies(Expression.java:979)

      at net.sf.saxon.expr.Expression.computeStaticProperties(Expression.java:927)

      at net.sf.saxon.expr.Expression.getCardinality(Expression.java:312)

      at net.sf.saxon.style.SourceBinding.getInferredType(SourceBinding.java:484)

      at net.sf.saxon.style.XSLLocalVariable.getRequiredType(XSLLocalVariable.java:61)

      at net.sf.saxon.style.StyleElement.compileSequenceConstructor(StyleElement.java:1607)

      at net.sf.saxon.style.XSLChoose.compile(XSLChoose.java:121)

      at net.sf.saxon.style.StyleElement.compileSequenceConstructor(StyleElement.java:1634)

      at net.sf.saxon.style.XSLTemplate.compileDeclaration(XSLTemplate.java:325)

      at net.sf.saxon.style.PrincipalStylesheetModule.compileStylesheet(PrincipalStylesheetModule.java:773)

      at net.sf.saxon.PreparedStylesheet.setStylesheetDocument(PreparedStylesheet.java:393)

      at net.sf.saxon.PreparedStylesheet.prepare(PreparedStylesheet.java:236)

      at net.sf.saxon.TransformerFactoryImpl.newTemplates(TransformerFactoryImpl.java:142)

      at net.sf.saxon.TransformerFactoryImpl.newTransformer(TransformerFactoryImpl.java:97)

      at com.exln.stylus.CSaxon8Driver.doProcessing(CSaxon8Driver.java:224)

      at com.exln.stylus.CProcessorDriver.process(CProcessorDriver.java:116)

java.lang.RuntimeException

 

Thanks for your help!!

   

From: Ogburn, Clarice
Sent: Friday, March 25, 2016 2:54 PM
To: '[hidden email]'
Subject: Using Saxon Extension in XSLT to connect to SQL server NullPointerException

 

Hello,  I’m new to stylesheets and using the saxon processor.  Looking for help on how to use a stylesheet to connect to a SQL server database.   All the details are below. 

 

Scenario: Transforming XML to XML, but some fields I need to do a lookup in an existing database table to populate in the output XML. I'm using SQL Server 2014. I've downloaded the Microsoft JDBC driver version 4.1. The ODBC driver has been setup under Datasources. I'm able to connect to the database locally using my Windows Authentication. When I compile the stylesheet however, a "nullPointerexception" occurs. I'm not sure what I'm doing wrong or if there are additional configuration settings that need to be set on my PC.

Operating System: Windows 7 Saxon Version: 9.5.1.8 TCP/IP has been enabled in the Configuration Tool for SQL Server IDE used to create stylesheet is Stylus Studio.

Below is the code snippet from the stylehsheet

<?xml version='1.0' ?>

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

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

extension-element-prefixes="saxon sql"

 

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

xmlns:java="http://saxon.sf.net/java-type"

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

 

<xsl:variable name="count" select="0"/>

<xsl:variable name="date" select="current-date"/>

<xsl:variable name="time" select="current-time"/>

 

<!-- insert your database details here, or supply them in parameters -->

<xsl:param name="jdbc.driver" as="xs:string" select="string('com.microsoft.sqlserver.jdbc.SQLServerDriver')"/>

<xsl:param name="jdbc.database" as="xs:string" select="string('jdbc:sqlserver://localhost:1433;integratedSecurity=true;databaseName=master')"/> 

 

 

<xsl:template match="/">

 

<xsl:choose>

    <xsl:when test="element-available('sql:connect')">

        <xsl:variable name="sql.conn" as="java:java.sql.Connection">

            <sql:connect driver="{$jdbc.driver}" database="{$jdbc.database}">

            <xsl:fallback>

               <xsl:message terminate="yes"> SQL extensions are not installed</xsl:message>

            </xsl:fallback>

            </sql:connect>

            </xsl:variable>

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

    </xsl:when>

    <xsl:otherwise>

        <xsl:message terminate="yes">sql:connect element is not available</xsl:message>

    </xsl:otherwise>

</xsl:choose>

.......

 

 

 

Result is a "java.lang.NullPointerException"

java.lang.NullPointerException

at net.sf.saxon.expr.Expression.computeDependencies(Expression.java:979)

at net.sf.saxon.expr.Expression.computeStaticProperties(Expression.java:927)

at net.sf.saxon.expr.Expression.getDependencies(Expression.java:351)

at net.sf.saxon.expr.instruct.TraceExpression.getDependencies(TraceExpression.java:352)

at net.sf.saxon.expr.Expression.computeDependencies(Expression.java:979)

at net.sf.saxon.expr.Expression.computeStaticProperties(Expression.java:927)

at net.sf.saxon.expr.Expression.getDependencies(Expression.java:351)

at net.sf.saxon.expr.Expression.computeDependencies(Expression.java:979)

at net.sf.saxon.expr.Expression.computeStaticProperties(Expression.java:927)

at net.sf.saxon.expr.Expression.getCardinality(Expression.java:312)

at net.sf.saxon.style.SourceBinding.getInferredType(SourceBinding.java:484)

at net.sf.saxon.style.XSLLocalVariable.getRequiredType(XSLLocalVariable.java:61)

at net.sf.saxon.style.StyleElement.compileSequenceConstructor(StyleElement.java:1607)

at net.sf.saxon.style.XSLChoose.compile(XSLChoose.java:121)

at net.sf.saxon.style.StyleElement.compileSequenceConstructor(StyleElement.java:1634)

at net.sf.saxon.style.XSLTemplate.compileDeclaration(XSLTemplate.java:325)

at net.sf.saxon.style.PrincipalStylesheetModule.compileStylesheet(PrincipalStylesheetModule.java:773)

at net.sf.saxon.PreparedStylesheet.setStylesheetDocument(PreparedStylesheet.java:393)

at net.sf.saxon.PreparedStylesheet.prepare(PreparedStylesheet.java:236)

at net.sf.saxon.TransformerFactoryImpl.newTemplates(TransformerFactoryImpl.java:142)

at net.sf.saxon.TransformerFactoryImpl.newTransformer(TransformerFactoryImpl.java:97)

at com.exln.stylus.CSaxon8Driver.doProcessing(CSaxon8Driver.java:224)

at com.exln.stylus.CProcessorDriver.process(CProcessorDriver.java:116)

java.lang.RuntimeException

 


------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help 
Reply | Threaded
Open this post in threaded view
|

Re: Using Saxon Extension in XSLT to connect to SQL server NullPointerException

Ogburn, Clarice
In reply to this post by Ogburn, Clarice

I’ve  updated the stylesheet with the following :

 

<xsl:variable name="sql.conn" as="item()">

 

The NullPointerException still occurs, the stack trace doesn’t seem to have changed.

 

 

Thanks,

 

Clarice Ogburn


------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help 
Reply | Threaded
Open this post in threaded view
|

Re: Using Saxon Extension in XSLT to connect to SQL server NullPointerException

Michael Kay
Is there any particular reason you are using Saxon 9.5.1.8? Could you try it on 9.7.0.4?

I've made the minimum changes needed to run your code on my environment (that is, without Microsoft SQL Server) and it's working fine for me.

Michael Kay
Saxonica

On 31 Mar 2016, at 19:16, Ogburn, Clarice <[hidden email]> wrote:

I’ve  updated the stylesheet with the following :

 

<xsl:variable name="sql.conn" as="item()">

 

The NullPointerException still occurs, the stack trace doesn’t seem to have changed.

 

 

Thanks,

 

Clarice Ogburn

------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help


------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help 
Reply | Threaded
Open this post in threaded view
|

Re: Using Saxon Extension in XSLT to connect to SQL server NullPointerException

Michael Kay
In reply to this post by Ogburn, Clarice
I've now managed to reproduce the problem on Saxon 9.5.1.10, and it seems you can work around it by

(a) setting error-code="sql:XXXX9999" on the two xsl:message instructions

(b) setting version="3.0" on the xsl:stylesheet element.

I haven't yet managed to work out the exact circumstances that trigger the error.

Michael Kay
Saxonica


On 31 Mar 2016, at 19:16, Ogburn, Clarice <[hidden email]> wrote:

I’ve  updated the stylesheet with the following :

 

<xsl:variable name="sql.conn" as="item()">

 

The NullPointerException still occurs, the stack trace doesn’t seem to have changed.

 

 

Thanks,

 

Clarice Ogburn

------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help


------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help 
Reply | Threaded
Open this post in threaded view
|

Re: Using Saxon Extension in XSLT to connect to SQL server NullPointerException

Ogburn, Clarice
In reply to this post by Ogburn, Clarice

Micheal,

 

The saxon 9.5.1.8 version is integrated within Stylus Studio.  In order to use the other version, I would need to download the trial version.  I have requested a 30-day evaluation.  I had one from a while back but that temp license as expired.  I’ve generated the java code to run this transformation and will set it up in Eclipse instead of running it in Stylus Studio and see if that makes a difference also.

 

I really appreciate your help with this issue. 

 

 

Thanks,

 

Clarice Ogburn

 

 

 

 

 

 

 


------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help 
Reply | Threaded
Open this post in threaded view
|

Re: Using Saxon Extension in XSLT to connect to SQL server NullPointerException

Michael Kay
OK, thanks. I know that Stylus have started work on integrating 9.7, but I don't know how long it will take them; it's not a trivial task because integrating a debugger involves a lot of use of internal interfaces.

Michael Kay
Saxonica


On 1 Apr 2016, at 19:47, Ogburn, Clarice <[hidden email]> wrote:

Micheal,

 

The saxon 9.5.1.8 version is integrated within Stylus Studio.  In order to use the other version, I would need to download the trial version.  I have requested a 30-day evaluation.  I had one from a while back but that temp license as expired.  I’ve generated the java code to run this transformation and will set it up in Eclipse instead of running it in Stylus Studio and see if that makes a difference also.

 

I really appreciate your help with this issue. 

 

 

Thanks,

 

Clarice Ogburn

 

 

 

 

 

 

 

------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help


------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
[hidden email]
https://lists.sourceforge.net/lists/listinfo/saxon-help