I've got a problem with an hibernate generated SQL query :
it appears with selects on a class with <any > tag.
The generated SQL doesn't work with Oracle, see details below.
Hibernate version: 3.0.5
Using org.hibernate.dialect.OracleDialect
Mapping documents:
Code:
<class name="Transition"
table="HISTORIQUECN">
<id name="id" column="id" type="long">
<generator class="sequence">
<param name="sequence">SEQ_HISTORIQUECN</param>
</generator>
</id>
<any name="transitable" id-type="long" meta-type="string">
<meta-value value="CLT" class="Client" />
<meta-value value="CTR" class="Contrat" />
<meta-value value="PRO" class="PropositionCommerciale" />
<column name="type" />
<column name="transitable" />
</any>
<property name="dateOperation" column="DATECHANGEMENT" type="date" />
<many-to-one name="etatAvant" column="ETATAVANT" class="Etat" cascade="none" />
<many-to-one name="etatApres" column="ETATAPRES" class="Etat" cascade="none" />
<property name="commentaire" column="COMMENTAIRE" type="string" />
<many-to-one class="Personne" name="personne" column="PERSONNE" />
</class>
Code:
String query = "select t from Transition t where t.transitable = :transitable order by t.dateOperation desc";
Query hbQuery = session.createQuery(query);
hbQuery.setParameter("transitable", transitable);
Collection result = hbQuery.list();
Full stack trace of any exception that occurs:a bunch of exceptions due to a java.sql.SQLException
Name and version of the database you are using: Oracle 8.1.7.4.1The generated SQL (show_sql=true):Code:
select transition0_.id as id, transition0_.type as type8_, transition0_.transitable as transita3_8_, transition0_.DATECHANGEMENT as DATECHAN4_8_, transition0_.ETATAVANT as ETATAVANT8_, transition0_.ETATAPRES as ETATAPRES8_, transition0_.COMMENTAIRE as COMMENTA7_8_, transition0_.PERSONNE as PERSONNE8_
from HISTORIQUECN transition0_
where (transition0_.type, transition0_.transitable)=? order by datec desc
If, at first sight the query doesn't looks bad, the problem resides in the where clause, Oracle doesn't like the "(field1, field2) = ?" syntax. It gives me an ORA-00920 error : invalid relational operator
If I execute the sql with example values, I get the same error
Code:
select transition0_.id as id, transition0_.type as type8_, transition0_.transitable as transita3_8_, transition0_.DATECHANGEMENT as DATECHAN4_8_, transition0_.ETATAVANT as ETATAVANT8_, transition0_.ETATAPRES as ETATAPRES8_, transition0_.COMMENTAIRE as COMMENTA7_8_, transition0_.PERSONNE as PERSONNE8_
from HISTORIQUECN transition0_
where (transition0_.type, transition0_.transitable)=('CTR', 49006) order by datec desc
but if I add parenthesis, it works :
Code:
select transition0_.id as id, transition0_.type as type8_, transition0_.transitable as transita3_8_, transition0_.DATECHANGEMENT as DATECHAN4_8_, transition0_.ETATAVANT as ETATAVANT8_, transition0_.ETATAPRES as ETATAPRES8_, transition0_.COMMENTAIRE as COMMENTA7_8_, transition0_.PERSONNE as PERSONNE8_
from HISTORIQUECN transition0_
where (transition0_.type, transition0_.transitable)=(('CTR', 49006)) order by datec desc
Perhaps, the SQL generator should be updated ?
Login
FAQ
Search