Subject Re: External procedures: implementation proposal.
Author paulruizendaal
Hi all,

Sorry to have been quiet in this thread so far ... a project that I
am working on is eating up nearly all my time.

There are three things that are important to me:

[1] Fyracle needs to add java stored procedures. They are not
important to Oracle mode per se, but it is the way that the Compiere
system has gone and I need to follow to keep users happy. Being able
to run an unmodified Compiere is important to them and is part of the
marketing positioning of Fyracle/Firebird.

In this light, I am happy with the external procedure proposal. It
can be implemented quickly and runs efficiently. It is good enough for
the Fyracle releases based on 1.5.x and I do not care at all that
layering is broken (it already is, so nothing is lost). I am striving
to have this implemented by the end of August, regardless of
architect list discussions.


[2] Longer term (i.e. for Fyracle releases based on FB3) I think it
is worthwhile that layering is restored. This requires properly
architected ways to "call back into the engine" and indeed I
discussed this with Jim and Ann back in the spring of 2004.

Personally, I like the jdbc-style C++ api used in the IscDbc code and
support the idea that something like this should be the interal
engine API for FB3 and beyond.

However, API and layering are not the same thing. The essence for
restoring layering is that the code should no longer call up into the
Y-valve to get the connection handle ("GetWhyAttachment" function

For instance, it would be possible for the engine to expose a jdbc-
style connection object and for the external SP interface code to
expose a isc_* api like call interface, generating its own handle
id's on the fly, just like the Y-valve currently does. The IscDbc
code builds a jdbc-like api on top of the isc_* api; the reverse is
equally feasible.

In my mind, the big question for FB3 external SP's is not so much the
API, but how to isolate failures in the external code from messing up
the engine. PL/SQL, java, .net/mono could perhaps be supported "in
process" with sufficient stability, but how about everything else?

[3] The current proposal for external SP's might fall short in one
area: the handling of OUT params and cursor result sets. This is not
needed for the goals described under [1], so I am willing to press
ahead without it.

In Oracle, DB2 and SQLServer stored procedures can have both IN OUT
parameters and a cursor-typed return value. The current proposal
cannot handle this. One could argue that for the top-level procedure
called from the relational engine, OUT and IN OUT parameters are
illegal so that the infrastructure proposed could be used exclusively
for handling of cursor-typed return values. I need to study DB2 and
SQLServer a bit more to have a final opinion on this topic.