atom feed21 messages in net.sourceforge.lists.saxon-help[saxon] SQL extension insert conversi...
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:[saxon] SQL extension insert conversion error
From:Houghton,Andrew (houg@oclc.org)
Date:May 23, 2008 8:07:23 am
List:net.sourceforge.lists.saxon-help

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.