stored procedure

tim23 · **Joined:** Thu Oct 21, 2004 10:07 am **Posts:** 13

hi all,

I have a big problem and get no further more.
i had read the docs, but it doesn't work for me ;-(

could anyone give me an example how creates function with an return?
or is there an code example with this?

I had tried with Postgresql, Mysql and DB2, but anyhow it does not work.

I have a NamedQueries.hbm.xml with:
<sql-query name = "proc1_SP" callable = "true">
<return-scalar type = "stands in" column = "costwithtax"/>
{? = call proc1 (?)}
</sql-query>

Query sp = session.getNamedQuery ("proc1_SP");
sp.setParameter (0.1);
List list = sp.list ();

please, i needs really help, otherwise I still despair ; - (

many thanks in advance!

tim

jt_1000 · **Posted:** Thu Feb 16, 2006 4:26 pm

what is the error message you are getting, and
post the function that you are using.

-JT

tim23 · **Joined:** Thu Oct 21, 2004 10:07 am **Posts:** 13

Hi JT,

exception with DB2,mysql and postgresql:
Exception in thread "main" java.lang.ArrayStoreException
at java.lang.System.arraycopy(Native Method)
at java.util.ArrayList.toArray(ArrayList.java:305)
at org.hibernate.util.ArrayHelper.toTypeArray(ArrayHelper.java:75)
at org.hibernate.impl.AbstractQueryImpl.typeArray(AbstractQueryImpl.java:654)
at org.hibernate.impl.AbstractQueryImpl.getQueryParameters(AbstractQueryImpl.java:662)
at org.hibernate.impl.SQLQueryImpl.getQueryParameters(SQLQueryImpl.java:168)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:160)

in mysql i have a function like this:
CREATE FUNCTION proc1 (pid bigint(20)) RETURNS double
BEGIN
declare ret bigint(20);
select cost*16 into ret from projects where projectNr=pid;
return ret;
END

or a procedure like this:
CREATE PROCEDURE proc1 (INOUT pid bigint(20))
BEGIN
select cost*16 into @cost from projects where projectNr=pid;
END

the function/procedure works in the db shell, but anyway not with hibernate ;-(

hope that is what you need...

many thanks for your help in advance!!!

tim

jt_1000 · **Posted:** Fri Feb 17, 2006 4:18 pm

Tim,

I have done what you are trying to do against Oracle, but not the dbs that you are targeting.

However, I noticed a few things in your code that looked odd -

1) the return type in your sql-query is probably not right... it should be long or double, string...

2) the setParameter looks off too what is 0.1?
try using something like setDouble(0,4.5); where "0" is the first parameter in your list that you are passing to the function. and 4.5 is the value.

3) also with Oracle I had to return a result set (and put the numeric value in col of the select), and not a direct number... not sure if this will apply with your dbms.

oracle example:

Code:

create or replace function getCount( arg number)
return sys_refcursor as st_cursor SYS_REFCURSOR;
mycnt number:=0;
begin
    open st_cursor for
          select (count(*)*arg) as cnt from user_tables;
    return st_cursor;
end;

-JT

tim23 · **Joined:** Thu Oct 21, 2004 10:07 am **Posts:** 13

Hi JT,

may thanks for your help!

jt_1000 wrote:

1) the return type in your sql-query is probably not right... it should be long or double, string...

the return type should be correct.

jt_1000 wrote:

2) the setParameter looks off too what is 0.1?
try using something like setDouble(0,4.5); where "0" is the first parameter in your list that you are passing to the function. and 4.5 is the value.

in my code i hade writen: .setParameter(0,1), because the in parameter is an bigint.

jt_1000 wrote:

3) also with Oracle I had to return a result set (and put the numeric value in col of the select), and not a direct number... not sure if this will apply with your dbms.

for mysql it doesnt works, but i try it with postgresql + db2.

JT could you send me your NamedQueries.hbm.xml or mapping and your java code to show me how its work, please.

Many thank once again!!!

Tim

jt_1000 · **Posted:** Sun Feb 19, 2006 6:45 pm

re #2: you example showed 0.1 (that is zero dot one) ..thus it is a problem. (probably just a typo though).

re #1: what is "stands in"? never seen that before? are you sure about the "type?"... can't have spaces as a Types (as far as Java goes, although this sounds like some sort of alias - I am interested)...

Here is the code that worked for me under Oracle 9i:

Java Query Code w/ unique and with listing the result set:

Code:

       Query q=sess.getNamedQuery("getCount");
       q.setDouble(0,2);
        boolean doUnique=true;
        if(doUnique)
        {
            Object obj=q.uniqueResult();
            if(obj instanceof Long)
            {
                System.out.println("answer:" + ((Long)obj).doubleValue());
            }
        }else
        {
            Long dbl=null;
            Iterator itr=q.list().iterator();
            while(itr!=null&&itr.hasNext())
            {
                 dbl=(Long) itr.next();
                System.out.println("dbl=" + dbl);
            }
        }

Named Query declaration:

Code:

...
    </class>
   <sql-query name="getCount" callable= "true">
        <return-scalar column="cnt" type="long"/>
        { ? = call getCount(?) }
   </sql-query>

</hibernate-mapping>

tim23 · **Joined:** Thu Oct 21, 2004 10:07 am **Posts:** 13

Hi JT,

sorry, I was ill and could not answer before.

I tried it once again, but it doesnt work for me ;-(

Many thanks once again for your effort and time.

Tim

jt_1000 · **Posted:** Fri Mar 03, 2006 1:26 pm

Tim - check out the solution to your problem (similar) on this recent post... (at the end).

http://forum.hibernate.org/viewtopic.php?t=956217&postdays=0&postorder=asc&start=15