JBoss and Statement Pooling endgame.

First off, many thanks for everyone's time.

Quick answer: JBoss can already do it, but we'll need our server
prepared statements to be able to report the number of rows updated by
an UPDATE command (Just as Kris reported was needed of 7.5.). It checks
the result, and really wants to see the number it expects, at least
when it thinks it knows the answer:

org.jboss.tm.JBossRollbackException: Unable to commit,
tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=jlrobins.local//29,
BranchQual=] status=STATUS_NO_TRANSACTION; - nested throwable:
(javax.ejb.EJBException: Update failed. Expected one affected row:
rowsAffected=0id=44596); - nested throwable:
(org.jboss.tm.JBossRollbackException: Unable to commit,
tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=jlrobins.local//29,
BranchQual=] status=STATUS_NO_TRANSACTION; - nested throwable:
(javax.ejb.EJBException: Update failed. Expected one affected row:
rowsAffected=0id=44596))

Long Answer:

After more digging around in JBoss, first verifying if indeed JBoss
uses its own PooledConnection implementation or uses the JDBC driver's
implementation, I found that it uses its own, so caching prepared
statements in our own JDBC driver's implementation would have done me
no good. So I dug into the code in JBoss 3.2 cvs head corresponding to
the classes that it reported implemented the DataSource and the
Connections returned by the DataSource
(org.jboss.resource.adapter.jdbc.WrapperDataSource and
org.jboss.resource.adapter.jdbc.WrappedConnection, found in cvs module
jboss-3.2, subdir connector/src/main/org/jboss/resource/adapter/jdbc)
and poked around.

Their WrappedConnection made reference to a Map of prepared connections
(if it had been constructed or subsequently tweaked with a prepared
statement pool size, replaced LRU, with the key being the SQL template
passed into the Connection.prepareStatement(String sql) method.

Then the trick was to figure out how to enable this JBoss feature,
configuring the beast being a twisty maze of XML rooms, each one
looking the same as the rest. Their search engine finally turned up a
tag "<prepared-statement-cache-size>" which can be embedded in your
datasource config XML file (see your jboss distro,
/docs/dtd/jboss-ds_1_0.dtd). This enabled this feature in our copy of
JBoss 3.2.3 (but didn't make 3.2.1 flinch at all -- bug? Unimplemented
at that time?).

The only way I could enable server-side statement preparation to really
test this out was to then inject a hack into our
AbstractJdbc1Statement's AbstractJdbc1Statement (BaseConnection
connection, String p_sql) constructor, making an explicit call to
setUseServerPrepare(true) so as to force server preparation for all
PreparedStatements. And then things went well, up until the point at
which it tried to do an update, at which the lack of being able to
return the count of updated rows made the beast upset.

But up until the first update statement, I watched transactions start,
queries get prepared, then executed, then not deallocated, since JBoss
was keeping the PreparedStatement open. Life with backend 7.5 will be
good indeed!

I suppose I could further refine my driver hack to only force-prepare
if it smells like a SELECT as opposed to UPDATE / INSERT for further
experimentation to see if it ultimately is worth it.

----
James Robinson
Socialserve.com