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
On Thursday, 5 January 2017 16:10:30 UTC+1, Bret Calvey wrote:
>
> 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 :(
>
>
> On Wednesday, July 2, 2014 at 5:31:58 AM UTC+1, 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.
>>
>> 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 ?
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
