Passing NULL into SPs through Hibernate named queries.

suren · **Posted:** Mon Sep 05, 2005 5:25 am

I have a set of stored procedures in Oracle 10g database which I access using Hibernate named queries. These SPs also accept NULL input parameters as well. But when I try to pass NULL objects I get the following error.

--------------------------------------------------------
Data Access Failure

Hibernate operation: could not execute query; bad SQL grammar [{? = call GET_KOMMUNFOR(?) }]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in call to 'GET_KOMMUNFOR' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
« Back
--------------------------------------------------------

BTW I'm using the Sping framework's HibernateTemplate.

Thanks,
Suren.

UTDrew · **Joined:** Wed May 03, 2006 5:09 pm **Posts:** 17

I'm having the exact same problem. If I go in and create a separate named query where the parameter is the null literal it works

ex:

ORA-06550

Code:

@NamedNativeQuery(name="test_func",query="{? = call test_func(:testVal) }",resultClass=Test.class,                hints=@QueryHint(name="org.hibernate.callable",value="true"))
...
em.createNamedQuery(test_func).setParameter("testVal",null).getSingleResult();

Works fine

Code:

@NamedNativeQuery(name="test_func_null",query="{? = call test_func(null) }",resultClass=Test.class,                hints=@QueryHint(name="org.hibernate.callable",value="true"))
....
em.createNamedQuery(test_func_null).getSingleResult();

Did you ever discover how to fix it?

Drew

max · **Posted:** Mon Oct 16, 2006 6:09 am

we don't have a .setNull() method on query api because it doesn't really make sense for queries.

It *might* make sense for storedprocedure calls but it just feels so ugly to add this just because you don't have an apropriate method for your sp call ....

UTDrew · **Joined:** Wed May 03, 2006 5:09 pm **Posts:** 17

Could you elaborate on what 'an apropriate method for your sp call....' might be?

Drew

max · **Posted:** Tue Oct 24, 2006 10:28 am

one that doesn't take null's ;)

Shivali · **Joined:** Mon Oct 30, 2006 10:14 am **Posts:** 1

I was facing the same problem. I tried this solution
use query.setDate(param1, param2) in your DAO layer where param1 is your bind variable and param2 is 'null';

It'll work. and Let me know

max · **Posted:** Mon Oct 30, 2006 10:24 am

that is what he did and claims errors...or?

UTDrew · **Joined:** Wed May 03, 2006 5:09 pm **Posts:** 17

Unfortunately that method isn't available as we are using Hibernate with EJB3. Thanks for your response though.

Do you know how to do this with the EJB3 Query object?

max · **Posted:** Mon Oct 30, 2006 11:24 am

on second thought .setParameter(blah, null); should just work.

max · **Posted:** Mon Oct 30, 2006 11:33 am

on third thought, no it won't work since we can't detect at runtime what type you actually is "setting".

you can work around this by casting to hibernat world and use the call that were suggested before.

UTDrew · **Joined:** Wed May 03, 2006 5:09 pm **Posts:** 17

Couldn't you just detect what type the field 'should' be and then cast it appropriately or just pass through when the value is null? I'm still confused about why Hibernate does that type checking anyways...it seems like you should be able to set a parameter to be whatever you want and you deal with the consequences.

max · **Posted:** Mon Oct 30, 2006 12:07 pm

eh ?

JDBC requires to know the type when you call .setNull too..we can't ignore that.

and we do try and detect it via jdbc getParameterMetaData but if the underlying db doesn't want to tell us then we are out of luck.

ranjee29 · **Joined:** Wed Jul 26, 2006 9:53 pm **Posts:** 16

Shivali wrote:

I was facing the same problem. I tried this solution
use query.setDate(param1, param2) in your DAO layer where param1 is your bind variable and param2 is 'null';

It'll work. and Let me know

I have tried using all the methods listed below:
query.setParameter(bindName, value, Hibernate.STRING);
query.setString(bindName, value);

where value is null but the query doesnt gives the correct output.

But instead if for null values I do not use and bind parameters and explicitly write my query with the where clause as "... where property IS NULL" then the query result is correct.

The query API says that the setparameter() methods allow null values but they do not seem to be converting it to the the right SQL query. As in a SQL query "...where = null" never works.

max · **Posted:** Fri Nov 10, 2006 2:49 pm

if you expect a null you query will need to be written to support it.

e.g.

(:param is null || x = :param)

ranjee29 · **Joined:** Wed Jul 26, 2006 9:53 pm **Posts:** 16

Thank you so much for such a prompt reply. I really appreciate this.

But I couldnt completely get teh solution could you write it more explicitly??