Re: Problem with Sql Extension

[Erin Harris]

Hi,

John Gentilin ([EMAIL PROTECTED]) wrote the SQL extension support. 
 I don't think there is anyone else who knows much about it.  The source 
is available at: http://www.apache.org/dyn/closer.cgi/xml/xalan-j so one 
option would be to download the source and put some breakpoints in 
org.apache.xalan.lib.sql.XConnection to see what is happening.

Thanks.


Erin Harris





"Pankaj Bishnoi" <[EMAIL PROTECTED]> 
28/02/2007 02:19 AM

To
"Xalan List" <xalan-j-users@xml.apache.org>
cc

Subject
Problem with Sql Extension






Hi All
         I am using xalan sql extesnion. My xsl code is having a variable
named as varDB and in this variable i connect to database and the this
variable varDB is used in firing query. Queries are fired in xsl template
"dbLookupTemplate" . Now when i execute this stylesheet i get
java.sql.SQLException: [SybDriver] Too many connections: 3

 exception  ::

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
version="1.1" xmlns:java="http://xml.apache.org/xslt/java";
xmlns:xalan="http://xml.apache.org/xalan";
xmlns:str="http://exslt.org/strings";>
     <xsl:output method="xml" version="1.0" encoding="ISO-8859-1"
indent="yes"/>
     <xsl:param name="_repositoryPath"/>
     <xsl:param name="_Input1"/>
     <xsl:param name="_Input2"/>
     <xsl:variable name="Input_SchemaItemSource"
select="document($_Input1)"/>
     <xsl:variable name="Input_SchemaDatabaseEqid"
select="document($_Input2)"/>
     <xsl:variable name="apos">'</xsl:variable>
     <xsl:variable name="varDB"
select="globalmethod:new(&apos;com.inet.syb.SybDriver&apos;,
&apos;jdbc:inetsyb:ashwani:5000?database=master&apos;,&apos;sa&apos;,&apos;&
apos;)" xmlns:globalmethod="org.apache.xalan.lib.sql.XConnection"/>
     <xsl:variable name="query" select="&apos;SELECT eqname FROM 
dbo.t_equip
where eqid =&apos;"/>

     <xsl:template name="dbLookupTemplate">
          <xsl:param name="db" select="'test'"/>
          <xsl:param name="query" select="'dummy'"/>
          <xsl:param name="eqid" select="'2223'"/>
          <xsl:variable name="updatedQuery" 
select="concat($query,$eqid)"/>
          <xsl:variable name="table" select="globalmethod:query($db,
$updatedQuery)" 
xmlns:globalmethod="org.apache.xalan.lib.sql.XConnection"/>
<xsl:if test="$table/sql/row-set/row/col != ''">
          <xsl:choose>
               <xsl:when test="$table/sql/row-set/row/col = 'eth0'">
                    <xsl:value-of select="$table/sql/row-set/row/col"/>
               </xsl:when>
               <xsl:otherwise>
                    <xsl:call-template name="dbLookupTemplate">
                         <xsl:with-param name="db" select="$db"/>
                         <xsl:with-param name="query" select="$query"/>
                         <xsl:with-param name="eqid" select="21778"/>
                    </xsl:call-template>
               </xsl:otherwise>
          </xsl:choose>
</xsl:if>
     </xsl:template>
     <xsl:template match="/">
          <ItemTrace>
               <Item>
                    <xsl:attribute name="Name">
                         <xsl:value-of
select="$Input_SchemaItemSource/AddItem/Item/@Name"/>
                    </xsl:attribute>
                    <xsl:attribute name="id">
                         <xsl:value-of
select="$Input_SchemaItemSource/AddItem/Item/ItemInfo/id"/>
                    </xsl:attribute>
                    <xsl:attribute name="date">
                       <xsl:call-template name="dbLookupTemplate">
                              <xsl:with-param name="db" select="$varDB"/>
                              <xsl:with-param name="query" 
select="$query"/>
                              <xsl:with-param name="eqid"
select="$Input_SchemaItemSource/AddItem/Item/ItemInfo/id"/>
                         </xsl:call-template>
                    </xsl:attribute>
                    <Trace>
                         <xsl:call-template name="dbLookupTemplate">
                              <xsl:with-param name="db" select="$varDB"/>
                              <xsl:with-param name="query" 
select="$query"/>
                              <xsl:with-param name="eqid"
select="$Input_SchemaItemSource/AddItem/Item/ItemInfo/id"/>
                         </xsl:call-template>
                    </Trace>
                    <xsl:variable name="varDBClose"
select="globalmethod:close($varDB)"
xmlns:globalmethod="org.apache.xalan.lib.sql.XConnection"/>
               </Item>
          </ItemTrace>
     </xsl:template>
</xsl:stylesheet>



My question is this that why XConnection is creating seprate connection 
for
each query firing. Why the XConnection is not using single connection ? 
How
can i configure this default connection pool?

Thanks
Pankaj