Question

I m trying to spot-test an Oracle backed database with hsqldb and dbunit, but I ve run into a snag.

The problem is with the following EJB-QL (simplified a bit):

SELECT o 
FROM Offer o 
WHERE :nowTime 
  BETWEEN o.startDate AND o.startDate + 7

This seems to only work in Oracle s version of SQL.

What s the easiest way for me to convert this to work in both hsqldb and oracle? Assume that changing the two between arguments to named parameters is a very difficult refactor, so I m going to favor answers that provides a more standardized analog to

o.startdate + 7

EDIT: After doing some more research, it looks like Oracle converts the above snippet to

o.startdate + INTERVAL  7  DAY

which is apparently more standard, but doesn t work in HSQLDB.

Answer 1

HSQLDB 2.0 (released in 2010) supports the syntax.

SELECT o 
FROM Offer o 
WHERE nowTime 
  BETWEEN o.startDate AND o.startDate + 7 DAY

It also suppports

o.startdate + INTERVAL  7  DAY

Update:

In ORA compatibility mode introduced since HSQLDB 2.2, the expression o.startDate + 7 is also supported.

Answer 2

Your methodology seems correct. I don t know what you re having trouble with. This is working in Oracle and HSQLDB using the script below:

CREATE TABLE OFFER (ID INTEGER, STARTDATE DATE);
INSERT INTO OFFER (ID, STARTDATE) VALUES (1, DATE  2009-01-01 );
SELECT ID, STARTDATE, STARTDATE + INTERVAL  7  DAY FROM OFFER;

In both environments I get the results showing the first and the eighth of January.

Answer 3

The HSQLDB syntax for date manipulation is quite different from Oracle. Your best chance is to write a stored procedure in the Oracle DB which simulates the behavior of the HSQLDB functions/procedures.

友情链接