0

In order to use H2 database in our Junit tests instead of calling Oracle, I am blocked on creating aliases on H2 to emulate some Oracle compatibility :

  • I first declared an alias for to_char for date to char conversion : works fine

    create alias TO_CHAR as $$ java.lang.String toChar(java.util.Date date, String format) throws Exception{ ... }$$;

  • Then I try to declare an alias for to_char for number to char conversion : now h2 doesn't accept it : create alias TO_CHAR as $$ java.lang.String toChar(java.lang.Number value){ ... }$$;

It is rejected with the following error message :

org.h2.jdbc.JdbcSQLException: Function alias "TO_CHAR" already exists; SQL statement: create alias TO_CHAR as $$

java.lang.String toChar(java.lang.Number value){ return java.lang.String .valueOf(value); }$$

I also tried to declare the 2 functions in 1 block, like :

create alias TO_CHAR as $$

java.lang.String toChar(int value){ ... }

java.lang.String toChar(java.util.Date date, String format) throws Exception{ ... } $$;

In this case, there are no errors, but only the firest method declared is taken in account.

So, is there any way to declare 2 aliases having the same name but not the same signature ?

Craig
  • 7,471
  • 4
  • 29
  • 46
chburd
  • 4,131
  • 28
  • 33

1 Answers1

2

As for TO_CHAR, H2 support it now, since version 1.3.175 (2013-01-18).

H2 does support function overloading. However, there is a limitation, because declaring such functions as source code is not supported.

You would need to declare the method as follows:

CREATE ALIAS YOUR_METHOD FOR "acme.Function.yourMethod";

This limitation is documented under User Defined Functions as follows:

Method Overloading

Multiple methods may be bound to a SQL function if the class is already compiled and included in the classpath. Each Java method must have a different number of arguments. Method overloading is not supported when declaring functions as source code.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • "Multiple methods may be bound to a SQL function" so I would expect something like CREATE ALIAS YOUR_METHOD FOR "acme.Function.yourMethod","acme.Function.yourMethodOverload1"; – Pieter De Bie Nov 07 '18 at 11:52
  • Method overloading is supported in Java, so I don't think you would need to pass two Java methods. Maybe best ask a new question. – Thomas Mueller Nov 08 '18 at 10:12
  • In java you can overload with the same amount of parameters, for instance `foo(int a, int b)` and `foo(String a, String b)`. H2 does not seem to support this and does not support `foo(Object a, Object b)` . H2 does however have a "Value" class that can be used for this. – Pieter De Bie Nov 08 '18 at 10:46