Jim,

> I agree. But I don't agree that prepared statement pooling should
> be done at the driver level. It is better for everone involved if
> it is done at the database level. While this is infeasible in
> Firebird 2 and earlier, it is the basis for the Vulcan request
> architecture.

I agree with you, engine can manage such resources better than connection
pool. And the price of allocating a physical connection is very low now.

> I am not a fan of connection pooling. Again, I think it's a
> solution at the wrong level. Connection pooling is important only
> when the cost of database attachment is high.

Not only this. Connection pools (or more correctly, data sources, but
usually these concepts are intermixed) have a plenty of other properties,
e.g. limiting the number of open connections, connection leak detection etc.

> All that said, fewer connections are better than more connections,
> which discourages programmers from using multiple independent
> transactions/connections when program logical would otherwise call
> them them. I've always had a lot of sympathy for application
> programmers, which is why I built multiple transactions per
> attachment into DSRI/OSRI. At that time, blobs, triggers, UDFs, and
> even dates were non-standard, so we've had some progress.
> But there is no doubt in my mind that the SQL committee blew it on
> transactions and the JDBC designed amplified the blunder. If it can
> be fixed within the context of the standard, I think it makes sense
> to do so.

I support the approach David suggests. When application needs a new
transaction, application should initiate new transaction context in the
application server. The rest is done by the JCA code: application server
would allocate another managed connection, and connector does it best to
manage resources. In fact, application running in the application server
does not know anything about the physical connection. It knows about temp
handle (in our case implementing java.sql.Connection interface) that is
valid only for the current context. A clone() method makes little sense for
a temp handle. So, this feature is useless for code running in application
server.

Though this does not solve the issue for the standalone applications. I
doubt that people would want to implement that part of the J2EE container
themselves or integrate some open-source code. So, people that wrote their
own code to handle connections/persistence/transactions might like this
feature... if there is a real need for this.

> A cloned connection could outlive the original connection, but
> perhaps the physical atttachment should not be reused until the
> parent connection and all clones have been closed.

I suspect to implement it this way would mean a lot of changes in JayBird.
For me personally that would also mean to get the expertise in JCA, which I
do not plan to do in the nearest future. So, unless there's somebody willing
to sponsor my JCA-studies for at least 3 months and then at least 2 months
for refactoring JayBird and implementing a feature, and later to pay for all
bugfixes I would have to make to keep everybody happy, I will not start
working on it. But a volunteer(s) that would take full responsibility of
JayBird in this case are welcome. Hey, this is open source, code is free! :)

And one more idea: The original request comes from a person that migrates
from Delphi to Java and used IBX components. IBX does not really fit the
Borland's model for database-related code. Neither it fits any of Microsoft
standards, and despite this, it exists. So, maybe there's also a market
niche for a IBX for Java. Backend code is there, that's the
org.firebirdsql.gds, org.firebirdsql.jgds and org.firebirdsql.ngds packages.
They have notions of a "Database", "Transaction", "Query", etc. There's a
big part of FB API available. Developer would need to create nice beans that
can be linked visually in JBuilder (and other IDEs) and try to convince
others to use these components insead of JDBC. Personally I do not believe
in this, but maybe I'm too old for the innovation.

Roman