atom feed21 messages in net.sourceforge.lists.saxon-helpRe: [saxon] SQL extension insert conv...
FromSent OnAttachments
Norman WalshMay 19, 2008 1:29 am 
Michael KayMay 19, 2008 1:57 pm 
Norman WalshMay 20, 2008 10:09 am 
Michael KayMay 20, 2008 10:51 am 
Michael KayMay 20, 2008 11:06 am 
Houghton,AndrewMay 20, 2008 12:00 pm 
Michael KayMay 20, 2008 1:42 pm 
Houghton,AndrewMay 20, 2008 3:55 pm 
Michael KayMay 20, 2008 4:10 pm 
Michael KayMay 21, 2008 2:34 am 
Houghton,AndrewMay 21, 2008 7:06 am 
Michael KayMay 21, 2008 7:22 am 
Norman WalshMay 22, 2008 9:10 am 
Houghton,AndrewMay 23, 2008 8:07 am 
Michael KayMay 23, 2008 10:48 am 
Houghton,AndrewMay 23, 2008 12:13 pm 
Michael KayMay 24, 2008 2:33 am 
Michael KayMay 24, 2008 6:44 am 
Norman WalshJun 3, 2008 6:57 pm 
Michael KayJun 4, 2008 12:14 am 
Norman WalshJul 16, 2008 5:52 am 
Subject:Re: [saxon] SQL extension insert conversion error
From:Michael Kay (mi@saxonica.com)
Date:May 24, 2008 6:44:14 am
List:net.sourceforge.lists.saxon-help

This is now logged as bug 1971291 and a patch is in Subversion; I have also added a test case (sql002).

-----Original Message----- From: saxo@lists.sourceforge.net [mailto:saxo@lists.sourceforge.net] On Behalf Of Houghton,Andrew Sent: 23 May 2008 20:13 To: Mailing list for the SAXON XSLT and XQuery processor Subject: Re: [saxon] SQL extension insert conversion error

Changing the XSL to:

<xsl:param name="sql.hack" as="xsd:string" select="string('')" />

<sql:column name="idPT" select="concat($idPT,$sql.hack)" /> <sql:column name="idBT" select="concat($idBT,$sql.hack)" />

still yields the same problem. I sent the files to your saxonica.com e-mail address.

Thanks, Andy.

-----Original Message----- From: saxo@lists.sourceforge.net [mailto:saxon-help- boun@lists.sourceforge.net] On Behalf Of Michael Kay Sent: Friday, May 23, 2008 1:49 PM To: 'Mailing list for the SAXON XSLT and XQuery processor' Subject: Re: [saxon] SQL extension insert conversion error

This looks like a Saxon bug, caused by an some kind of internal optimization rewrite of the query. I've got a good idea what has probably happened, but if you can send me the full code that will help me to confirm my suspicions.

A workaround is probably to use anything other than a simple variable reference in the select attribute of sql:column; for example use concat($idPT, $param) where $param is a parameter initialized to a zero-length string.

Michael Kay http://www.saxonica.com/

-----Original Message----- From: saxo@lists.sourceforge.net [mailto:saxo@lists.sourceforge.net] On Behalf Of Houghton,Andrew Sent: 23 May 2008 16:08 To: Mailing list for the SAXON XSLT and XQuery processor Subject: [saxon] SQL extension insert conversion error

I'm using the Saxon SQL extensions to insert some data into a database. The insert for the first record fails with:

java.lang.ClassCastException: net.sf.saxon.expr.LocalVariableReference cannot be cast to net.sf.saxon.sql.SQLColumn$ColumnInstruction

I don't quite understand why it has a casting issue. The SQL insert looks like:

<sql:insert connection="$sql.conn" table="dbo.ConceptBT"> <sql:column name="idPT" select="$idPT" /> <sql:column name="idBT" select="$idBT" /> </sql:insert>

with the variables declared as:

<xsl:variable name="idPT" as="xsd:string?" select="..." /> <xsl:variable name="idBT" as="xsd:string?" select="..." />

I have used "..." in the select attribute to simplify things, but valid nodes or local XPath functions are being called to generate string values. I'm printing out the values with xsl:message and none of the values are a null string. In addition, $idPT is not equal to $idBT, per check constraint in the database. The dbo.ConceptPT table was loaded successfully. Both the $idPT and $idBT values are found as an id in the dbo.ConceptPT table and I can successfully go into the database and manually add a row into the dbo.ConceptBT table with the values for $idPT and $idBT. So I think the database is setup correctly and working fine.

The database tables are setup as:

CREATE TABLE dbo.ConceptPT ( id VARCHAR(32) NOT NULL PRIMARY KEY, term VARCHAR(500) NOT NULL, norm VARCHAR(500) NOT NULL );

CREATE TABLE dbo.ConceptBT ( idPT VARCHAR(32) NOT NULL REFERENCES dbo.ConceptPT, idBT VARCHAR(32) NOT NULL REFERENCES dbo.ConceptPT, CHECK(idPT <> idBT) );

It seems odd that the dbo.ConceptPT table loaded successfully, but the dbo.ConceptBT table fails on the first insert. The overall XSL is setup looks like:

<xsl:variable name="sql.conn" as="java:java.sql.Connection"> <!-- open the sql connection --> </xsl:variable>

<xsl:template mode="BT" match="..."> <!-- do insert for ConceptBT table --> </xsl:template>

<xsl:template mode="PT" match="..."> <!-- do insert for ConceptPT table --> </xsl:template>

<xsl:template match="/"> <xsl:apply-templates mode="PT" select="..." /> <xsl:apply-templates mode="BT" select="..." /> <sql:close ... /> </xsl:template>

The same nodes are being selected for modes PT and BT in the xsl:apply-templates.

Can anyone shed light on why there is a conversion error and what I can do to rectify the issue.

Thanks, Andy.

--------------------------------------------------------------

----------------------------------------------------------------------

-

--------------------------------------------------------------