Roman Rokytskyy escreveu:

> 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);
>

I'm doing this stuff, see below.

> 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.
>

Our output parameters (of stored procedures) are different from others
DBMS. So I think we may use something (ResultSet, for example) different.

> 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;
>

In Oracle, you (always, AFAIK) need to specify the parameter types and
return type. You can specify types as "int" or "java.lang.Integer".

In the implementation I'm doing, if types are not passed, it
automatically maps INTEGER to java.lang.Integer. IMO, we should never
transform NULL to 0.

> 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"

In Oracle it's "jdbc:default:connection:" (not the ":" at end).

> 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.
>

Already working in my implementation. ;-)

> 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.
>

I think we can support return of java.sql.ResultSet, but we should
construct (derived from ResultSet) another class to make easy creation
of selectable procedures that produce rows, like ExternalResultSet from
FirebirdExternalApi.h. It's very difficult and non-sense to implement
ResultSet methods to return values.

> 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.

Certainly. But it's a case of 0-based language with a standard SQL
interface 1-based.


Adriano