I have been trying to add a custom SQLFunction to handle a date diff. This is just like the issue here
https://hibernate.onjira.com/browse/HHH-2434. And as it suggests I have created a custom dialect to calculate these. Our product has to support Oracle, DB2 and MSSQL. MSSQL and Oracle work great in all the test cases I have so far. DB2 works fine as long as the arguments come from the database and not from a bind variable.
Here is the DB2 dialect that I have written.
Code:
public class CustomDB2Dialect extends DB2Dialect {
public CustomDB2Dialect() {
super();
registerFunction("date_diff_seconds", new SQLFunctionTemplate(
StandardBasicTypes.LONG,
"(days(cast(?2 as TIMESTAMP)) - days(cast(?1 as TIMESTAMP))) * 86400" +
" + (midnight_seconds(cast(?2 as TIMESTAMP)) - midnight_seconds(cast(?1 as TIMESTAMP)))"));
}
}
Here is the pertinent section of the test code. I am using the test_date column from the database and trying to do a diff based on a passed in timestamp.
Code:
Query query = session.createQuery(
"select date_diff_seconds(test_date, ?) from HibernateCustomization");
query.setTimestamp(0, now.toDate());
Iterator iter = query.iterate();
Here is the trace that is output when the error occurs. The translation works fine, but due to having referencing the variable more than once in the sql function only the first reference gets bound causing DB2 to puke saying that not all positions were bound.
Quote:
1 ions D 08:17:10,797 (QueryTrans) [0] HQL: select date_diff_seconds(test_date, ?) from com.redprairie.moca.db.hibernate.HibernateCustomization
1 ions D 08:17:10,797 (QueryTrans) [0] SQL: select (days(cast(? as TIMESTAMP)) - days(cast(test_date as TIMESTAMP))) * 86400 + (midnight_seconds(cast(? as TIMESTAMP)) - midnight_seconds(cast(test_date as TIMESTAMP))) as col_0_0_ from test_hibernate_table hibernatec0_
1 ions D 08:17:10,798 (ErrorCount) [0] throwQueryException() : no errors
1 ions T 08:17:10,798 (HQLQueryPl) [0] HQL param location recognition took 0 mills (select moca_util.date_diff_seconds(test_date, ?) from HibernateCustomization)
1 ions T 08:17:10,798 (HQLQueryPl) [0] iterate: select moca_util.date_diff_seconds(test_date, ?) from HibernateCustomization
1 ions T 08:17:10,798 (QueryParam) [0] parameters: [Thu Aug 11 08:17:10 CDT 2011]
1 ions T 08:17:10,798 (QueryParam) [0] named parameters: {}
1 ions D 08:17:10,798 (AbstractBa) [0] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
1 ions D 08:17:10,798 (SQL ) [0] select (days(cast(? as TIMESTAMP)) - days(cast(test_date as TIMESTAMP))) * 86400 + (midnight_seconds(cast(? as TIMESTAMP)) - midnight_seconds(cast(test_date as TIMESTAMP))) as col_0_0_ from test_hibernate_table hibernatec0_
1 ions T 08:17:10,798 (AbstractBa) [0] preparing statement
1 ions T 08:17:10,801 (BasicBinde) [0] binding parameter [1] as [TIMESTAMP] - Thu Aug 11 08:17:10 CDT 2011
1 ions T 08:17:10,802 (Loader ) [0] Bound [2] parameters total
1 ions D 08:17:10,875 (AbstractBa) [0] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
1 ions T 08:17:10,875 (AbstractBa) [0] closing statement
com.ibm.db2.jcc.am.SqlException: The number of variables in the EXECUTE statement, the number of variables in the OPEN statement, or the number of arguments in an OPEN statement for a parameterized cursor is not equal to the number of values required.. SQLCODE=-313, SQLSTATE=07001, DRIVER=4.8.87
at com.ibm.db2.jcc.am.gd.a(gd.java:679)
Is there any way to use a bind variable with a custom function that uses that variable more than once? The SQLFunction interface really has no way that I see to control the behavior of binding, just the translation of the statement.
The only thing I can find that is somewhat acceptable to workaround this is adding a stored procedure for DB2. But that is not really what we want to do to get this working.
Any help or feedback would be greatly appreciated.
edit: forgot to mention this is using hibernate 3.6.0.
Login
FAQ
Search