Vlad Khorsun wrote:

>> Here is a possible example of how it could be:
>>
>> create procedure query returns (
>> id integer,
>> name varchar(60)
>> )
>> external name 'Query.query(ProcedureContext) returns
>> ExternalResultSet!some_database'
>> engine java
>> as
>> q'{
>> select id, name
>> from person
>> }';
>>
>> [ There is where the new quotation syntax works great. ;-) ]
>>
>
> I'm sorry, but i understand nothing in this example. Could you explain what's what
> and for what ?
>

Sorry, I'll be more verbose. :-)

Example above was about execute query (defined at metadata), against
others databases (some_database may be a pool name, with has connection
information in another table) and return its result as a selectable
procedure. So lets imagine how that could be without a body. A important
point here is "defined at metadata", i.e., it's not the user that pass
the query, but the database developer.

One way would be to someone code a external routine with the query on
its body, and have the method (may be in Java, but is analog for others
languages) with (output) parameters declared to return the specific
information:

create procedure customers returns (
id integer,
name varchar(60)
)
external name 'Queries.customers(int[], String[]) returns
ExternalResultSet!some_database'
engine java;

create procedure employees returns (
id integer,
name varchar(60),
salary numeric(10,2)
)
external name 'Queries.employees(int[], String[], BigDecimal[])
returns ExternalResultSet!some_database'
engine java;

Then code Java methods Queries.customers and Queries.employees.

Every time surges a necessity to run a different query (or import a XML
file, or run a XQuery, etc) one will need to code a specific method and
declare it.

Another way would be to declare a external method that receives the
query as parameter and create two PSQL procedure wrappers for each one,
to pass the query:

create procedure run_customers
query varchar(1000)
) returns (
id integer,
name varchar(60)
)
external name 'Queries.runQuery(ProcedureContext) returns
ExternalResultSet!some_database'
engine java;

create procedure run_employees returns (
query varchar(1000)
) returns (
id integer,
name varchar(60),
salary numeric(10,2)
)
external name 'Queries.runQuery(ProcedureContext) returns
ExternalResultSet!some_database'
engine java;

create procedure customers returns (
id integer,
name varchar(60)
)
as
begin
for select id, name from run_customers('select * from customers')
into id, name do
suspend;
end;

create procedure employees returns (
id integer,
name varchar(60),
salary numeric(10,2)
)
as
begin
for select id, name, salary from run_employees('select * from
employees') into id, name, salary do
suspend;
end;

With body as I presented, one external method will be capable to run the
queries, and one (instead of two) external declarations for each query
will be needed.

But external engines is generic, it's not only about run queries and
return results. So lets talk about another thing, the Fyracle case. One
of its objective is to run PL/SQL. Considering this (only, as I don't
know all Fyracle features) objective, no matter how Fyracle works, it
has a huge drawback. It's not Firebird anymore. And that fact alone is a
big thing that may cause people needing PL/SQL to not look at it (or any
other possible similar product).

So how that could be better? First, if Firebird had a stable API and
someone implements PL/SQL (or anything else) for it, without modify
Firebird itself, chances of user acceptance are much more. Second, a
PL/SQL here is an external engine, so external engines should support
that case. Third, that thing about another languages should be used
easily. How would you consider one writing a PL/SQL procedure in
Firebird without a body? I don't know, but with a body:

create procedure proc1 (
n1 integer
)
as
q'{
/* here is the PL/SQL body */
}';

The declaration, of course, need to be in PSQL, but that is all. A
PL/SQL engine could nicely get the parameters and pass to its body.

And what about if I want to create a simple Java method, that does
something simple, but that I need to call in a trigger. Should I write a
class, store it at database or file system, even if it's a simple and
unique Java method used in the database?

Well, I could then create the method in the body. But, hey, then when it
will be compiled, at runtime?

May be, but DDL triggers may nicely handle this problem. Engines
(plugins) could have DDL triggers, that may know that a Java (or PL/SQL
or whatever) method is being created, compile it and if wrong, throw
exception. If it's ok, it binary code could be stored on a plugin table.
Everything (alter, drop) could be handled with DDL triggers.

That's why I want it. Everything fits nicely on the architecture, make
user's life easier and is easy to create currently.

>
>
>> And that is my other wish. Instead of only external name and engine,
>> external routines could also have a body. It should be optional, and
>> maybe even the external name could be, but at least one of NAME/body
>> shall be specified.
>>
>> That body will be stored on the normal source column present on system
>> tables, that currently is always NULL for external routines.
>>
>
> And who and what will do with this body ?

This is plugin job. It may pass it to user function. It may consume it
entirely and do something (for routines with body but without an
external name). He decides. Firebird just pass the information for him.


Adriano