Greetings!
I'm having trouble getting an HQL IN clause working with a tuple constructor. I have a table with columns ID, FIRSTNAME, and LASTNAME that I have mapped to a bean called Names. I want to search on first AND last name for a couple values, so I wrote a simple query in HQL:
Code:
from Names where ( first, last ) in ( values( 'Joe', 'Cool' ), ( 'Tom', 'Sawyer' ) )
Unfortunately the SQL that Hibernate emits only has the first tuple constructor, e.g. ( 'Joe', 'Cool' ), with a trailing comma which isn't correct SQL syntax. If I only use one tuple constructor the statement works fine, and I've verified manually adding ( 'Tom', 'Sawyer' ) to the statement works ( look below for Hand-tweaked SQL that works ).
Anyone know if this is supposed to work? Are there other techniques that will help accomplish the same thing? Perhaps a workaround?
Thanks for any and all help!
Hibernate version: 3.2.1
Mapping documents:Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="persistence.Names" schema="SCRATCH" table="NAMES">
<id name="id" column="ID"><generator class="identity"/></id>
<property name="first" column="FIRSTNAME"/>
<property name="last" column="LASTNAME"/>
</class>
</hibernate-mapping>
Code:
Query query = session.createQuery( "from Names where ( first, last ) in ( values( 'Joe', 'Cool' ), ( 'Tom', 'Sawyer' ) )" );
ScrollableResults sr = query.scroll();
while ( sr.next() )
{
System.out.println( sr.get( 0 ) );
}
Full stack trace of any exception that occurs:Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query using scroll
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.scroll(Loader.java:2319)
at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:453)
at org.hibernate.hql.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:390)
at org.hibernate.engine.query.HQLQueryPlan.performScroll(HQLQueryPlan.java:245)
at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1206)
at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:67)
at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:57)
at persistence.TestNames.runQuery(TestNames.java:43)
at persistence.TestNames.main(TestNames.java:32)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: );ues('Joe', 'Cool') ,;<row_value_constructor>
at com.ibm.db2.jcc.a.rf.e(rf.java:1680)
at com.ibm.db2.jcc.a.rf.a(rf.java:1239)
at com.ibm.db2.jcc.b.jb.h(jb.java:139)
at com.ibm.db2.jcc.b.jb.a(jb.java:43)
at com.ibm.db2.jcc.b.w.a(w.java:30)
at com.ibm.db2.jcc.b.cc.g(cc.java:160)
at com.ibm.db2.jcc.a.rf.n(rf.java:1219)
at com.ibm.db2.jcc.a.sf.gb(sf.java:1790)
at com.ibm.db2.jcc.a.sf.d(sf.java:2266)
at com.ibm.db2.jcc.a.sf.X(sf.java:508)
at com.ibm.db2.jcc.a.sf.executeQuery(sf.java:491)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
at org.hibernate.loader.Loader.scroll(Loader.java:2284)
... 8 more
Name and version of the database you are using:DB2 v8.2
The generated SQL (show_sql=true):Code:
select
names0_.ID as ID0_,
names0_.FIRSTNAME as FIRSTNAME0_,
names0_.LASTNAME as LASTNAME0_
from
SCRATCH.NAMES names0_
where
(
names0_.FIRSTNAME , names0_.LASTNAME
) in (
values
('Joe', 'Cool') ,
)
Hand-tweaked SQL that works:Code:
select
names0_.ID as ID0_,
names0_.FIRSTNAME as FIRSTNAME0_,
names0_.LASTNAME as LASTNAME0_
from
SCRATCH.NAMES names0_
where
(
names0_.FIRSTNAME , names0_.LASTNAME
) in (
values
('Joe', 'Cool') , ('Tom', 'Sawyer')
)
ID0_ FIRSTNAME0_ LASTNAME0_
----------- -------------------- --------------------
1 Tom Sawyer
1 record(s) selected.
Code:
Code:
Login
FAQ
Search