Jim,

> If I were king and dictator, I'd declare the existing API to be a
> legacy interface, and find something a little more modern. My first
> choice would be JDBC semantics as the most principled generally
> accepted database API available.

Cannot we look on the existing API as something very close to JDBC
suitable for C programmers?

isc_db_handle + isc_tr_handle <---> java.sql.Connection
isc_stmt_handle <---> java.sql.PreparedStatement + java.sql.ResultSet

Our experience with JayBird shows that most tricky code we have in
mapping between Firebird transaction and JCA/XA transaction models
mostly due to the fact that Firebird does not allow us to execute one
transation using different connections each time. JCA requires us to
implement connection pooling, but since transaction is linked to
socket, most tricks that were created by David Jencks are related to
checking "can I use this connection?", etc.

> While first crack might layer a JDBC translation layer on the
> existing DSQL/BLR engine, I'd evolve it into a SQL engine coexisting
> with the BLR engine, sharing OPT, EVL, VIO, IDX, etc. If I started
> with the existing C++ JDBC layer to support embedded Java JDBC
> access, I'd not only have a higher performance subsystem, but
> one that could be the core of a more modern engine.

That's too much for me. Maybe somebody from the team is capable of
doing such thing, but I personally don't feel to be able to start even
think about this. I don't have your experience in designing database
systems.

> If my only goal were embedded Java JDBC access, I would consider
> your approach.

Ok, I would probably limit myself to this case only :)

> The problem is lingering semi-orphaned JDBC objects referencing
> potentially deleted native objects. The engine really has to track
> Java object lifetimes to break the association of Java object to its
> "native" counterpart or face an ugly situation when a lingering
> object makes a native reference to a C++ object.

> If Java programmers could be taught to close objects when they're
> doing playing with them and not to start threads when the whim
> strikes, it wouldn't be a problem. But now that
> CPU and memory is almost free and almost infinite, kids don't learn
> these things until their regularly scheduled mid-life crisis.

We are capable of closing everything inside the database gracefully,
aren't we? Here we can define our own rules (I hope that SQLj specs
allows us to perform cleanup after Java SP execution). If somebody
leaves some thread running in the background that will do some
database access, we can allow this by allocating new connection
outside current execution context (start new transaction, like Message
Driver Beans do in J2EE world), or we can throw SQLException (much
more easire, valid though). If we exit the context, but there are some
open result sets, statements, etc., they will be closed, database
state will be rolled back to the state before calling Java SP and some
error is generated ("Sorry, find better programmer").

I don't care about other resources (sockets, file handles, etc),
that's responsibility of the programmer.

> Believe me, the most difficult of Netfrastructure is keeping
> everyone happy when a connect exits with active statements and
> resultSets that need to be closed before the Java garbage collectors
> gets around (if ever) to finalizing the objects. A really nasty
> multi-thread race condition.

I think I will start writing some architecture document, how do I plan
to integrate Java with Firebird assuming that I have GDS-like API and
some dynamic library with entry point described by Paul.

Thanks!
Roman