Hello again
Sorry, I forgot to post the Hibernate version I'm using:
Hibernate 3.0,5
After patching the method AbstractQueryImpl.verifyParameters(...) so that it runs through, Hybernate produces some wired SQL-Statements, that can not be executed by oracle.
So my guess is:
Using hibernate objects with combined primary keys in the where clause of HQL is not supported at the moment.
Is this the case?
Regards Matthias Gerber
HQL Example and resulting SQL:
HQL:
SELECT i FROM ch.slf.sdbobj.ams.Imis150 as i WHERE
i.id.standort in (?) AND i.id.datum = ( SELECT MAX(i1.id.datum) FROM ch.slf.sdbobj.ams.Imis150 as i1 WHERE
i1.id.standort = i.id.standort)
SQL:
select . . . where
((imis150x0_.STAT_ABK, imis150x0_.STAO_NR)in (?)) and imis150x0_.DATUM=(select MAX(imis150x1_.DATUM) from IMIS150 imis150x1_ where
(imis150x1_.STAT_ABK, imis150x1_.STAO_NR)=(imis150x0_.STAT_ABK, imis150x0_.STAO_NR))
results in java.sql.SQLException: ORA-01036: Illegal Parameter Name/Value.
HQL:
SELECT i FROM ch.slf.sdbobj.ams.Imis150 as i WHERE
i.id.standort = ? AND i.id.datum = ( SELECT MAX(i1.id.datum) FROM ch.slf.sdbobj.ams.Imis150 as i1 WHERE
i1.id.standort = i.id.standort)
SQL:
select . . . from IMIS150 imis150x0_
where
(imis150x0_.STAT_ABK, imis150x0_.STAO_NR)=? and imis150x0_.DATUM=
(select MAX(imis150x1_.DATUM) from IMIS150 imis150x1_
where
(imis150x1_.STAT_ABK, imis150x1_.STAO_NR)=(imis150x0_.STAT_ABK, imis150x0_.STAO_NR))
results in java.sql.SQLException: ORA-01036: Illegal Parameter Name/Value.
SQL, As it would work:
select . . . from IMIS150 imis150x0_
where
(imis150x0_.STAT_ABK, imis150x0_.STAO_NR) = (SELECT ?, ? FROM DUAL) and imis150x0_.DATUM=
(select MAX(imis150x1_.DATUM) from
IMIS150 imis150x1_
where imis150x1_.STAT_ABK = imis150x0_.STAT_ABK AND imis150x1_.STAO_NR = imis150x0_.STAO_NR)
Patched method AbstractQueryImpl.verifyParameters(...):
Code:
protected void verifyParameters(boolean reserveFirstParameter) throws HibernateException {
if ( actualNamedParameters.size() != namedParameters.size() + namedParameterLists.size() ) {
Set missingParams = new HashSet(actualNamedParameters);
missingParams.removeAll( namedParameterLists.keySet() );
missingParams.removeAll( namedParameters.keySet() );
throw new QueryException( "Not all named parameters have been set: " + missingParams, getQueryString() );
}
int positionalValueSpan = 0;
int positionalValueCount = 0; // added
for ( int i=0; i<values.size(); i++ ) {
Object object = types.get(i);
if( values.get(i)==UNSET_PARAMETER || object==UNSET_TYPE ) {
if(reserveFirstParameter && i==0) {
continue;
} else {
throw new QueryException( "Unset positional parameter at position: " + i, getQueryString() );
}
}
positionalValueSpan += ( (Type) object ).getColumnSpan( session.getFactory() );
positionalValueCount++; // added
}
// if ( positionalParameterCount!=positionalValueSpan ) { // removed
// if(reserveFirstParameter && positionalParameterCount-1!=positionalValueSpan) { // removed
if ( positionalParameterCount!=positionalValueCount ) { // added
if(reserveFirstParameter && positionalParameterCount-1!=positionalValueCount) { // added
throw new QueryException(
"Expected positional parameter count: " +
(positionalParameterCount-1) +
", actual parameters: " +
values,
getQueryString()
);
} else if (!reserveFirstParameter) {
throw new QueryException(
"Expected positional parameter count: " +
positionalParameterCount +
", actual parameters: " +
values,
getQueryString()
);
}
}
}