• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Hibernate/Native SQL and null inparams

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
reply
    Bookmark Topic Watch Topic
  • New Topic