Phil,
In earlier versions of 2.x we had some issues about classloaders and JDBC drivers.
So there have been some changes there. One change that is in the default connection
pool is that we grab a connection, that not used, and just hold a reference to it. Some JDBC drivers, I think MySQL or postgress, would unregister the driver when the
connection count got to zero and this would cause problems when the pool tried to
grab another connection. Classloaders and the SUN endorsed mechanism have been a
thorn in my side as of late... :-(
The internal or default connection pool was only provided for code symmetry to support
command line operation. When I say command line op, I mean the test code that you run
with Process where all the connection parameters are passed to the connect statement.
When using the external connection pool, you create the pool externally, i.e. in the controlling program, the pool is registered with the Pool Manager and is referenced by name. A similar mechanism to the one implemented in JNDI, actually I tried to make
the interface easy/robust enough that homegrown connection pools could be supported in
plain old JDBC and with the implementation of a simple wrapper, also support JNDI style
connection pools.
When you pass all the connection parameters on the connect method, the XConnection object
creates a private pool name constructed via a mangling of all the connection properties and uses that as the name reference. If in your code, you are caching the Transformer
and reusing it, the default connection pool will probably be reused as long as the connection parameters are exactly the same. If you are caching templates, the references
to the connection pool and the pool manager may get destroyed so each time the transformer
is run the SQL extensions will need to rebuild the connections.
You can minimize the use of the Connection Pool by turning of connection pooling in your stylesheet by either
// The old way
xconn.disableDefaultConnectionPool( )
// The new way
xconn.setFeature("default-pool-enabled", "false")
If this is set, each time you call xconn.close the connections are really closes and released to the JDBC driver.
During the design of the SQL extensions, I made the assumption that any production environment would use external connection pooling for the following reasons.
1) its more secure. The connection properties are specified in one general place and the Stylesheets only know the Connection Pool Name.
2) Global Connection Pools are just more efficient since the connection can be shared across Stylesheets also the connections can be shared across the application. i.e. I use the connection pool for the update side of our applications as well as the Stylesheet / Query side.
3) Allow Xalan to utilize EJB and JNDI connections.
4) Most Applications already have some connection pooling mechanisms already installed. i.e. Tomcat, with a simple wrapper class almost any existing connection pool implementation can be adapted to cooperate with Xalan and share resources. ConnectionPool is just an interface, DefaultConnectionPool is just one implementation of that.
There are two ways to close connections in your query. Assuming you create the query with
<xsl:variable name="Qy" select="sql:query($DbCon, $SQL)"/>
the the close would be
<xsl:value-of select="sql:close($DbCon)" />
Note the value-of returns null so it does not affect the result tree.
This will close as discard any connections and SQL document that were created with the object $DbCon.
You can also use xsl:value-of select="sql:close($DbCon, $Qy)" />
And that will just discard the SQL Document $Qy and its associated connection.
Thank you for the complement, participation in the Apache Xalan project has lead me down a much more interesting path than I would of ever thought of and its been fun to boot.
My response ran on there a bit, let me know if I have answered your questions.
Regards John G
Phil Friedman wrote:
John,
This behavior changed between 2.0.1 and 2.6.0. In 2.0.1 one connection was reused for each subsequent SQL batch. But that's water under the bridge.
I don't understand how using external connection pools helps. How or when is a connection returned to the pool to be reused without closing the connection? Why does it matter if it is the internal pool or an external pool?
It seems to me SQLDocument.close() needs to be called after I am done with the result-set(s) from an SQL batch. How do I do this from XSLT?
BTW, thanks for all you work and help with the SQL Extensions. They are a key part of our use of Xalan.
-- Regards, Phil Friedman -- ABLE Computer Consulting
John Gentilin wrote:
Yes, you need to call XConnection.close() to close all the connections that
were opened. You should also go to external connection pools. The way you are
using the internal connection pool, you are going to have a ton of memory
turn over because the JDBC driver will probably be unregistered / discarded
for each transformation, not to mention the time it takes to build up the
connection pool.
Regards
John G
Phil Friedman wrote:
John,
Is there something to this? I finally got eclipse to display my source, rather than the JDK source. I looks like executeSQLStatement() finds the previous connection inUse and opens another. Is there something I need to do in my XSLT or in SQLDocument.java to free the connection? Remember, I've changed SQLDocument.java line 400 or so to:
if (! m_QueryParser.hasParameters() )
{
m_Statement = conn.createStatement();
//if (! m_Statement.execute(m_QueryParser.getSQLQuery()))
//{
// throw new SQLException("Error in Query");
//}
m_ResultSet = m_Statement.executeQuery(m_QueryParser.getSQLQuery());
}
This is running from org.apache.xalan.xslt.Process. I connect with: sql:connect( $DbCon, $DbDrvr, $DbUrl, $DbUsr, $DbPwd).
Do I need a <xsl:value-of select="sql:close($DbCon)"/> after processing the results of each sql batch?
[EMAIL PROTECTED] wrote:
Quoting Phil Friedman <[EMAIL PROTECTED]>:
It seems that after a large number --hundreds, but I have not counted-- of
queries on the same db connection, the SQL extension starts returning empty
result sets with no errors thrown.
I'm at 2.6.0 connecting to Sybase SQL Anywhere 8. There are no problems with
the SQL as I've run similar statements earlier and can restart and run the
same statements w/o errors later.
Any ideas on what to look for? Regards, Phil Friedman - Terralink Software - 1-207-772-6500 x101
Perhaps you are not closing the resultsets/statements and you are running out of
server side cursors, or other db objects?
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
