>> And I insist on the Java/JDBC example. It's more about SQL, not about
>> programming languages.
>
> Maybe it's worth asking the actual API users to express their opinions?
> :-) At least authors of IBPP, FIB+, IBProvider, JDBC/ODBC, etc.

Re. Java a short answer would be - "Java developers will have both, so
it does not really matter" (see below). Also, since there will be always
a proxy between Java code, it can do the conversion.

Now the longer explanation. There is some kind of de-facto standard for
the Java stored procedures. There was an attempt to create such standard
long time ago and it is known under the name SQL/J. In fact it consists
of three parts: using embedded SQL in Java, using Java routines in SQL
and a third one which I have forgotten.

So, the second part. First of all, it is about calling static methods,
passing the parameters into them and obtaining the results. As far as I
remember, the SQL/J does not consider the case of returning a result
set, only "functions" with zero or more input parameters and zero or
more output parameters. This is done by postulating that:

a) each public static method can be called;
b) input parameters are specified as usual having the desired type (and
there is type mapping defined);
c) output parameters are specified as arrays of the desired type of size
1; the routine itself assigns then the values to that very element of an
array and it is then returned into SQL layer.

Example:

public class MyBusinessRoutines {
public void myProc1(int inParam1, double inParam2, String inParam3,
int[] outParam1, double[] outParam2) {

....
outParam1[0] = 1;
outParam2[0] = 2.15;
}
}

Then you could issue

EXECUTE PROCEDURE myProc1(1,34.67,"Some value") RETURNING :var1, :var2;

Second, the SQL/J tells how to do the database calls from within the
Java code. It postulates that using the JDBC URL
"jdbc:default:connection" one can obtain a database connection within
the same context in which the Java routine was called. Then developer
can use normal JDBC code to perform queries, etc.

And that's it. The specification, IIRC, does not cover:

- case with the non-void method with no output parameters (e.g. a
function many input and one output params)

- returning the result sets to the client.

However, since these two cases looks quite important, we (Evgeny, Vlad
and myself) have "extended" the specification to support them. In case
of result set it looked obvious to use same JDBC interface as usual,
however it does not really matter.

Now re. the indexes, 0- or 1-based. As you can see, the SQL/J does not
really care what we define on the plugin API side - it would be the
responsibility of the Java plugin to extract the passed values, convert
it into Java objects, call the corresponding method and pass the results
back. It also does not matter when ResultSet object is returned from the
method - the column indexes are 1-based there, but again the plugin will
have to recode the values.

Also, I assume that same applies for .NET driver too - we can't call the
CRL method directly via entry points, so some kind of proxy is needed.

As to the Delphi, IBPP and regular C/C++ users, I guess there is no
standard there. The best option would be to check what is used, for
example, in PgSQL, which, AFAIK, supports calling external procedures.

Roman