Hi,

I have a problem with a HQL query:

Query q = session.createQuery("select l1_1.id, l1_1.name from Location as l1_1 where (l1_1.id = :ID) ");
q.setParameter("ID", locationID);

locationID is an Integer variable.

If locationID <> null the query works fine, but if it is null I get the following error:

11:18:15,538 WARN JDBCExceptionReporter:57 - SQL Error: 932, SQLState: HY000
11:18:15,538 ERROR JDBCExceptionReporter:58 - [Oracle JDBC Driver][Oracle]ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

This is the generated sql :
select location0_.id as x0_0_, location0_.name as x1_0_ from core_location location0_ where ((location0_.id=? ))

I'm using:

Hibernate 2.1.7.
Oracle 9
Tomcat 5.5

Many thanks,
Vasile

How is your Location.hbm.xml ?
If you id is defined as an integer, there maybe a problem. Could you post it

seb

This is the mapping file:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="home.Location" table="core_location" lazy="false" discriminator-value="null" >
<cache usage="nonstrict-read-write" />
<id name="id" access="field" type="integer" unsaved-value="null">
<column name="id" sql-type="numeric(10,0)"/>
<generator class="native"/>
</id>
<discriminator not-null="false" length="4"/>
<version name="version" column="vstp" access="field"/>
<!-- Component -->
<component name="address" class="home.Address" >
<property name="line1" type="string" access="field" update="true">
<column name="addressline1" length="40" not-null="false" unique="false" />
</property>

<property name="line2" type="string" access="field" update="true">
<column name="addressline2" length="40" not-null="false" unique="false" />
</property>
</component>
</class>
</hibernate-mapping>

You may have better result if you check nullity in your java code an change your hql with 'id is null' if your parameter is null.

Unfortunately I can not change the query depending on it's params, the same query works fine against SQL Server even if that param is null (doesn't return a record if that param is null)....

I am getting the very same error could anybody tell me how to get rid of it ...

I had a similar problem this morning and was due to me not setting the third parameter to a Hibernate Type i.e. Hibernate.LONG, Hibernate.INTEGER, etc. When you don't do this it won't allow you to try and save a null value for an attribute in your class and gives you that nice oracle error.

Example:
Bad
qry.setParameter("reviewerVal", matter.getReviewer());

Good
qry.setParameter("reviewerVal", matter.getReviewer(), Hibernate.LONG);

So if class A has a relationship with class B inside of it - i couldn't set the class B reference to null and update the row. But when I added the third parameter of Hibernate.LONG to it - it worked fine. In the guts of those user type classes it has the setNull() code that it needs to set the attribute to null.

Hope it helps.