Re: [xsl] SQL instert

[Michael Kay <mike@xxxxxxxxxxxx>]

It's a very Saxon-specific question so it would be better asked on a 
Saxon list.

I would suggest making the connection a global variable so the 
stylesheet only connects to the database once. Having said that, I don't 
know exactly what's causing the error, but I'm no expert on JDBC.

Michael Kay
Saxonica

On 21/07/2011 16:04, Szabo, Patrick (LNG-VIE) wrote:
> Hi,
>
> Im using java:/net.sf.saxon.sql.SQLElementFactory to insert entries into
> a db.
> My template looks like this:
>
> <xsl:template name="sql-insert">
> 		<xsl:param name="doknr"/>
> 		<xsl:param name="gericht"/>
> 		<xsl:param name="datum"/>
> 		<xsl:param name="filename"/>
> 		<xsl:param name="gz"/>
> 		<xsl:param name="typ"/>
> 		<xsl:param name="sz"/>
> 		<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:fallback>
> 					<xsl:message terminate="yes">SQL
> extensions are not installed</xsl:message>
> 				</xsl:fallback>
> 			</sql:connect>
> 		</xsl:variable>
> 		<xsl:choose>
> 			<xsl:when test="$typ = 'Volltext'">
> 				<xsl:choose>
> 					<xsl:when test="$gericht =
> 'OGH'">
> 						<sql:insert
> connection="$connection" table="Volltext_OGH">
> 							<sql:column
> name="DOKNR" select="normalize-space($doknr)"/>
> 							<sql:column
> name="Datum" select="normalize-space($datum)"/>
> 							<sql:column
> name="Filename" select="normalize-space($filename)"/>
> 							<sql:column
> name="Gesnr" select="normalize-space($gz)"/>
> 							<sql:column
> name="SZ" select="normalize-space($sz)"/>
>
> 						</sql:insert>
> 					</xsl:when>
> 					<xsl:when test="$gericht =
> 'VWGH'">
> 						<sql:insert
> connection="$connection" table="Volltext_VWGH">
> 							<sql:column
> name="DOKNR" select="normalize-space($doknr)"/>
> 							<sql:column
> name="Datum" select="normalize-space($datum)"/>
> 							<sql:column
> name="Filename" select="normalize-space($filename)"/>
> 							<sql:column
> name="Gesnr" select="normalize-space($gz)"/>
> 						</sql:insert>
> 					</xsl:when>
> 					<xsl:when test="$gericht =
> 'VFGH'">
> 						<sql:insert
> connection="$connection" table="Volltext_VFGH">
> 							<sql:column
> name="DOKNR" select="normalize-space($doknr)"/>
> 							<sql:column
> name="Datum" select="normalize-space($datum)"/>
> 							<sql:column
> name="Filename" select="normalize-space($filename)"/>
> 							<sql:column
> name="Gesnr" select="normalize-space($gz)"/>
> 						</sql:insert>
> 					</xsl:when>
> 					<xsl:when
> test="starts-with($gericht, 'AUSL')">
> 						<sql:insert
> connection="$connection" table="Volltext_AUSL">
> 							<sql:column
> name="DOKNR" select="normalize-space($doknr)"/>
> 							<sql:column
> name="Gericht" select="normalize-space($gericht)"/>
> 							<sql:column
> name="Datum" select="normalize-space($datum)"/>
> 							<sql:column
> name="Filename" select="normalize-space($filename)"/>
> 							<sql:column
> name="Gesnr" select="normalize-space($gz)"/>
> 						</sql:insert>
> 					</xsl:when>
> 				</xsl:choose>
> 			</xsl:when>
> 			<xsl:otherwise>
> 				<xsl:choose>
> 					<xsl:when test="$gericht =
> 'OGH'">
> 						<sql:insert
> connection="$connection" table="Rechtssatz_OGH">
> 							<sql:column
> name="DOKNR" select="normalize-space($doknr)"/>
> 							<sql:column
> name="Datum" select="normalize-space($datum)"/>
> 							<sql:column
> name="Filename" select="normalize-space($filename)"/>
> 							<sql:column
> name="Gesnr" select="normalize-space($gz)"/>
> 						</sql:insert>
> 					</xsl:when>
> 					<xsl:when test="$gericht =
> 'VWGH'">
> 						<sql:insert
> connection="$connection" table="Rechtssatz_VWGH">
> 							<sql:column
> name="DOKNR" select="normalize-space($doknr)"/>
> 							<sql:column
> name="Datum" select="normalize-space($datum)"/>
> 							<sql:column
> name="Filename" select="normalize-space($filename)"/>
> 							<sql:column
> name="Gesnr" select="normalize-space($gz)"/>
> 						</sql:insert>
> 					</xsl:when>
> 					<xsl:when test="$gericht =
> 'VFGH'">
> 						<sql:insert
> connection="$connection" table="Rechtssatz_VFGH">
> 							<sql:column
> name="DOKNR" select="normalize-space($doknr)"/>
> 							<sql:column
> name="Datum" select="normalize-space($datum)"/>
> 							<sql:column
> name="Filename" select="normalize-space($filename)"/>
> 							<sql:column
> name="Gesnr" select="normalize-space($gz)"/>
> 						</sql:insert>
> 					</xsl:when>
> 					<xsl:when
> test="starts-with($gericht, 'AUSL')">
> 						<sql:insert
> connection="$connection" table="Rechtssatz_AUSL">
> 							<sql:column
> name="DOKNR" select="normalize-space($doknr)"/>
> 							<sql:column
> name="Gericht" select="normalize-space($gericht)"/>
> 							<sql:column
> name="Datum" select="normalize-space($datum)"/>
> 							<sql:column
> name="Filename" select="normalize-space($filename)"/>
> 							<sql:column
> name="Gesnr" select="normalize-space($gz)"/>
> 						</sql:insert>
> 					</xsl:when>
> 				</xsl:choose>
> 			</xsl:otherwise>
> 		</xsl:choose>
> 		<sql:close connection="$connection"/>
> 	</xsl:template>
>
> Now it seems to work for some time/files but then i get the following
> error:
>
> Error on line 35 of sql_insert.xslt:
>    SXSQ0003: JDBC Connection Failure: Network error IOException: Address
> already in use: connect
>    at xsl:call-template name="sql-insert"
> (file:/R:/Produktion/Cases/xslt/transformiere_daten.xslt#103)
>    at xsl:apply-templates
> (file:/R:/Produktion/Cases/xslt/transformiere_urteile.xslt#139)
>       processing
> /result/data[1]/risdokument[1]/metadaten[1]/judikatur[1]/gz[1]/item[1]
>    at xsl:apply-templates
> (file:/R:/Produktion/Cases/xslt/transformiere_urteile.xslt#31)
>       processing
> /result/data[1]/risdokument[1]/dokumentliste[1]/hauptdokument[1]/inhalt[
> 1]/daten[1]/xml[1]/nutzdaten[1]
>    at xsl:apply-templates
> (file:/R:/Produktion/Cases/xslt/transformiere_daten.xslt#86)
>       processing /result/data[1]/risdokument[1]
> While processing JFT_10048998_95W00I05_00.xml: Run-time errors were
> reported
>
> Does anyone see what is causing this problem ?!
> I do close the connection in the end of the template so how can this be
> happening ?!
>
> Kind regards
>
> . . . . . . . . . . . . . . . . . . . . . . . . . .
> Patrick Szabo
>   XSLT Developer
> LexisNexis
> Marxergasse 25, 1030 Wien
>
> mailto:patrick.szabo@xxxxxxxxxxxxx
> Tel.: 00431 534521573
> Fax: +43 (1) 534 52 - 146