CODEWITHSUNDEEP
javaoracle-databasejunith2
goFlowYourself
goFlowYourself

Reputation: 1

H2 drop and create alias to_char not working

I've been trying to run a few unit tests that use the to_char function that works for our oracle database, but for our h2 in memory database for testing doesn't seem to work. I try to drop the alias to_char and override it for one that works, but when i try to drop the alias to_char and create it it doesn't seem to work.

DROP alias if exists TO_CHAR;
CREATE alias TO_CHAR FOR "package.TEST.toChar";

Results in: "org.h2.jdbc.JdbcSQLException: Function alias "TO_CHAR" already exists; SQL statement:"

DROP alias TO_CHAR;
CREATE alias TO_CHAR FOR "package.TEST.toChar";

Results in: "org.h2.jdbc.JdbcSQLException: Function alias "TO_CHAR" not found; SQL statement:".

Also tried with a direct sql script as follows, but doesn't work:

DROP alias if exists TO_CHAR;
CREATE alias TO_CHAR as '
public static String toChar(String date, String pattern) throws Exception {
        pattern = pattern.replaceAll("YY","yy");
        pattern = pattern.replaceAll("DD","dd");
        pattern = pattern.replaceAll("HH24|hh24","HH");
        pattern = pattern.replaceAll("HH?!24|hh?!24","KK");
        pattern = pattern.replaceAll("MON|mon","MMM");
        pattern = pattern.replaceAll("MI|mi","mm");
        pattern = pattern.replaceAll("SS|ss","ss");
        pattern = pattern.replaceAll("AM|PM","aa");
        pattern = pattern.replaceAll("T","");
        SimpleDateFormat sm = new SimpleDateFormat(pattern);
        java.util.Date dt;
        if(date.length() > 10)dt = java.sql.Timestamp.valueOf(date);
        else
            dt = java.sql.Date.valueOf(date);
        return sm.format(dt);
    }';

I thought it could be lack of permissions but it doesn't work with user="sa", also looked up and they might not allow overriding the to_char function as someone stated that they don't allow to override some functions: http://h2-database.66688.n3.nabble.com/trunc-alias-is-read-only-and-avoids-emulating-Oracle-trunc-function-td4025947.html

Does anyone know what i'm missing here or is it not possible to override it?

Upvotes: 0

Views: 2752

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

If you really want to redefine that function with your own implementation, you need to add ;BUILTIN_ALIAS_OVERRIDE=1 to your database URL.

Upvotes: 2

Related Questions