H2 - Throwing Syntax Error for Create Alias

[Kunal Kishan]

When running CREATE SQL Query as below from a ".sql" file , Syntax error is thrown,. However if i run the same sql on the console it runs fine.

create alias TO_DATE as $$

 java.util.Date toDate(String s,String format) throws Exception {

  return new java.text.SimpleDateFormat("yyyy.MM.dd").parse(s);}

$$;

create alias TO_DATE as [*]$$ java.util.Date toDate(String s,String format) throws Exception { return new java.text.SimpleDateFormat(""yyyy.MM.dd"").parse(s)" [42000-177]

at org.h2.message.DbException.getSyntaxError(DbException.java:190)

if i change this query to below in .sql file : 

create alias TO_DATE as '$$'

 java.util.Date toDate(String s,String format) throws Exception {

  return new java.text.SimpleDateFormat("yyyy.MM.dd").parse(s);}

'$$';

It throws an error : 

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "create alias TO_DATE as '$$' java.util.Date toDate(String s,String format) throws Exception { return new java.text.SimpleDateFormat(""yyyy.MM.dd"").parse(s[*])" [42000-177]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)

at org.h2.message.DbException.get(DbException.java:178)

at org.h2.message.DbException.get(DbException.java:154)

Whats the issue here and how to resolve it ?

[Noel Grandin]

On 2014-07-02 06:31 AM, Kunal Kishan wrote:
> When running CREATE SQL Query as below from a ".sql" file , Syntax error is thrown,. However if i run the same sql on
> the console it runs fine.
>

How are you running it from a .sql file?

[Sam Blume]

Hi 

FYI; I've contributed the TO_DATE (and TO_TIMESTAMP) code to H2 and it should be in Version 1.4.191 (It's not mentioned in the change log :-( ). 

Note: If you have programed an alias with that name, you need to delete that alias first or H2 will complain of having a name clash.

Cheers Sam

[Bret Calvey]

Hi,

What exactly is going on with this?

We were using an older version of H2.

We had a table that stored a day, month and year in separate fields and had a query like this to turn the three fields into a Java date...

SELECT TO_DATE(day || '-' || month || '-' || year, 'dd-MM-yyyy') FROM WhateverTheTableIsCalled....

The TO_DATE we have is defined as...

CREATE ALIAS IF NOT EXISTS TO_DATE AS $$ java.util.Date to_date(String value, String format) throws java.text.ParseException { java.text.DateFormat dateFormat = new  java.text.SimpleDateFormat(format); return dateFormat.parse(value); }$$;

When running this on the older version, we noticed that it would always return the first date value from the first row in the result set for all of the records. So say the first record had the date "01-01-2017", all of the other records would return "01-01-2017" even if they had totally different values.

So I thought "this must be a bug" and went on to try and upgrade to the latest version.

After upgrading, when creating the alias (suing CREATE ALIAS IF NOT EXISTS...) , we get this error...

org.h2.jdbc.JdbcSQLException: Function alias "TO_DATE" already exists;

If I try and drop the alias, I get...

sql> DROP ALIAS TO_DATE;
Error: org.h2.jdbc.JdbcSQLException: Function alias "TO_DATE" not found; SQL statement:
DROP ALIAS TO_DATE [90077-190]
sql>

So it looks like the latest version is in a bit of a mess...

I'm starting to think that I probably should start looking for an alternative to H2 :(

[Steve McLeod]

Here's what happened:

1) You created a database with an older H2 jar and added a user-defined function (UDF)

2) You updated your H2 jar to a newer version that has a built-in function that has the same name as your user-defined function

3) Now H2 gets a bit confused at start up because it tries to recreate your user-defined function but finds the the name conflict.

I've experienced a similar problem. My (not ideal) solution was thus:

1) Use the older H2

2) Create a new UDF MY_TO_DATE with a name that doesn't conflict

3) Port my code to use the new UDF

4) Drop the old UDF TO_DATE

5) Upgrade to the new H2 jar

6) If the new TO_DATE built-in function achieves what you want, port your code again to use built-in function instead of the UDF.

You can simplify this depending on how much direct access you have to the end user environment