Hi,
i am using a custom Order bean to be able to use xquery on an DB2 database for sorting purposes. Below is the bean:
Code:
package de.uta.wws.kibr.monitoring.dataAccess.dao;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Order;
public class OrderBySqlFormula extends Order
{
private static final long serialVersionUID = 1L;
private String sqlFormula;
/**
* Constructor for Order.
* @param sqlFormula an SQL formula that will be appended to the resulting SQL query
*/
protected OrderBySqlFormula(String sqlFormula)
{
super(sqlFormula, true);
this.sqlFormula = sqlFormula;
}
public String toString()
{
return sqlFormula;
}
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException
{
return sqlFormula;
}
/**
* Custom order
*
* @param sqlFormula an SQL formula that will be appended to the resulting SQL query
* @return Order
*/
public static Order sqlFormula(String sqlFormula)
{
return new OrderBySqlFormula(sqlFormula);
}
}
Now i want to order my results based on a specific value in a XML column.
Below is the code that Hibernate produces:
Code:
select this_."IDN" as y0_ from "EAI"."MonitoringRequest" this_ order by XMLCAST(XMLQUERY('$d/EAI/Karte/KundenNummer' PASSING this_."MessageBody" AS "d") AS INTEGER) asc
If just paste this query into SQuirreL or execute it directly on an existing DataSource everything works fine. However, when hibernate tries to execute the code above, i am getting the following error:
Code:
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-214, SQLSTATE=42822, SQLERRMC=XMLQUERY/XMLEXISTS;ORDER BY;6, DRIVER=3.61.75
at com.ibm.db2.jcc.am.ed.a(ed.java:676)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:127)
at com.ibm.db2.jcc.am.gn.c(gn.java:2554)
at com.ibm.db2.jcc.am.gn.d(gn.java:2542)
at com.ibm.db2.jcc.am.gn.a(gn.java:2034)
at com.ibm.db2.jcc.am.hn.a(hn.java:6500)
at com.ibm.db2.jcc.t4.cb.g(cb.java:140)
at com.ibm.db2.jcc.t4.cb.a(cb.java:40)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
at com.ibm.db2.jcc.am.gn.gb(gn.java:2005)
at com.ibm.db2.jcc.am.hn.qc(hn.java:3053)
at com.ibm.db2.jcc.am.hn.b(hn.java:3838)
at com.ibm.db2.jcc.am.hn.dc(hn.java:683)
at com.ibm.db2.jcc.am.hn.executeQuery(hn.java:657)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:1099)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:720)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 97 more
Error 214: http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.codes%2Fsrc%2Ftpc%2Fn214.htm
It says "AN EXPRESSION IN THE FOLLOWING POSITION, OR STARTING WITH position-or-expression-start IN THE clause-type CLAUSE IS NOT VALID. REASON CODE = reason-code"
with reason-code 6 meaning "Invalid use of scalar-fullselect. This reason code can be issued when the RETURN statement of an SQL function contains a scalar-fullselect, or a scalar-fullselect is passed as an argument on a CALL statement for a parameter that is defined as an input parameter (IN)."
At first this would seem to be a DB2 specific problem but the query works fine outside of hibernate.
What i am doing wrong?
/edit: We are using Hibernate 3.0...
Login
FAQ
Search