aspose file tools*
The moose likes Object Relational Mapping and the fly likes NamedStoredProcedureQuery gets no result Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "NamedStoredProcedureQuery gets no result" Watch "NamedStoredProcedureQuery gets no result" New topic
Author

NamedStoredProcedureQuery gets no result

Jose Alvarez de Lara
Ranch Hand

Joined: May 10, 2008
Posts: 92
Hi,

Now I am trying a NamedStoredProcedureQuery using EclipseLink 2.0 and Eclipse Galileo SR2 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,



I use a façade to manage the query,



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



But I do not get anything. No exception, no warning, ...

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="jdbcracle:thin:@127.0.0.1:1521:XE"/>
<property name="eclipselink.jdbc.user" value="myuser"/>
<property name="eclipselink.jdbc.password" value="mypass"/>
<property name="eclipselink.logging.level" value="FINEST"/>
</properties>
</persistence-unit>
</persistence>


What am I doing wrong?

Thanks in advance,
Jose
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Your code looks correct. If you are not getting any error, then it seems to be working.

What SQL is logged? Call the procedure manually to ensure you have data in the table.

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Jose Alvarez de Lara
Ranch Hand

Joined: May 10, 2008
Posts: 92
Hi,

I have not results because a bad configuration of the project.
I made some changes and it is starting to go.

I am getting the following exception,


06-may-2010 18:10:21 com.sun.enterprise.transaction.JavaEETransactionManagerSimplified initDelegates
INFO: Using com.sun.enterprise.transaction.jts.JavaEETransactionManagerJTSDelegate as the delegate
javax.ejb.EJBException
at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:5070)
at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:4968)
at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:4756)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1955)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1906)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:208)
at com.sun.ejb.containers.EJBObjectInvocationHandlerDelegate.invoke(EJBObjectInvocationHandlerDelegate.java:75)
at $Proxy231.customize(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie.dispatchToMethod(ReflectiveTie.java:146)
at com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie._invoke(ReflectiveTie.java:176)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatchToServant(CorbaServerRequestDispatcherImpl.java:682)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:216)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1841)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1695)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:1078)
at com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:221)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:797)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.dispatch(CorbaMessageMediatorImpl.java:561)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.doWork(CorbaMessageMediatorImpl.java:2558)
at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.performWork(ThreadPoolImpl.java:492)
at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:528)
Caused by: javax.persistence.PersistenceException: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.IntegrityException
Descriptor Exceptions:
---------------------------------------------------------

Exception [EclipseLink-48] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [LPROJECT.PROJ_ID]. Only one may be defined as writable, all others must be specified read-only.
Mapping: org.eclipse.persistence.mappings.OneToOneMapping[project]
Descriptor: RelationalDescriptor(model.Lproject --> [DatabaseTable(LPROJECT)])

Exception [EclipseLink-48] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [SALARY.EMP_ID]. Only one may be defined as writable, all others must be specified read-only.
Mapping: org.eclipse.persistence.mappings.OneToOneMapping[employee]
Descriptor: RelationalDescriptor(model.Salary --> [DatabaseTable(SALARY)])

Exception [EclipseLink-48] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [PHONE.EMP_ID]. Only one may be defined as writable, all others must be specified read-only.
Mapping: org.eclipse.persistence.mappings.OneToOneMapping[employee]
Descriptor: RelationalDescriptor(model.Phone --> [DatabaseTable(PHONE)])

Runtime Exceptions:
---------------------------------------------------------

at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:392)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.getServerSession(EntityManagerFactoryImpl.java:151)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManagerImpl(EntityManagerFactoryImpl.java:207)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:202)
at com.sun.enterprise.container.common.impl.EntityManagerWrapper._getDelegate(EntityManagerWrapper.java:197)
at com.sun.enterprise.container.common.impl.EntityManagerWrapper.createNamedQuery(EntityManagerWrapper.java:521)
at ejb.EmployeeSessionBean.customize(EmployeeSessionBean.java:77)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1052)
at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1124)
at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:5243)
at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:615)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:567)
at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doAround(SystemInterceptorProxy.java:157)
at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:139)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:858)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:367)
at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:5215)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:5203)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:201)
... 19 more
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.IntegrityException
Descriptor Exceptions:
---------------------------------------------------------

Exception [EclipseLink-48] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [LPROJECT.PROJ_ID]. Only one may be defined as writable, all others must be specified read-only.

Exception [EclipseLink-48] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [SALARY.EMP_ID]. Only one may be defined as writable, all others must be specified read-only.

Exception [EclipseLink-48] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [PHONE.EMP_ID]. Only one may be defined as writable, all others must be specified read-only.

Runtime Exceptions:
---------------------------------------------------------

at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.initializeDescriptors(DatabaseSessionImpl.java:478)
at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.initializeDescriptors(DatabaseSessionImpl.java:406)
at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.postConnectDatasource(DatabaseSessionImpl.java:671)
at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.loginAndDetectDatasource(DatabaseSessionImpl.java:620)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryProvider.login(EntityManagerFactoryProvider.java:228)
at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:368)
... 47 more


Here is the Employee entity,



At the top of it I have some commented code.
Is something like that what I need?

Thanks,
Jose
Jose Alvarez de Lara
Ranch Hand

Joined: May 10, 2008
Posts: 92
Hi

I have made some changes in the related entities code by typing @Column(name="EMP_ID", insertable=false, updatable=false).
This change resolves the last issue but now I am getting problems with the calling of the Stored Procedure,

Here is the code at the Employee entity,



Here is the calling of the Stored Procedure in the facade EJB,



And this gives me the following exception,



How is the calling of the Stored Procedure?

Thanks,
Jose
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
You need to define the cursor parameter,



(don't define any parameter in the query for this, the query parameters are only inputs)
Jose Alvarez de Lara
Ranch Hand

Joined: May 10, 2008
Posts: 92
Hi,

The problem was in the name I put in the code. I did not know,
but it has got to be this way



As you can see name and queryParameter has the same string value and they are the name of the arguments in the stored procedure.
This way it works fine.

Best Regards,
Jose
 
wood burning stoves
 
subject: NamedStoredProcedureQuery gets no result