CODEWITHSUNDEEP
javasqlprepared-statementhsqldb
Ricardo Duval
Ricardo Duval

Reputation: 31

HSQLDB 2.3 and issues with parameter binding

I have a production Oracle database and I am using HSQLDB 2.3 as a integration test DB to verify some DAO methods. I notice HSQL does not deal very well with dates and parameter binding from Java's PreparedStatement. For example, the query below works as expected, assuming column2 is a date type:

select
    column1,
    column2
from
    table
where
    trunc(column2) = trunc(sysdate + 14)
    and column3 = ?

But this another query simply does not work when I set an Int, preparedStatement.setInt(1, 14), on the first parameter (?). Needless to say it works perfectly in Oracle.

select
    column1,
    column2
from
    table
where
    trunc(column2) = trunc(sysdate + ?)
    and column3 = ?

Curiously, if you try something like select (sysdate + 14) future from any_single_row_table it works as expected, but it shows the current date if you try select (sysdate + ?) future from any_single_row_table

Why does it behave like this? Is it a bug in HSQL prepared statement implementation? Is there any workaround?

Upvotes: 0

Views: 691

Answers (1)

fredt
fredt

Reputation: 24352

You are using simple date arithmetic which is specific to Oracle and to some extent supported by HSQLDB. You can try an explicit INTERVAL value which is Standard SQL. You should also use the very latest version of HSQLDB (currently 2.3.3 release candidate) as syntax compatibility has evolved in each version:

select
    column1,
    column2
from
    table
where
    trunc(column2) = trunc(sysdate + cast(? as interval day))
    and column3 = ?

Upvotes: 1

Related Questions