db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: number of parameters
Date Wed, 02 Jan 2013 12:28:15 GMT
Pavel Bortnovskiy <pbortnovskiy@jefferies.com> writes:

> we have a need to create a java-mapped function which will take 92
> parameters, but we are running into Derby’s limitation:
> Caused by: java.sql.SQLException: The limit for the number of
> parameters for a procedure has been exceeded. Limit is 90 and number
> of parameters for procedure CALC_VALUES are 92.
> http://db.apache.org/derby/docs/10.8/ref/rrefdbmlimits.html
> Can this limit either be increased or worked-around?

Comments in the code suggest that the limit was put in place for
compatibility with DB2. I don't think there is anything in the
architecture that limits the number of parameters to 90, so lifting the
limit might be as simple as changing a constant in Derby and write some
tests to verify that it works.

> [...]
> So, we mapped a Java function and then we need to do something like this:
> select a,b,c,
>   case
>     when JAVA_FUNCTION(d,e,f) = 0 then COLUMN00
>     when JAVA_FUNCTION(d,e,f) = 1 then COLUMN01
>     when JAVA_FUNCTION(d,e,f) = 2 then COLUMN02
>                 ...
>     when JAVA_FUNCTION(d,e,f) = 88 then COLUMN89
>     else COLUMN89
>  end as col_value
> ...
> But such select causes JAVA_FUNCTION to be invoked 90 times.

It would be nice if Derby's SQL compiler could recognize that
JAVA_FUNCTION is called with the same arguments every time and only call
it once. (If it is declared with the DETERMINISTIC keyword, that is.)

Might be possible to work around it with a nested query, though:

  select a,b,c
      when colNum = 0 then COLUMN00
      when colNum = 1 then COLUMN01
      when colNum = 2 then COLUMN02
      when colNum = 89 then COLUMN89
      else COLUMN89
    end as col_value
  from (select t.*, JAVA_FUNCTION(d,e,f) from t ... ) as s

> [...]
> So, I was able to properly define and create the UDT and the function, but have trouble
invoking it.
> What I’m attempting to do is:
> select a,b,c,
>   JAVA_FUNCTION(d,e,f,USER_DEFINED_TYPE(COLUMN00, ... , COLUMN89)) as col_value
>   ...
> But doing so causes the following error:
> java.sql.SQLSyntaxErrorException: 'USER_DEFINED_TYPE' is not recognized as a function
or procedure.

A user defined type cannot be called as a function. To create instances
of the UDT using SQL, you'd have to create another function that returns
objects of that type. Something like this:

    public static MyUDT create(int col00, ... , int col89) {
        return new MyUDT(col00, ... , col89);

    create function create_object(col00 int, ... , col89 int)
           returns my_udt
           language java parameter style java external name ...

And call that new function in the query:

    select a,b,c,
      JAVA_FUNCTION(d,e,f,CREATE_OBJECT(COLUMN00, ... , COLUMN89)) as col_value

You may also be interested in DERBY-3069 (support for vararg functions)
planned for Derby 10.10, which may provide another way to work around
the limitation.

Hope this helps,

Knut Anders

View raw message