Hi All

I think before create a new feature in Firebird, it is desirable to look how it is made in other databases and it is described in the SQL standard. In standards mechanism of calling routine is described by LANGUAGE and PARAMETER STYLE. By PARAMETER STYLE we can describe of parameter transmission. By PARAMETER STYLE we can solve discussion fist index is 0 or 1.

As example in UDF we can describe parameter by value, by pointer, or by structure PARAMDSC. UDF can write in one language (C++) and have many types of call parameters.
If external procedure return cursor (in java by instance of java.sql.ResultSet) routine described DYNAMIC RESULT SETS. Additional routine can read and modify data its can be described.

My thought about ENGINE and language of External routine:
First: ENGINE is not equals LANGUAGE. Engine is plug-in for execute external procedures. Language and PARAMETER STYLE is definition of call mechanism.
Second: Engine don't contain external procedure with business logic. Engine can contain routines for rule engine. As example SQL standard contains 4 procedure for rule JVM: SQLJ.INSTALL_JAR, SQLJ.REPLACE_JAR, SQLJ.REMOVE_JAR, SQLJ.ALTER_JAVA_PATH.

Some engine can be built in firebird server. As example: engine for SQL and C language.

Example of definition:

Declare function
CREATE FUNCTION state_region(state CHARACTER(20)) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.region';

Declare procedure with SQL modification data
CREATE PROCEDURE state_correction(old CHARACTER(20), new CHARACTER(20))
MODIFIES SQL DATA
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.correctStates';

Declare procedure with selectable SQL and return result in output parameters
CREATE PROCEDURE best2 (
OUT n1 CHARACTER VARYING(50), OUT id1 CHARACTER VARYING(5), OUT r1 INTEGER,
OUT s1 DECIMAL(6,2),
OUT n2 CHARACTER VARYING(50), OUT id2 CHARACTER VARYING(5), OUT r2 INTEGER,
OUT s2 DECIMAL(6,2), region INTEGER)
READS SQL DATA
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines2_jar:Routines2.bestTwoEmps';

Declare procedure with selectable SQL and return result in instance of java.sql.ResultSet
CREATE PROCEDURE ranked_emps (region INTEGER)
READS SQL DATA
DYNAMIC RESULT SETS 1
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines3_jar:classes.Routines3.orderedEmps(int, java.sql.ResultSet[])';


WBR Evgeny Putilin