Timestamp positional parameter null handling

lrow · **Joined:** Thu Jan 21, 2010 3:20 pm **Posts:** 17

Hi ,
I have a positional parameter timestamp used in DAO layer to be comapred against timsestamp field in database such that all the records greater than that timestamp is returned back. However if there is null I am unable to handle it in HQL. I read HQL materials for null handling but it talks only about null handling for fields of the object and not for positional parameters.

Any pointers ??

Code snippet
---------------------------------------------------- DAO Class -----------------------------------------
Session session = getHibernateTemplate().getSessionFactory()
.getCurrentSession();
Query query = session
.getNamedQuery(getfundedquery);
query.setInteger("dis", districtNumber);
query.setTimestamp("timestamp", timestamp);
--------------------------------------------------------------------------------------------------------

---------------------------------------------------- HBM file -------------------------------------------
<query name="getfundedquery">

Select afd
from
FundedItem afd
where
dis = :dis
and
<< if timestamp is null retrieve all the records else retrive >>
or
transTimestamp > :timestamp

]]>
</query>
--------------------------------------------------------------------------------------------------------

nordborg · **Posted:** Thu Feb 18, 2010 5:15 pm

Try something like:

Code:

where
dis = :dis
and
(transTimestamp > :timestamp or :timestamp is null)

lrow · **Joined:** Thu Jan 21, 2010 3:20 pm **Posts:** 17

It gives following DB2 error :

Dump = com.ibm.db2.jcc.c.SqlException: A STATEMENT STRING TO BE PREPARED INCLUDES PARAMETER MARKERS AS THE OPERANDS OF THE SAME OPERATOR

I guess " :timestamp is null " is the problem as generally positional parameters are on the right side of comparison.

lrow · **Joined:** Thu Jan 21, 2010 3:20 pm **Posts:** 17

Any pointers on this . I guess I might have to handle this in the java code.

nordborg · **Posted:** Mon Feb 22, 2010 4:54 pm

Hmmm... this may be an issue with the database. I just tested a more or less identical query with MySQL and had no problem. Have you tried adding some more parenthesis just to be sure the expressions are evaluated in the correct order?

Code:

where
(dis = :dis)
and
((transTimestamp > :timestamp) or (:timestamp is null))

lrow · **Joined:** Thu Jan 21, 2010 3:20 pm **Posts:** 17

I did above as well .

But it still gives me following DB2 error :

Stack Dump = com.ibm.db2.jcc.c.SqlException: A STATEMENT STRING TO BE PREPARED INCLUDES PARAMETER MARKERS AS THE OPERANDS OF THE SAME OPERATOR

Sorry, for replying late on this. I did solve the earlier problem through Java code though .

lrow · **Joined:** Thu Jan 21, 2010 3:20 pm **Posts:** 17

Any updates ? Anybody.

lrow · **Joined:** Thu Jan 21, 2010 3:20 pm **Posts:** 17

Any pointers on above ??

nordborg · **Posted:** Tue May 18, 2010 5:23 pm

I am not a DB2 user but on a quick search on Google I found this document where the error is mentioned. http://webdocs.caspur.it/ibm/udb-6.1/db2m0/sql0400.htm

From the description I think it is the :timestamp is null part that is the problem. The parameter marker (:timestamp) is the only operand of the is null operator and it seems like DB2 requires that it must be cast to a specific data type. Eg. something like:

Code:

cast(:timestamp as timestamp) is null

I have no idea if this works or not... let's hope for the best. Otherwise... it may be better to have two different queries and decide which one to use depending on if timestamp is null or not.

sanne.grinovero · **Posted:** Tue May 18, 2010 5:59 pm

why don't you check for timestamp==null and then branch for two possible query executions?

lrow · **Joined:** Thu Jan 21, 2010 3:20 pm **Posts:** 17

Hi nordborg,

Great . It works . Thanks a lot.