Updating existing SQL query to support H2 database

[GoogleCodeExporter]

I have decided to modify the Sun's BPEL-SE source code to support H2
Database. Once it works, I will send a patch back to BPEL-SE dev team and I
can use for our product. 

I need your help to create following three query compatible to H2 DB
format. Below I have given sample queries from Derby and Oracle for your
reference. 

Derby: 
String BASE_INSERT_STMT_STR = "INSERT INTO " +
PersistenceDBSchemaCreation.ENGINE + 
"VALUES(?, ?, CURRENT_TIMESTAMP)";  

String BASE_UPDATE_STMT_STR = "UPDATE " +
PersistenceDBSchemaCreation.ENGINE +  
"SET lastupdatetime = CURRENT_TIMESTAMP " + "WHERE engineid = ?";  

String BASE_UPDATE_STMT_STR = "update " + PersistenceDBSchemaCreation.STATE
+ " set engineid = ? " + " where (status = '" + StateDBO.RUNNING_STATUS +
"' or status = '" + StateDBO.SUSPENDED_STATUS 
+ "') and engineid IN (select engineid from " +
PersistenceDBSchemaCreation.ENGINE 
+ " where {fn TIMESTAMPDIFF(SQL_TSI_SECOND, timestamp(lastupdatetime),
CURRENT_TIMESTAMP)} > ? / 1000 " + " and engineid != ?) ";  

Oracle: 

String ORCL_INSERT_STMT_STR = "INSERT INTO " +
PersistenceDBSchemaCreation.ENGINE + " VALUES(?, ?, sysdate)";  

String ORCL_UPDATE_STMT_STR = "UPDATE " +
PersistenceDBSchemaCreation.ENGINE + " SET lastupdatetime = sysdate " +
"WHERE engineid = ? ";  

String ORCL_UPDATE_STMT_STR = "update " + PersistenceDBSchemaCreation.STATE
+ " set engineid = ? " + " where (status = '" + StateDBO.RUNNING_STATUS +
"' or status = '" + StateDBO.SUSPENDED_STATUS  
+ "') and engineid IN (select engineid from " +
PersistenceDBSchemaCreation.ENGINE  
+ " where ((sysdate - lastupdatetime) * 86400) > (? / 1000) " + " and
engineid != ?) " + " and rownum < (? + 1)";  

Thanks, 

Original issue reported on code.google.com by rohit.c.joshi@gmail.com on 24 Apr 2009 at 9:34

[GoogleCodeExporter]

Hi,

Please only file bugs in the issue tracker. This is not a bug, it is a support 
request.

H2 doesn't support TIMESTAMPDIFF, but it support DATEDIFF. See also:
http://www.h2database.com/html/functions.html#datediff

This might work:
select datediff(S, d, CURRENT_TIMESTAMP()) from test;

CURRENT_TIMESTAMP and sysdate are supported.

Original comment by thomas.t...@gmail.com on 27 Apr 2009 at 6:25

• Changed state: Invalid