Hi people!

We need to use @SQLUpdate (Hibernate Annotations) for update a field with HSQLDB 1.8.0.10 (in components test). The integration is with Oracle database.

The annotation is @SQLUpdate(sql="UPDATE SRS_SOLICITUDES SET fec_recarga = ?). This works ok.

Firstly, when we change "?" by "sysdate": @SQLUpdate(sql="UPDATE SRS_SOLICITUDES SET fec_recarga = SYSDATE). This statament fails in HSQLDB.

Secondly, we try with "today": @SQLUpdate(sql="UPDATE SRS_SOLICITUDES SET fec_recarga = TODAY). And the statement fails too.

Next, we try with a function: public static String getFunctionSysdate() {
StringBuffer insert = new StringBuffer();
insert.append("CREATE ALIAS SYSDATE FOR \"com.tm.sers.base.BaseComponenteTest.sysdate\"");
return insert.toString();
}

The error says:

Caused by: java.sql.SQLException: Wrong data type: java.lang.NumberFormatException at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.setParameter(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.setTimestamp(Unknown Source) at org.apache.commons.dbcp.DelegatingPreparedStatement.setTimestamp(DelegatingPreparedStatement.java:143) at org.hibernate.type.TimestampType.set(TimestampType.java:45) at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:83) at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:60) at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:1932) at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2306)

Here the creation with HSQLDB:

"create table SRS_SOLICITUDES (COD_SOLICITUD numeric(5), NUM_IDENTORIG numeric(11), COD_TIPIDENTORIG varchar(2), " + "COD_ESTADO numeric(2), FEC_RECARGA TIMESTAMP default SYSDATE, NUM_IDENTDEST numeric(11), COD_TIPIDENTDEST varchar(2), " + "imp_recarga DECIMAL(7,2), CANT_REINT numeric(2), canal varchar(20), " + "COD_CLIENTEORIGEN numeric(9), COD_ORIGENRECARGA varchar(2), COD_TIPSOLICITUD varchar(2), " + "IND_COMUNICADA numeric(1), NUM_ABODESTINO numeric(10), NUM_ABOORIGEN numeric(10), " + "NOM_USUARORA varchar(30), COD_CLIENTEDESTINO numeric(9), COD_ERROR varchar(8), cod_centremi numeric(4), num_secuenci numeric(8), letra varchar(1), usuario varchar(20) )"); We try with datetime and didn't works...

Do you know how to use @sqlupdate with sysdate and HSQLDB ? or a suitable workaround to do that ?

Txs!