From: <glassfish_at_javadesktop.org>
Date: Wed, 28 Apr 2010 22:27:10 PDT

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="employee"/>
                        <property name="eclipselink.jdbc.password" value="employee"/>
                        <property name="eclipselink.logging.level" value="FINEST"/>
                </properties>
        </persistence-unit>
</persistence>

What am I doing wrong?

Thanks in advance,
Jose
[Message sent by forum member 'josealvarezdelara']

http://forums.java.net/jive/thread.jspa?messageID=399536