Thanks for the very detailed reply!
Your advice to use an external connection is probably a good idea, however, what holds me back is the dependancy this creates on a one-off wrapper class that needs to be distributed with the style sheet. Has there been any discussion of allowing the sql:connect method to search for a JNDI datasource?
eg.:
<xsl:if test="not(sql:connect($db, 'extpool'))" > <!-- attempts to find JNDI datasource named 'extpool' -->
....
</xsl:if>
Thanks again, Eric Everman
On Feb 28, 2005, at 10:52 PM, John Gentilin wrote:
Eric,
The default connection pool was developed to provide code symmetry in the SQL Extension.
The default connection pool is assigned a name that consist of a concatenation of the Driver, URL, User name and password.
So if two different stylesheets are running inside the same JVM and use the same connection parameters they will benefit from
using the same connection pool.
Although the default connection pools exists, IMHO I think you would be better served to follow the external connection
pool example and establish the connection pool outside the scope of running transformations. it gives you much more control
over the how the connection pool operates. It also allows you to remove the connection information from the Stylesheet,
which could be a security issue not to mention a potential maintenance headache, to a centralized location. I.e. Have your
main class read the connection information from a config file, establish and register the pool. Then from each stylesheet reference
the pool by name.
As for recovery from a DB restart, if a query generates an error the extension code will release the connection to the pool
indicating that an error occurred. The default connection pool will remove that connection from the pool and return it, i.e.
close the connection and remove all references to its instance. The next time a pool is retrieved from the pool, if there are
no currently free connections, a new connection will be retrieved.
So to answer your question. Yes it will recover, but after a DB reset, all the current connections in the pool will cause
one query to fail. Once all the connections have been cycled through, then the queries will start to work. This assumes
that the JDBC driver that you are using will not just recover on its own in the case where the connection to the DB
was severed.
Regards John G
Eric Everman wrote:
Hi-
I'm wondering what the usage and 'scope' of the default connection pool is. If I use the default connection pool in one XSLT page, is that same pool used in all XSLT pages that specify the same connection information? Does the pool only exist for the duration of a single transformation? Must I explicitly close the connection? Will the pool handle a db restart?
This is the XSLT chunk I use in my pages. It works, but am I taking advantage of the pool, especially if this code is used in multiple pages?
================================================================
<xsl:template name="a-template">
<xsl:param name="driver">oracle.jdbc.OracleDriver</xsl:param>
<xsl:param name="datasource">jdbc:oracle:thin:name/[EMAIL PROTECTED]:1521:db</xsl: param>
<xsl:param name="queryString">SELECT * FROM MY_TABLE</xsl:param>
<xsl:variable name="connection" select="sql:new()"/>
<xsl:value-of select="sql:setFeature($connection, 'default-pool-enabled', 'true')"/> <!-- Connection Pool -->
<xsl:if test="not(sql:connect($connection, $driver, $datasource))">
<xsl:message>Error Connecting to the Database</xsl:message>
<xsl:copy-of select="sql:getError($connection)/ext-error"/>
</xsl:if>
<xsl:variable name="table" select='sql:query($connection, $queryString)'/>
<xsl:apply-templates select="$table/sql/row-set" mode="series-list"/>
</xsl:template>
================================================================
I've looked thru the docs and samples but have found not any explicit help on this.
Thanks in advance, Eric Everman
