DATEDIFF H2 and MSSQL compatibility

[Jean-Philippe Prade]

Hello

I am using SQL server for my application and H2 for my test unit, 

The problem is that the syntax in MSSQL use double quote " and simple quote ' in H2

In my entity I have a column like this :

@Formula("DATEDIFF(\"DAY\", GETDATE(), contract_date_end)")
private String difference;

this works in MSSQL but not in H2  :

2014-07-18 09:25:28,606: ERROR [org.hibernate.util.JDBCExceptionReporter](?:?)  - Column "CONTRACT0_.DAY" not found; SQL statement:

select ... DATEDIFF(contract18_."DAY", GETDATE(), contract18_.contract_date_end) as formula4_17_ .... from ....

It considers "DAY" as a column...

@Formula("DATEDIFF('DAY', GETDATE(), contract_date_end)")
private String difference;

this works in H2 and not MSSQL.

But I am using H2 in MSSQL mode  :

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSourceH2">
    <property name="driverClassName" value="org.h2.Driver"/>
   <property name="url" value="jdbc:h2:mem:unittest;MODE=MSSQLServer"/>
</bean>

So isn't it suppossed to work ?

thanks for help !

[Thomas Mueller]

Hi,

Could you try without double quotes and single quotes? That should work without both MS SQL Server and H2.

@Formula("DATEDIFF(DAY, GETDATE(), contract_date_end)")

Regards,

Thomas

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

[Jean-Philippe Prade]

Just tried sadly hibernate is considering DAY as a column name and add an alias ...

2014-07-18 10:30:02,697: ERROR [org.hibernate.util.JDBCExceptionReporter](?:?)  - Column "CONTRACT0_.DAY" not found; SQL statement:

... DATEDIFF(contract0_.DAY, GETDATE(), contract0_.contract_date_end) as formula4_30_

[Jean-Philippe Prade]

I found a hack

I figured out that MSSQL dialect used [ and ] as quote and  H2 use : " (default)

So I created a custom dialect :

public class H2AHEADDialect extends H2Dialect {


 public H2AHEADDialect(){
 super();
 }
 
 public char closeQuote() {
 return ']';
 }


 public char openQuote() {
 return '[';
 }
 
 
}

Because in org.hibernate.sql.Template it adds the placeholder if the string start by a quote 

if ( isOpenQuote ) {
 result.append( placeholder ).append( '.' );
 }