Hi Roman,

> > Now we have a design specification for another kind of external
> > routines - Java ones.
>
> If you mean the paper posted here, that's not 100% correct. We tried
> to avoid mentioning Java there as well as define interfaces generic
> enough, so that practically any VM can be used (.Net, Python,
> JavaScript and VBScript if you want).

Sorry, I had in mind VM ones, not Java.

> > 1) some symbolic qualifiers (like the proposed {JAVA | NATIVE} ones)
> > in the DDL syntax
>
> symbolic qualifiers should not belong to SQL syntax, otherwise adding
> new VM would mean changing engine.

You're right, as long as all possible kinds of VMs are supported by the
single execution engine via the defined interfaces.

> On the other hand it is not clear what to do with the selectable
> procedure that is executed with EXECUTE PROCEDURE statement. If
> procedure performs some database modifications in the FOR SELECT ;
> SUSPEND; loop, fetching only first row leads to incorrect results. I
> would rather disable this feature.

And break lots of existing databases ;-)

> > The first sentense can be made true without breaking anything
> > (select from non-selectable procedure always returns a NULL
> > resultset, so nobody should use such a code), but the second one is
> > a real problem.
>
> Why is it a problem? It might require some work, but in theory it
> requires only checking if procedure body contains SUSPEND keyword. We
> can even drop need for the keyword, but during BLR compilation simply
> set some flag in RDB$PROCEDURES table. And when compiling SELECT or
> EXECUTE PROCEDURE statement simply check if value of the flag is
> appropriate for the statement.

Compatibility is a problem. First of all, lots of people use SUSPEND at the
end of executable procedures just to be safe (unfortunately, I still see
such advices in some support forums). Secondly, some people (me included)
use such tricks to emulate stored functions - adding SUSPEND to a singleton
procedure allows it to be executed either via EXECUTE inside PSQL, or via
SELECT inside some query. Something like GET_CURRENT_SESSION is an obvious
example - it can be used in the WHERE clause and/or select list as well as
somewhere inside PSQL (without need to SELECT from this procedure there).

> > Now regarding the following statement "It should be possible to call
> > procedures that return only one output parameter as functions, e.g.
> > SELECT myProc(someCol) FROM someTable should be valid SQL
> > statement". I'd prefer to declare such functions explicitly, via
> > DECLARE EXTERNAL FUNCTION. It's not a matter of the parser only,
> > because stored (i.e. internal) functions are required by the SQL
> > specification to have slightly different syntax than stored
> > procedures, so there won't be any confusion there. But if we clearly
> > distinguish between internal procedures and functions, why making
> > external ones to behave separately? Our goal is to make the
> > difference (from the engine's POV) between internal and external
> > routines as much transparent as possible.
>
> My suggestion holds not only for external procedures, but for internal
> as well. I know there was a feature request in some list (or maybe
> Russian newsgroup) to implement this. Probably Eugeney will have some
> comments about your ideas.

FWIW, we have a standard. If we're going to follow it, an ability to have
both real stored functions and procedures that can be called as functions
looks very confusing to me. Even if this is easier to implement.

> > About the internal routines... I see Paul is keen to detach the
> > entire PSQL implementation from the engine and make it an
> > independent (although still built-in) subsystem under the
> > aforementioned interface. I don't have strong pro's or contra's at
> > the time being and would like to discuss these ideas later, once the
> > external stuff is agreed on.
>
> I think this idea has one advantage. If we agree that PSQL works
> through the same interface as other external procedures, we will have
> a possibility to check if this interface is implemented correctly by
> simply running all existing procedures that we have without need to
> implement something in Java/C#/etc. Consider this as an existing test
> suite for the interface.

Nice idea, thanks.


Dmitry