Hi gurus,
I am calling function from my java code using hibernate.. When i remove [u]Where[/u] condition then its working properly..when i use where condtion its giving error.

Error:

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:674)
at org.hibernate.impl.AbstractQueryImpl.getQueryParameters(AbstractQueryImpl.java:682)
at org.hibernate.impl.SQLQueryImpl.getQueryParameters(SQLQueryImpl.java:173)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:165)
at examples.schema.GetPatientData.main(GetPatientData.java:26)


Here is the function:

CREATE OR REPLACE FUNCTION selectAllEmployments
RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;
number1 number;
BEGIN
OPEN st_cursor FOR
SELECT p.KEY_CD,p.KEY_ORIGINAL_TXT
FROM PATIENT_DATA p where p.key_cd=number1;
RETURN st_cursor;
END;
/


Mapping file is :
sql-query name="Query_PatientData_SP" callable="true">
<return-scalar column="KEY_CD" type="long"/>
{?=call selectAllEmployments(?)}
</sql-query>[b][quote][/quote][/b]



Please help me..
Ur help is apppreciated with full points..
Thanks
Nag

cursor return no data when you set condition,probably.if this is oracle then it is no data sure because you use number1=null in where)
try first where with any existsing cd number and if it work add number like parameter in function

advice : it is easier use native callable statement with session.getConnection() and you haven't constraint for procedure/function - i can't see advantage usage hibernate's procedure/function

you can't see advantage ?

- named parameters
- can be used as a loader
- can be changed to be a hql, sql or stored procedure without affecting client code...

if there were no advantages we wouldn't have added it.

But anyhow - the exception does not look related to the arraystoreexception since it has to do with parameters.

Actually we have a big database andwe are planning to implement hibernate. we just want to find out featurs of hibernate.. thats the reason we are implemtning examples..

from java i am passing the parameter..thats parameter has values...

Please help with ur session code...
Thanks
Nag

I am not getting that error with out WHERE CONDITON..WHY i am getting..i couldnt able to trace it...
I am spending all the day with out getting o/p.
thanks
Nag

I am sorry for repeating...
I changed the function .. and passing the parameter also/////

I am not getting that error with out WHERE CONDITON..WHY i am getting with paraemeter.................i couldnt able to trace it...
I am spending all the day with out getting o/p.
thanks
Nag

i can do it all with native callable statement , too
i force usage hql, sql before stored procedure and reason 3 isn't important
for example



i don't know - maybe , you can say that you support stored procedures
however, it isn't important - stored procedures are too much database specific and i don't
expect that ORM tool suport it - i use procedure/function which do any work in database (dml)
and hibernate doesn't suport it

eh ? hibernate does support stored proceudres for insert/update/delete.

any how other advantages are second level caching and other stuff.

But i definitly do not expect that every kind of stored procedure should be wrapped into hibernate - it is just for those operations that some people put in stored procedures that maps nicely to resultsets which we can process.

nag,
i tell you - you get no row
send us java code for call procedure and i try it , too

i don't use stored procedures which hibernate support - i can do it without stored procedure (with hibernate, of course) - when i need stored procedure for any work this hibernate doesn't support and it is fine - i can't see how hibernate can do it

hibernate support for stored procedure insert/delete/update is for class/entity only (this is like instead of trigger in oracle) - i need mostly complex work with stored procedure and hibernate can't help me - i don't blame hibernate - i think that it is impossible

and again you are talking about those cases I say is not relevant for hibernate to cover, but the user of this thread has exactly those kind of stored procedures that could make sense to wrap.

i try help user and you talk to me only

"advice : it is easier use native callable statement with session.getConnection() and you haven't constraint for procedure/function - i can't see advantage usage hibernate's procedure/function"

That was what you said which I think were wrong given the context of the user.

And I have answered the users question, but he did not follow up upon it.

nag,
try this

snpesnpe,
I did that.. Still not working..when i remove WHERE conditon then its working properly..

I will attache my java code..


<sql-query name="Query_PatientData_SP" callable="true">
<return-scalar column="KEY_ORIGINAL_TXT" type="string"/>
{?=call selectAllEmployments(?)}
</sql-query>

cleint code:

Session session = null;
try {

// This step will read hibernate.cfg.xml and prepare hibernate for
SessionFactory sessionFactory = new Configuration().configure()
.buildSessionFactory();

long p = 2051;
System.out.println("Entering");
session = sessionFactory.openSession();
Query query = session.getNamedQuery("Query_PatientData_SP");
query.setParameter(1,2051);
System.out.println("Query String " + query.getQueryString());

// System.out.println(query.getReturnTypes());
List list = query.list();
System.out.println("sss");

//System.out.println("List length :" + list.size());
Iterator it = list.iterator();
while (it.hasNext()) {

System.out.println("patient ID : " + it.next());
}

Thanks


Nag