Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » How to get return code from a stored procedure using EclipseLink (NamedStoredProcedureQuery Annotation)
How to get return code from a stored procedure using EclipseLink [message #723621] Fri, 09 September 2011 01:23 Go to next message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Hi Gurus,

I am trying to execute a stored procedure using EclipseLink NamedStoredProcedureQuery. Can someone help me to get the return code from the storedprocedure?

For example, the stored procedure will be something like:

*********************************************************************************
create procedure get_person
@person_name varchar(80)

as
declare @machine_type char(1)

IF @person_name = 'ALL' BEGIN
SELECT ID, NAME, SURNAME, ADDRESS
FROM Person
WHERE NAME > ''
ORDER BY NAME
END
ELSE BEGIN
IF exists (SELECT NAME FROM Person WHERE NAME = @person_name)
SELECT ID, NAME, SURNAME, ADDRESS
FROM Person
WHERE NAME = @person_name
ORDER BY NAME
END
return -999 /*SUCCESS*/
*********************************************************************************

The NamedStoredProcedureQuery Annotation only allows you to set the StoredProcedureParameter list but it does not allow to capture the return code from the stored procedure. In the above example, how do I capture the return code -999?

If I call using the sql92 convention {? = call get_person(?)} using a callable statement, I am able to get the return code succesfully. I would like to use EclipseLink's NamedStoredProcedureQuery Annotation for practical purposes.


Thanks
Sridhar
Re: How to get return code from a stored procedure using EclipseLink [message #724550 is a reply to message #723621] Mon, 12 September 2011 15:17 Go to previous messageGo to next message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Hello Gurus,

Does anyone have an idea to get the return code from a stored procedure? This is what I am doing:

Declared the stored procedures using NamedStoredProcedureQuery annotation as follows:
------------------------------------------------------------------------------------
@NamedStoredProcedureQuery(
name="GET_PERSON",
procedureName="get_person",
returnsResultSet=true,
parameters={
@StoredProcedureParameter(queryParameter="person_name",direction=Direction.IN,type=String.class)
},
resultClass=Person.class,
resultSetMapping="subsResult"
)
@SqlResultSetMapping( name="subsResult", entities={
@EntityResult( entityClass=Person.class , fields = {
@FieldResult( name="id", column="ID" ),
@FieldResult( name="name", column="NAME" ),
@FieldResult( name="surname", column="SURNAME" ),
@FieldResult( name="address", column="ADDRESS" )
})
}
)


The stored procedure get_person is declared as follows:
------------------------------------------------------
create procedure get_person
@person_name varchar(80)

as
declare @machine_type char(1)

IF @person_name = 'ALL' BEGIN
SELECT ID, NAME, SURNAME, ADDRESS
FROM PERSON
WHERE NAME > ''
ORDER BY NAME
END
ELSE BEGIN
IF exists (SELECT NAME FROM PERSON WHERE NAME = @person_name)
SELECT ID, NAME, SURNAME, ADDRESS
FROM PERSON
WHERE NAME = @person_name
ORDER BY NAME
END
return -999 /*SUCCESS*/

The person class is declared as follows:
---------------------------------------
package com.sridhar.jpa.tutorial;

import java.io.Serializable;
import javax.persistence.*;


/**
* The persistent class for the PERSON database table.
*
*/
@Entity
@Table(name="PERSON")
public class Person implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@Column(name="ID")
private String id;

@Column(name="ADDRESS")
private String address;

@Column(name="NAME")
private String name;

@Column(name="SURNAME")
private String surname;

public Person() {
}

public String getId() {
return this.id;
}

public void setId(String id) {
this.id = id;
}

public String getAddress() {
return this.address;
}

public void setAddress(String address) {
this.address = address;
}

public String getName() {
return this.name;
}

public void setName(String name) {
this.name = name;
}

public String getSurname() {
return this.surname;
}

public void setSurname(String surname) {
this.surname = surname;
}

}

This is the test driver:
------------------------
public class TestSampleSPUsingNamedStoredProcedureQuery {
public static void main(String[] args) {
EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("testjpa");

EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();

Query qry = em.createNamedQuery("GET_PERSON");
qry.setParameter("person_name", "ALL");

for (Iterator<Parameter<?>> it = qry.getParameters().iterator(); it.hasNext()Wink {
Parameter<?> pa = it.next();
System.out.println(pa.getName()+" - "+qry.getParameterValue(pa.getName()));
}

@SuppressWarnings("unchecked")
List<Person> persons = (List<Person>)qry.getResultList();

java.util.Iterator<Person> iter = persons.iterator();
while (iter.hasNext()) {

Person person = iter.next();
System.out.println("Person: " + person.getName() +
"," + person.getSurname() +
"," + person.getAddress());
}

em.getTransaction().commit();
em.close();
entityManagerFactory.close();
}

}

This test works just fine. However, I need to find a mechanism to get the return code -999 from the stored procedure.

Can someone throw somelight in this regard?
Thanks
Sridhar
Re: How to get return code from a stored procedure using EclipseLink [message #724599 is a reply to message #724550] Mon, 12 September 2011 16:04 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

If,

{? = call get_person(?)}

works, then you need to execute it as a StoredFunction instead of a stored procedure. (@NamedStoredFunctionQuery)

Otherwise, you can pass a return code as an output parameter.



James : Wiki : Book : Blog : Twitter
Re: How to get return code from a stored procedure using EclipseLink [message #724651 is a reply to message #724599] Mon, 12 September 2011 18:15 Go to previous messageGo to next message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Hi James,
Thanks for your quick response. I am trying to make use of EclipseLink on a legacy application that is supported on Sybase, MSSQL and Oracle. In this particular case, the get_person is created as a stored procedure. I tried both the mechanisms suggested (StoredFunctionCall and @NamedStoredFunctionQuery) going against Sybase but they fail with:

"Exception Description: SybasePlatform does not support stored functions"

Also, altering the existing stored procedure to return the return code as a seperate parameter will not work because it is going to break legacy.

It appears @NamedStoredProcedureQuery is the right mechanism to handle stored procedures. Is ther a way we can get the return code retrival handling added to this annotation?

Thanks and appreciate your time.
Sridhar

Re: How to get return code from a stored procedure using EclipseLink [message #725721 is a reply to message #724651] Thu, 15 September 2011 15:34 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You will need to modify/extend your SybasePlatform to allow stored function execution.


James : Wiki : Book : Blog : Twitter
Re: How to get return code from a stored procedure using EclipseLink [message #726934 is a reply to message #725721] Tue, 20 September 2011 01:45 Go to previous messageGo to next message
Sridhar Baratam is currently offline Sridhar Baratam
Messages: 22
Registered: September 2011
Junior Member
Thanks James. All the existing databases are legacy and I cannot alter their existing functionality. It would be great if the @namedstoredprocedure is enhanced to return the stored procedure return code.

Thanks
Sridhar
Re: How to get return code from a stored procedure using EclipseLink [message #726947 is a reply to message #725721] Tue, 20 September 2011 01:45 Go to previous messageGo to next message
Sridhar is currently offline Sridhar
Messages: 12
Registered: July 2009
Junior Member
Thanks James. All the existing databases are legacy and I cannot alter their existing functionality. It would be great if the @namedstoredprocedure is enhanced to return the stored procedure return code.

Thanks
Sridhar
Re: How to get return code from a stored procedure using EclipseLink [message #754223 is a reply to message #724599] Tue, 01 November 2011 13:55 Go to previous messageGo to next message
Ahsan  is currently offline Ahsan
Messages: 1
Registered: November 2011
Junior Member
Hi James,

Can you please help me with an example on how to use @NamedStoredFunctionQuery.

I have a stored function (oracle) that returns a TABLE. I am using glassfish3.1 and eclipselink. I can run @NamedStoredProcedureQuery successfully but can't call functions at all.

I am having trouble specifying the return parameter as PL/SQL TABLE.

thanks,
Ahsan

[Updated on: Tue, 01 November 2011 15:58]

Report message to a moderator

Re: How to get return code from a stored procedure using EclipseLink [message #754227 is a reply to message #724599] Tue, 01 November 2011 13:55 Go to previous messageGo to next message
Ahsan  is currently offline Ahsan
Messages: 2
Registered: December 2010
Junior Member
Hi James,

Can you please help me with an example on how to use @NamedStoredFunctionQuery.

I have a stored function (oracle) that returns a resultset and another that returns an array. I am using glassfish3.1 and eclipselink. I can run @NamedStoredProcedureQuery successfully but can't call functions at all.

I am having trouble specifying the return parameter as resultset or array.

thanks,
Ahsan
Re: How to get return code from a stored procedure using EclipseLink [message #755444 is a reply to message #723621] Tue, 08 November 2011 14:45 Go to previous messageGo to next message
Neikius Mising name is currently offline Neikius Mising name
Messages: 43
Registered: April 2011
Member
@Ahsan:

This is impossible to do. NamedStoredFunctionQuery is a construct that only returns simple types (like String). This is a very great undocumented limitation that took me a week worth of time to figure out. You better just make a stored procedure wrapper that returns an output parameter with cursor to the said table. That worked for me fine.

[Updated on: Tue, 08 November 2011 14:45]

Report message to a moderator

Re: How to get return code from a stored procedure using EclipseLink [message #755445 is a reply to message #723621] Tue, 08 November 2011 14:45 Go to previous message
Neikius Mising name is currently offline Neikius Mising name
Messages: 43
Registered: April 2011
Member
This is impossible to do. NamedStoredFunctionQuery is a construct that only returns simple types (like String). This is a very great undocumented limitation that took me a week worth of time to figure out. You better just make a stored procedure wrapper that returns an output parameter with cursor to the said table. That worked for me fine.
Previous Topic:Strange cache behaviour after clear
Next Topic:DescriptorQueryManager+EntityManager
Goto Forum:
  


Current Time: Wed Oct 22 15:42:03 GMT 2014

Powered by FUDForum. Page generated in 0.02079 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software