aspose file tools*
The moose likes Object Relational Mapping and the fly likes Hibernate/Native SQL and null inparams Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate/Native SQL and null inparams" Watch "Hibernate/Native SQL and null inparams" New topic
Author

Hibernate/Native SQL and null inparams

Mathias Berg
Greenhorn

Joined: Oct 03, 2007
Posts: 5
Hi i wonder if i could get some help regarding a small but so irretation thing.

Is there a problem of using Timestamp as null in a call to a stored procedure, by using Hibernate and Native SQL to to the call.
(im using this as an example, http://www.hibernate.org/hib_docs/v3/reference/en/pdf/hibernate_reference.pdf -> 16.2.2. Using stored procedures for querying)

Im using Spring 2.3, Hibernate 3, Oracle 10g database.

My DAO look like this.
-----------------------------------------

...
List<TestSP> testSP = getHibernateTemplate().findByNamedQueryAndNamedParam
("selectAllCases_sp", new String[]{"id", "date_from"}
, new Object[]{"73",null}
);

....

My Hibernate stored procedure mapping file.
-----------------------------------------

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

<class name="TestSP" mutable="false">
<id name="Id" column="id" type="string" >
<generator class="assigned"/>
</id>
<property name="type" column="type"/>
</class>

<sql-query name="selectAllCases_sp" callable="true">
<return alias="testSP" class="TestSP">
<return-property name="Id" column="id"/>
<return-property name="type" column="type"/>

</return>
{ ? = call selectAllCases(:id, ate_from) }
</sql-query>
</hibernate-mapping>


My Exception is this
-------------------------------------------------------
....
2007-11-13 16:55:50,522 DEBUG org.hibernate.jdbc.AbstractBatcher(line:393) - { ? = call selectAllCases(?, ?) }
2007-11-13 16:55:50,569 WARN org.hibernate.util.JDBCExceptionReporter(line:77) - SQL Error: 6550, SQLState: 65000
2007-11-13 16:55:50,569 ERROR org.hibernate.util.JDBCExceptionReporter(line:78) - ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'SELECTALLCASES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

2007-11-13 16:55:50,569 ERROR CaseDaoHibernate(line:86) - error
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at org.springframework.orm.hibernate3.HibernateTemplate$33.doInHibernate(HibernateTemplate.java:940)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:369)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQueryAndNamedParam(HibernateTemplate.java:931)
at CaseDaoHibernate.getTestSP(CaseDaoHibernate.java:80)
at CaseDaoTest.testCaseSearch(CaseDaoTest.java:85)
.....
Mathias Berg
Greenhorn

Joined: Oct 03, 2007
Posts: 5
Response to myself

My inparam to the SP wich is a timestamp. If its null then i get this error

PLS-00306: wrong number or types of arguments in call to 'SELECTALLCASES'

But if a replace the null with a emtpy string "" then it doesnt throw a exception.
So it seems my Hibernate query skips the inpara if its null (This only happens for Timestamps though, for varchar2 it works fine with null)

Any clues, would be apreciated?
 
Consider Paul's rocket mass heater.
 
subject: Hibernate/Native SQL and null inparams