Problem embedded-database DATE_SUB HSQL

[Supercarlillos]

Hi:
I have the next problem when I try to ejecute the test of my aplication.
This is my configuration :

<jdbc:embedded-database id="embeddedDataSource" type="HSQL">
		<jdbc:script location="classpath:script/schema.sql" />
</jdbc:embedded-database>

schema.sql

SET DATABASE SQL SYNTAX MYS TRUE;

CREATE TABLE EVENTS (
		id bigint not null auto_increment, 
		count_id varchar(255) not null, 
		creation_date DATETIME not null, 
		sent_date DATETIME, 
		PRIMARY KEY (id));

I'm using the next update:

String query = "UPDATE notifications SET status = ?, sent_date = NOW() "
				+ "WHERE sent_date <= DATE_SUB(NOW(), INTERVAL ? MINUTE) AND status = ?";

The problem is in the function sql DATE_SUB the system traces the next error

Caused by: java.sql.SQLSyntaxErrorException: usuario no tiene privilegios suficientes o objeto no encontrado: DATE_SUB
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unkno wn Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Un known Source)
at org.springframework.jdbc.core.JdbcTemplate$SimpleP reparedStatementCreator.createPreparedStatement(Jd bcTemplate.java:1375)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:580)
... 36 more
Caused by: org.hsqldb.HsqlException: usuario no tiene privilegios suficientes o objeto no encontrado: DATE_SUB
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ParserDQL.readColumnOrFunctionExpressio n(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPri mary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionP rimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpre ssion(Unknown Source)
at org.hsqldb.ParserDQL.XreadRowOrCommonValueExpressi on(Unknown Source)
at org.hsqldb.ParserDQL.XreadRowValuePredicand(Unknow n Source)
at org.hsqldb.ParserDQL.XreadPredicateRightPart(Unkno wn Source)
at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unk nown Source)
at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unkn own Source)
at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknow n Source)
at org.hsqldb.ParserDQL.XreadBooleanValueExpression(U nknown Source)
at org.hsqldb.ParserDML.compileUpdateStatement(Unknow n Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)


Somebody know if it possible uses this function???

tks

[Marten Deinum]

Somebody know if it possible uses this function???

The stacktrace obviously tells you it isn't... Switching on MySQL support only does so for some specifics for column definitions not the sql dialect. So if you want to fully test native queries use the database you are on.