Hi all,

I think that external data sources should be internally handled as
streams of rows that provide only sequential access and potentially at
great expense (high cost value for optimizer). We need a clean metadata
specification in our formats at Firebird side and flexibility at
external side. The closest thing that match this specification we
already have in Firebird are selectable (and normal) stored procedures,
so why don't provide external data streams as selectable stored
procedures that happen to access external database internally ? We will
just define PSQL API to execute arbitrary statements on external data
providers and bind expressions into input parameters and return values
from row to local variables/output parameters (once we would have Java /
whatever language SP's, they could use their own native access method
like JDBC, ODBC, Python DBAPI etc. The hardest part would be to define
how the transaction scope is handled, but it's inescapable anyway.

This approach is simple, flexible, and doesn't require changes in
optimizer / execution engine, neither complex DDL additions and
enhancements.

It has a drawback that it doesn't allow any smart and magical internal
optimizations when the external data source happens to be the Firebird
server or another database at the same server. But personally I see it
more as advantage, as I think that any attempt to get smart and create
such optimizations is either doomed to fail or would bite us big time at
the end (external data sources should be rather used as escape routes,
not as basic building blocks).

best regards
Pavel Cisar
IBPhoenix