Vlad Horsun wrote:

>> 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.
>
> Does you read part 3 ("Direct (pass-through) queries to external data source")
> of my proposal ? :)

Yes, of course. But I would stop there and would not dare to go to part
4 ("Heterogeneous queries to external data source"). It's unnecessary
(you can do the same easily with selectable SP that calls [FOR] EXECUTE
STATEMENT ... ON ...), adds another non-standard syntax and unnecessary
complexity, and above all it tempts you to play games with optimizer.

>> The hardest part would be to define
>> how the transaction scope is handled, but it's inescapable anyway.
>
> And i made attempt to describe this task too

I've noticed :) Personally, I would prefer to implement it in stages:

1. stage: Implement only AUTONOMOUS TRANSACTION. It would allow us to
avoid delving into complex transaction managers and deciding on complex
error handling when something goes wrong on external transaction when
local transaction ends (and we can't really do much at this stage). With
AUTONOMOUS transaction, we can simply return error code that could be
handled in WHEN "exception" block of SP/EXEC BLOCK, or resolved easily
at statement level when it's not handled by user code.

2. stage: Study how other servers provide this functionality, why and
with what results, and then decide how to define final syntax and
implement WITHIN COMMON [2PC] TRANSACTION.

best regards
Pavel Cisar
IBPhoenix