I need to create a dynamic query based on the some condition. I don't want to hardcode this query in my java class.
So that's the reason i want to get this using Named query in hibernate.
Below is how iam trying to implement.

DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String today = sdf.format(date);
Query query = session.getNamedQuery("findDateDifference");
query.setString(1, today);
List date_Diff_List = query.list();

<sql-query name="findDateDifference"><![CDATA[
select (? - temp.bv_date) as days from
( select CLO_DTE as bv_date from BRAND ) temp]]>
</sql-query>

I need to pass the value to the '?' which is given in above query.
When I run the above code it gives me error as below.

Can anyone please give me some suggestions how to fix this issue.


Hibernate:
select (? - temp.bv_date) as days from
( select CLO_DTE as bv_date from BRAND ) temp

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1502)
at org.hibernate.loader.Loader.list(Loader.java:1482)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1333)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:146)
at com.hibernate.ExceptionTest.getDateDifference(ExceptionTest.java:219)
at com.hibernate.ExceptionTest.main(ExceptionTest.java:43)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -171, SQLSTATE: 42815, SQLERRMC: 2;-
at com.ibm.db2.jcc.a.hd.e(hd.java:1659)
at com.ibm.db2.jcc.a.hd.a(hd.java:1235)
at com.ibm.db2.jcc.a.hd.a(hd.java:1221)
at com.ibm.db2.jcc.c.jb.h(jb.java:142)
at com.ibm.db2.jcc.c.jb.a(jb.java:43)
at com.ibm.db2.jcc.c.w.a(w.java:30)
at com.ibm.db2.jcc.c.cc.g(cc.java:160)
at com.ibm.db2.jcc.a.hd.n(hd.java:1215)
at com.ibm.db2.jcc.a.id.gb(id.java:1780)
at com.ibm.db2.jcc.a.id.d(id.java:2255)
at com.ibm.db2.jcc.a.id.X(id.java:505)
at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:107)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1183)
at org.hibernate.loader.Loader.doQuery(Loader.java:363)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203)
at org.hibernate.loader.Loader.doList(Loader.java:1499)
... 6 more

Hi,
Can anyone please responed to this issue. Its urgent.
Thanks in advance.

Dunno, what is "Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -171, SQLSTATE: 42815, SQLERRMC: 2;-"?

Hibernate is trying to bind the values where do you have where condition in your SQL.
For example
select emp_id,empname,status,department from emp where emp_id = ?

Query query = session.getNamedQuery("findEmp");
query.setInteger(1, 12);
List results = query.list();

As per the above condition it work perfect. But when iam trying to bind the values in some other place like the below, it gives me the problem

Query query = session.getNamedQuery("findDateDifference");
query.setString(1, today);
List date_Diff_List = query.list();

<sql-query name="findDateDifference"><![CDATA[
select (? - temp.bv_date) as days from
( select CLO_DTE as bv_date from BRAND ) temp]]>
</sql-query>

you are still not telling us what that db2 error means. I am not a db2 user nor am I going to google/doc it for you. After we know what the db2 error means we can probably help you.

I am actually surprised the query parses at all. Db2 is very touchy about parameters in the select clause

Iam not sure what does that error mean.
I think it is not able to parse the SQL query which is generated.
Any idea

http://www.sqlerror.de/db2_sql_error_-1 ... 42815.html

I think you'd better post in a DB2 forum to get help writing a correct SQL statement.

Thanks for the reply.

But when i tried to execute this query on DB2 it works fine. It doesn't give me any problem.
I tried as following

select (Current Date - temp.bv_date) as days from
( select CLO_DTE as bv_date from BRAND ) temp


select ('2010-02-15' - temp.bv_date) as days from
( select CLO_DTE as bv_date from BRAND ) temp

Both of these queries works fine.

So i believe it is not the problem with DB2. This should be something which Hibernate is not trying to recognize the query.

But same query when executed with

Query date_diff_Query = session.createSQLQuery("select (? - temp.bv_date) as days from
( select CLO_DTE as bv_date from BRAND ) temp" );
date_diff_Query.setString(0, today);

It works perfect. So its only problem with the Named Query.

Thanks

Any idea on this...
Please reply.