-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 36 posts ]  Go to page 1, 2, 3  Next
Author Message
 Post subject: problem with stored procedurewith where condtion
PostPosted: Thu Nov 17, 2005 6:46 pm 
Regular
Regular

Joined: Tue Nov 08, 2005 1:30 pm
Posts: 50
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 8:31 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 8:56 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
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.

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject: Help On Stored procuderes with where condition
PostPosted: Thu Nov 17, 2005 8:58 pm 
Regular
Regular

Joined: Tue Nov 08, 2005 1:30 pm
Posts: 50
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:06 pm 
Regular
Regular

Joined: Tue Nov 08, 2005 1:30 pm
Posts: 50
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:09 pm 
Regular
Regular

Joined: Tue Nov 08, 2005 1:30 pm
Posts: 50
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:14 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
Quote:
- named parameters
- can be used as a loader
- can be changed to be a hql, sql or stored procedure without affecting client code...


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

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


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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:17 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
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.

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:19 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
nag,
i tell you - you get no row
send us java code for call procedure and i try it , too


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:27 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
Quote:
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.


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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:32 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
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.

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:39 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
Quote:
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 9:51 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
"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.

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 10:03 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
nag,
try this
Code:
CREATE OR REPLACE FUNCTION selectAllEmployments(number1 number)
RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;
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;
/


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 11:42 am 
Regular
Regular

Joined: Tue Nov 08, 2005 1:30 pm
Posts: 50
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 36 posts ]  Go to page 1, 2, 3  Next

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.