DB: Oracle 9i

A simple sqlquery is created as following:
<sql-query name="SearchEmployees">
<return alias="e" class="com.gal.fast.common.hib.domain.SfaEmployees"/>
select e.* from employees e where e.last_name = ? order by e.last_name, e.first_name
</sql-query>

The statement
getCurrentSession().getNamedQuery(SearchEmployees).setParameter(0, "Test").scroll();
failed with java.sql.SQLException: ORA-01465: invalid hex number

But when change the statement to
getCurrentSession().getNamedQuery(SearchEmployees).setParameter(0, "Test", Hibernate.STRING).scroll();
it works fine.

Obviously the setParameter(int, object) could not guess the Hibernate type as it should from the object class.

Is this a configuration issue? Thank you advance for your reply.

what type is last_name mapped as ?

varchar2(40 byte)

the hibernate type not the sql type.

<property name="lastName" type="string">
<column name="LAST_NAME" length="40" not-null="true"/>
</property>

But how is it related to the Hibernate type?

From the api doc, it says:"

public Query setParameter(int position,
Object val)
throws HibernateException

Bind a value to a JDBC-style query parameter, guessing the Hibernate type from the class of the given object." , it is assumed that the guessing is ONLY based on the Object type.

hmm the api doc is out of date.

With the new parser we are actually able to deduce what the expected type of the parameter is (in some cases) and uses that before falling back to heuristics based on the type of the object you pass in.

Would be interesting if you could step through the setParameter call and let me know what part of AbstractQueryImpl.determineType that actually gives the type ? (parameterMetadata.getOrdinalParameterExpectedType or guessType)

I'll go and update the javadocs now..

Thank you for your assistance. I will go through the "guessing" and let you know what I find out.

Have not gone to the Hibernate code. But found the determineType method is not fully implemented for native Oracle sql especially when the variables are in plsql functions such as decode

native oracle sql ?! that shouldn't affect our HQL query parsing...what are you referring to ?

According the Hibernate doc, queries within sqlquery tag are consided to be native query, are they not?

ah ..didn't see your sqlquery in the initial question.

so again, what method is actually returning the guess type in your case ?

/max

It looks like the determineType is used to guess the type for the setParameter(int, object) method in sqlquery.

could you create a *minimal* test case that fails and put it in jira ?

i think this is an issue with the oracle drivers and not so much hibernate....but maybe we can detect that and avoid it. Would just be good with a failing testcase (remember to list db and jdbc driver version)

Thanks.

Hibernate obviously can not guess the parameter in this query:
SELECT DECODE('x', NULL, 'x', 'n') AS test_column FROM dual.

I am going to send the test case later.