Subject Vulcan: Internal JDBC Support
Author Jim Starkey
I have implemented a prototype, internal, in-engine JDBC-type interface
in Vulcan. This means, for the first time in the long history of
Firebird, Interbase, and Rdb/ELN, SQL is now available inside the
database engine.

The ramifications are enormous. The following are now possible:

1. The gradual phasing out of all engine preprocessed modules.
2. In-line metadata access in ordinary engine objects
3. A mechanism to export internal database semantics to security plugins
4. A mechanism to export database semantics to user defined functions
and blob filters
5. A mechanism to supported internal engine tables layered on a small
set of core system tables.
6. An architecturally supportable mechanism to implement "execute
sql" functionality
7. A mechanism to export database semantics to alternative embedded
virtual machines such as Java and PLSQL.

And these are just the beginning. Internal SQL brings the same benefits
that database technology brings application software to the database
engine implementation itself.

The implementation is interesting. When I moved DSQL into the engine, I
broke "sql statement" into two classes, CStatement, a potentially cached
and shared compiled statement and DStatement, the statement instance
object. The CStatement object contains the actual request handle, the
message and parameter information, and specific knowledge of the
statement type. The JDBC-style InternalStatement also uses the
CStatement class and all of its goodies, but implements a different
client interface and value handling faithful to the set of JDBC abstract

Other than statement preparation, which is more expensive than a pure
BLR statement but subject to caching and re-use, the JDBC layer is very
thin and getting thinner as cleanups continue, so there is no
performance reason to retain preprocessed code for even high volume
metadata access.

I have also implemented "smart pointer" classes Connect, PStatement,
RSet to manage object destruction of Connection (InternalConnection),
PreparedStatement (InternalPreparedStatement), and ResultSet
(InternalResultSet and a gob of InternalXXXResultSets for JDBC metadata
access). A sample snippet of working code follows:

Connect connection = attachment->getUserConnection (transaction);
PStatement statement = connection->prepareStatement (
"select rdb$relation_name, rdb$relation_id from
RSet resultSet = statement->executeQuery();

while (resultSet->next())
ib_printf ("%s %d\n", resultSet->getString (1),
resultSet->getInt (2));

I'm not at sure I like the looks of the code -- it looks naked to me
without the "close()" operations. But it does clean up properly in the
face of an exception.

The intention is to have two methods to get internal connections -- a
user connection, subject to user level access control and security, and
a system connection with unlimited access.

I haven't figured out the correct transactions semantics. For the time
being, a client supplies the transaction object when requesting a
connection. I look forward to the debate as we try to figure out how it
should work.

The existing implementation is a working prototype proof-of-concept.
The list of what it does is much shorter than what it doesn't do, but
the structure is in place, so the rest is just details.

I started originally with the existing IscDbc code from the ODBC
driver. Unfortunately, it had mutated past use, so I reverted to a
version corresponding to my original ODBC driver submission. Oddly
enough, it has a striking resemblance to the internal Netfrastructure
API, with which I have great familiarity. As a consequence, I have
grown strangely territorial toward "Connection.h", which defines the
abstract interface. Muck with that and you will find a wolf on your
case so fast your head will spin. I have every intention of using the
same abstract interface to implement a JDBC client library then extend
the plumbing to transparently driver the semantics all the way into the
engine proper, bypassing and obsoleting the existing mega-ugly DSQL

This step is, in essence, the final recognition that SQL won, BLR lost,
and it's time to throw in the towel. Whether or not the JDBC mechanism
is layered on or replaces the existing BLR-base mechanisms is now
nothing more than a trivial implementation detail.

Good bye, BLR. And thanks for all the fish.


Jim Starkey
Netfrastructure, Inc.
978 526-1376

[Non-text portions of this message have been removed]