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 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.
