[h2] Re: H2 - Throwing Syntax Error for Create Alias

[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





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-') 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(".MM.dd").parse(s);}
>> $$;
>>
>> create alias TO_DATE as [*]$$ java.util.Date toDate(String s,String 
>> format) throws Exception { return new 
>> java.text.SimpleDateFormat("".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(".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("".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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: H2 - Throwing Syntax Error for Create Alias

[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-') 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(".MM.dd").parse(s);}
> $$;
>
> create alias TO_DATE as [*]$$ java.util.Date toDate(String s,String 
> format) throws Exception { return new 
> java.text.SimpleDateFormat("".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(".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("".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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: H2 - Throwing Syntax Error for Create Alias

[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

-- 
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.