From: Jose Alvarez de Lara <dakhla.0563_at_hotmail.com>
Date: Mon, 3 May 2010 04:36:00 +0200

Hi,

The IDE is Eclipse Galileo SR2 witch I am trying the example on.

Any suggestion will be apreciated.

Thanks in advance,
Jose


From: Jose Alvarez de Lara
Sent: Friday, April 30, 2010 6:14 AM
To: ejb_at_glassfish.dev.java.net
Subject: NamedStoredProcedureQuery gets no result


Hi,

I am trying a NamedStoredProcedureQuery using EclipseLink 2.0 and Eclipse Galileo RS1as IDE against Oracle and GF v3.

I would like to retrieve some fields from a table Employee represented by an entity class Employee.

Here is the stored procedure,

CREATE OR REPLACE package body employeedetailpkg as
procedure getEmployeeDetail(employeeId in NUMBER, myCsr out csr)
is
begin
open myCsr for
select emp_id, f_name, l_name from employee
where emp_id = employeeId;
end getEmployeeDetail;
end;


The entity class has declared the NamedStoredProcedureQuery as follows,

@NamedStoredProcedureQuery(
name="findEmployeeById",
resultClass=Employee.class,
procedureName="EMPLOYEEDETAILPKG.GETEMPLOYEEDETAIL",
parameters={
@StoredProcedureParameter(name="EMPLOYEEID",
queryParameter="EMPID", direction=Direction.IN, type=Long.class),
@StoredProcedureParameter(queryParameter="CSR", direction=Direction.OUT_CURSOR)
}
)
public class Employee implements Serializable {

..........................
..........................
}

I use a façade to manage the query,

public List<Employee> customize() {
List<Employee> empList = new ArrayList<Employee>();
try {
emf = Persistence.createEntityManagerFactory("EmployeeJPA");
em = emf.createEntityManager();
EntityTransaction etx = em.getTransaction();
etx.begin();
Query query = em.createNamedQuery("findEmployeeById");
query.setParameter("EMPID", 32L);
//query.setParameter("CSR", new ArrayList());
empList = (List<Employee>)query.getResultList();
etx.commit();
} catch(Exception ex){
Logger.getLogger(EmployeeSessionBean.class.getName()).log(Level.SEVERE, null, ex);
} finally {
em.close();
emf.close();
}
return empList;
}


And here is the code in a client project to call the EJB,

public void JPAStoredProcedureCall() {
EmployeeSessionRemote employeeSessionBean;
try {
InitialContext ctx = new InitialContext();
employeeSessionBean = (EmployeeSessionRemote)ctx.lookup("java:global/JPAStoredProcedure-ear/EmployeeEJB/EmployeeSessionBean");
List<Employee> empList = employeeSessionBean.customize();

for(Employee employee: empList){
System.out.println("Emp Id => " + employee.getEmpId());
System.out.println("Emp FName => " + employee.getFName());
System.out.println("Emp LName => " + employee.getLName());
}
} catch (NamingException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
}

But I do not get anything. No exception, no warning, just a message that I have got instantiate the façade session bean.

Here is the persistence.xml,

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
<persistence-unit name="EmployeeJPA" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/employee</jta-data-source>
<properties>
<property name="eclipselink.jdbc.native-sql" value="true"/>
<property name="eclipselink.jdbc.driver" value="oracle.jdbc.driver.OracleDriver"/>
<property name="eclipselink.jdbc.url" value="jdbc:oracle:thin:@127.0.0.1:1521:XE"/>
<property name="eclipselink.jdbc.user" value="someuser"/>
<property name="eclipselink.jdbc.password" value="somepass"/>
<property name="eclipselink.logging.level" value="FINEST"/>
</properties>
</persistence-unit>
</persistence>

What am I doing wrong?

Thanks in advance,
Jose