CODEWITHSUNDEEP
javahibernatehsqldb
Andrew Grothe
Andrew Grothe

Reputation: 2374

HSQLDB Unexpected token: GETDATE

JPA & Hibernate application uses HSQLDB for JUnit tests.

While the tests worked fine against an Oracle database, getting the following error now that we are using MSSQL 2016:

Unexpected token: GETDATE in statement [select ..... effective_date < GETDATE() AND ... ]

So I understand that HSQL uses SYSDATE, CURDATE, or NOW instead of the MSSQL GETDATE function, and I've done the following:

Attempted to set sql.syntax_mss to true by URL and SQL statement:

public static final String HYPERSONIC_JDBC_URL = "jdbc:hsqldb:mem:aname;sql.syntax_mss=true";

entityManager.createNativeQuery("set database sql syntax MSS true").executeUpdate();

Register the function in the Dialect and/or create a function:

registerFunction("GETDATE", new NoArgSQLFunction("SYSDATE", new DateType()));

entityManager.createNativeQuery("CREATE FUNCTION GETDATE() RETURNS DATE RETURN CURDATE()").executeUpdate();

None of this seems to have any effect.

Live application is connected to MS SQL Server 2016 via mssql-jdbc-6.2.1.jre7 driver.

Upvotes: 1

Views: 1195

Answers (1)

Andrew Grothe
Andrew Grothe

Reputation: 2374

So while upgrading the libraries as @fredt mentioned in the comments is probably the best route, upgrading hsqldb.jar breaks dbunit.jar, upgrading that breaks.. etc.

Was able to remove the GETDATE occurrences and fix the sequence generation strings (select next value for seq_name from seq_name is the format it likes) and now we are back to a working state.

Upgrading these libraries can be put in the bucket with upgrading jBoss, Hiberate, jBPM and Quartz :)

Upvotes: 0

Related Questions