Hello World of Stored Procedures with Hibernate

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

Hibernate version:
3.05
Mapping documents:
<sql-query name="selectall" callable="true">
<return alias="team" class="sk.Team">
<return-property name="username" column="property"/>
<return-property name="roletype" column="roletype"/>
<return-property name="id" column="id"/>
</return>
{ ? = call GetName_SP(?) }
</sql-query>
Code between sessionFactory.openSession() and session.close():
List users = session.getNamedQuery ("selectall").setString(0, "A").list();
Full stack trace of any exception that occurs:
[java] org.hibernate.QueryException: Expected positional parameter count: 2
actual parameters: [Suresh] [
[java]
[java] { ? = call GetName_SP(?) }
[java] ]
[java] at org.hibernate.impl.AbstractQueryImpl.verifyParameters(Abstrac
QueryImpl.java:118)
[java] at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:133)
[java] at sk.Hibernate.query(Unknown Source)
[java] at sk.Hibernate.main(Unknown Source)
[java] java.lang.NullPointerException
[java] at sk.Hibernate.main(Unknown Source)
[java] Exception in thread "main"
[java] Java Result: 1

Name and version of the database you are using:
Oracle 9i
The generated SQL (show_sql=true):
[java] DEBUG - Named SQL query: selectall ->
[java]
[java] { ? = call GetName_SP(?) }

Debug level Hibernate log excerpt:

Any help appreciated. Can anyone point to a very simple example of using Oracle Stored Procedures from within Hibernate by using the mapping. Not using CallableStatement (JDBC)

The documentation in chapter 17 could be more helpful if there was a supporting example. What it says doesn't work.

max · **Posted:** Tue Aug 30, 2005 3:30 am

well its copied directly from our unit tests which does work....go look for callable= in hbm.xml in the unit test.

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

My Debug Window

[java] INFO - Checking 0 named queries
[java] DEBUG - opened session
[java] DEBUG - opening JDBC connection
[java] DEBUG - total checked-out connections: 0
[java] DEBUG - using pooled JDBC connection, pool size: 0
[java] org.hibernate.QueryException:
Expected positional parameter count: 1, actual parameters: [] [ [java]
[java]
[java] { ? = call simpleScalar(:number) }
[java] ]

I am running the cut and paste example from Junit tests
I can't run junit tests either. i am not concerned about that.

Can someone tell me what is expected positional parameter count... thing
I guess it is wanting a value for the first ? and second ? but I don't want to use JDBC code at all.

Please help.. and I will put a nice tutorial for starters to get it right.

My method to invoke this simpleScalar that is shipped with Hibernate

public void store(){
//if( !(getDialect() instanceof Oracle9Dialect)) return;

try {
Session s = super.currentSession();

Statement statement = s.connection().createStatement();
statement.execute("CREATE OR REPLACE FUNCTION simpleScalar (j number) " +
" RETURN SYS_REFCURSOR " +
"AS " +
" st_cursor SYS_REFCURSOR; " +
"BEGIN " +
" OPEN st_cursor FOR " +
" SELECT j as value, \'getAll\' as name from dual; " +
" RETURN st_cursor; " +
"END;");
statement.close();
Query namedQuery = s.getNamedQuery("simpleScalar_SP");
namedQuery.setLong("number", 43);
List list = namedQuery.list();
Object o[] = (Object[]) list.get(0);
s.close();
} catch (Exception e) {
System.out.println(e);

}
}

My mapping file

<sql-query name="simpleScalar_SP" callable="true">
<return-scalar column="name" type="string"/>
<return-scalar column="value" type="long"/>
{ ? = call simpleScalar(:number) }
</sql-query>

max · **Posted:** Wed Aug 31, 2005 12:16 am

remove the white space up to the { ..oracle hates those

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

tried
{?=call simpleScalar(?)}
still the same message

please shade some light guys, if you have made it work.

max · **Posted:** Wed Aug 31, 2005 9:42 am

did you remove the white space BEFORE/UP-TO the { as I wrote ?

and did you check the unit tests ?

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

Thanks for a quick reply. I am still poking it everywhere, I have read the documentation so many times now(chap 17)
My junit tests had compilation problem so I left them aside

Hibernate Map
<sql-query name="simpleScalar_SP" callable="true">
<return-scalar column="name" type="string"/>
<return-scalar column="value" type="long"/>
{?=call simpleScalar(:number)}</sql-query>

Hibernate Java
Query namedQuery = s.getNamedQuery("simpleScalar_SP");
namedQuery.setLong("number",30);

Hibernate Debug
[java] INFO - schema update complete
[java] INFO - cleaning up connection pool: jdbc:oracle:thin:@localhost:152
:sk
[java] INFO - Checking 0 named queries
[java] org.hibernate.QueryException: Expected positional parameter count: 1
actual parameters: [] [{?=call simpleScalar(:number)}]
----------------------------------------------------------------
Using ?

Mapping
<sql-query name="simpleScalar_SP" callable="true">
<return-scalar column="name" type="string"/>
<return-scalar column="value" type="long"/>
{?=call simpleScalar(?)}</sql-query>
Debug
[java] INFO - cleaning up connection pool: jdbc:oracle:thin:@localhost:152
1:sk
[java] INFO - Checking 0 named queries
[java] org.hibernate.QueryException: Unset positional parameter at position
: 0 [{?=call simpleScalar(?)}]

Hibernate Java
Query namedQuery = s.getNamedQuery("simpleScalar_SP");
namedQuery.setLong(1,30);

max · **Posted:** Wed Aug 31, 2005 10:25 am

and your dialect is set to Oracle9Dialect?

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

that's what it is

Oracle9Dialect

here are the files if anyone has time to check
OracleEmployment.hbm.xml

Code:

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

<!-- 

  This mapping demonstrates the use of Hibernate with
  all-handwritten SQL!
  
  This version is for Oracle
     
-->

<hibernate-mapping default-access="field">


<sql-query name="simpleScalar_SP" callable="true">
<return-scalar column="name" type="string"/>
<return-scalar column="value" type="long"/>
{?=call simpleScalar(?)}</sql-query>
</hibernate-mapping>

Hibernate Java

Code:

package hb.sql;
import org.hibernate.*;
import org.hibernate.cfg.*;

import java.sql.Statement;
import java.util.List;

public class Hibernate {

public static void main (String args[]) {
  Hibernate hm = new Hibernate();
  hm.query();
}

public void query () {
        try {
      Session s = currentSession();
      
      Statement statement = s.connection().createStatement();
      statement.execute("CREATE OR REPLACE FUNCTION simpleScalar (j number) " + 
            "    RETURN SYS_REFCURSOR " + 
            "AS " + 
            "    st_cursor SYS_REFCURSOR; " + 
            "BEGIN " + 
            "    OPEN st_cursor FOR " + 
            "        SELECT j as value, \'getAll\' as name  from dual; " + 
            "     RETURN  st_cursor; " + 
      "END;");

      statement.close();
      Query namedQuery = s.getNamedQuery("simpleScalar_SP");
      namedQuery.setLong(1,30);
      List list = namedQuery.list();
        } catch (Exception e) {
            System.out.println(e);
        }
}



private static final SessionFactory sessionFactory;

    static {
        try {
            sessionFactory = new Configuration().configure().buildSessionFactory();
        } catch (Throwable ex) {
        }
    }

    public static final ThreadLocal session = new ThreadLocal();

    public static Session currentSession() throws HibernateException {
        Session s = (Session) session.get();
        // Open a new Session, if this Thread has none yet
        if (s == null) {
            s = sessionFactory.openSession();
            session.set(s);
        }
        return s;
    }

    public static void closeSession() throws HibernateException {
        Session s = (Session) session.get();
        session.set(null);
        if (s != null)
            s.close();
    }
}

Hibernate.cfg.xml

Code:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"

 "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>
        <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:sk</property>
        <property name="hibernate.connection.username">sk</property>
        <property name="hibernate.connection.password">sk</property>
        <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
        <property name="show_sql">true</property>
        <property name="transaction.factory_class">
             org.hibernate.transaction.JDBCTransactionFactory
        </property>
        <property name="hibernate.cache.provider_class">
             org.hibernate.cache.HashtableCacheProvider
        </property>
        <property name="hibernate.hbm2ddl.auto">update</property>

        <mapping resource="hb/sql/OracleEmployment.hbm.xml"/>

    </session-factory>

</hibernate-configuration>

build.xml

Code:

<project name="hibernate" default="compile">
  <property file="run.properties"/>
  <target name="compile">
    <javac srcdir="." destdir=".">
      <classpath>
        <fileset dir="./lib">
          <include name="*.jar"/>
        </fileset>
      </classpath>
    </javac>
  </target>
    <path id="run.classpath">
      <pathelement location="./lib/antlr-2.7.4.jar"/>
      <pathelement location="./lib/commons-collections-2.1.1.jar"/>
      <pathelement location="./lib/commons-logging-1.0.4.jar"/>
      <pathelement location="./lib/cglib-full-2.0.2.jar"/>
      <pathelement location="./lib/dom4j-1.5.2.jar"/>
      <pathelement location="./lib/hibernate3.jar"/>
      <pathelement location="./lib/hsqldb.jar"/>
      <pathelement location="./lib/jdbc2_0-stdext.jar"/>
      <pathelement location="./lib/jta.jar"/>
      <pathelement location="./lib/log4j-1.2.9.jar"/>
      <pathelement location="./lib/ojdbc14.jar"/>
      <pathelement location="."/>
    </path>
  <target name="run1" depends="compile">

    <java classname="hb.sql.Hibernate" 
          classpathref="run.classpath" fork="true">
      <arg line="${arg}"/>
    </java>
  </target>
</project>

max · **Posted:** Wed Aug 31, 2005 10:44 am

so what dialect was it set to before ?

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

i really appreciate your prompt answers max.
It was Oracle9Dialect.

I was working with Hypersonic before. To check for procedures I changed to oracle.

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

Does anybody know of a link or have anybody used stored procedures with hibernate
If so, can you please tell me what could be the problem. It works using JDBC. It doesn't when I use Query... getNamedQuery

max · **Posted:** Thu Sep 01, 2005 2:26 am

look in the forums - others have confirmed it to work (and the junittests also still work)

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

Oops hibernate3.jar file was old one.

found out after literally tearing the hibernate library for 2 days.

Would hibernate team consider renaming the jar files to the exact versions.