| How to get return code from a stored procedure using EclipseLink [message #723621] |
Fri, 09 September 2011 01:23  |
 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  |
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() {
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
|
|
|
|
|
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.03431 seconds
] 
Search
Help
Register
Login
Home
