Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Failed to map sql result set when execute stored procedure returning cursor (Failed to map sql result set when execute stored procedure returning cursor)
Failed to map sql result set when execute stored procedure returning cursor [message #753602] Thu, 27 October 2011 21:18 Go to previous message
Ronny is currently offline Ronny
Messages: 7
Registered: October 2011
Junior Member
Two questions related to executing stored procedure which return cursor.

Question 1) A stored procedure return only one column which type is string
Store Procedure:
CREATE OR REPLACE PROCEDURE OPEN_PRODUCT (
IN I_BUSINESS_DATE CHAR(Cool
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE PRODUCT_CURSOR CURSOR WITH RETURN TO CLIENT FOR
SELECT PRODUCT
FROM RMSD.ENT_PRODUCT PROD
WHERE PROD.BUSINESS_DATE = I_BUSINESS_DATE;
FOR READ ONLY;

OPEN PRODUCT_CURSOR
END@

XML:
<named-stored-procedure-query
name="OPEN_PRODUCT "
procedure-name="OPEN_PRODUCT"
returns-result-set="true"
call-by-index="true">
<parameter
name="I_BUSINESS_DATE"
direction="IN"
query-parameter="I_BUSINESS_DATE"
type="String"
/>
</named-stored-procedure-query>

Java Code:
Query q = em.createNamedQuery("OPEN_PRODUCT");
q.setHint("eclipselink.cursor", true);
q.setParameter("I_BUSINESS_DATE", businessDate);
dbCursorProduct = (CursoredStream) q.getSingleResult();


I got the error
Exception [EclipseLink-6105] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.QueryException
Exception Description: Query has to be reinitialized with a cursor stream policy.
Query: DataReadQuery(name="OPEN_PRODUCT" )

How do I use EclipseLink for this cursor? Shall I create dummy class for result set?


Question 2) Actually, it is related to the question above. I tried to create a dummy class "Result" and SqlResultSetMapping

Dummy Class:
public class Result {
@Id
private String stringValue;
public String getStringValue() {
return stringValue;
}
public void setStringValue(String stringValue) {
this.stringValue = stringValue;
}
}

XML:
<named-stored-procedure-query
name="OPEN_PRODUCT "
procedure-name="OPEN_PRODUCT"
result-class="Result"
result-set-mapping="ProductMapping"
returns-result-set="true"
call-by-index="true">
<parameter
name="I_BUSINESS_DATE"
direction="IN"
query-parameter="I_BUSINESS_DATE"
type="String"
/>
</named-stored-procedure-query>
<sql-result-set-mapping name="ProductMapping">
<entity-result
entity-class="Result">
<field-result name="stringValue" column="PRODUCT" />
</entity-result>
</sql-result-set-mapping>

Executed the same Java Code, I got the message:

Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [DatabaseRecord(
=> PROD_1)] during the execution of the query was detected to be null. Primary keys must not contain null.
Query: ReadAllQuery(name="OPEN_PRODUCT" referenceClass=Result)

Seems that sql-result-set-mapping cannot be used by CursoredStream. Please kindly advise, many thanks.

Extracted stracktrace below
at org.eclipse.persistence.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:885)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:592)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:568)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.buildObject(ObjectLevelReadQuery.java:733)
at org.eclipse.persistence.queries.Cursor.buildAndRegisterObject(Cursor.java:310)
at org.eclipse.persistence.queries.CursoredStream.retrieveNextObject(CursoredStream.java:425)
at org.eclipse.persistence.queries.CursoredStream.setLimits(CursoredStream.java:462)
at org.eclipse.persistence.queries.CursoredStream.<init>(CursoredStream.java:58)
at org.eclipse.persistence.queries.CursoredStreamPolicy.execute(CursoredStreamPolicy.java:67)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:406)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1097)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:829)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1056)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:390)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1144)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1501)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1483)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1457)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:773)
at ProductDAO.OPEN_PRODUCT(ProductDAO.java:33)

Report message to a moderator

[Message index]
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic:Calling preparedStatement/Function the JPA way
Next Topic:lazy loading problem
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 19 11:03:23 EDT 2013

Powered by FUDForum. Page generated in 0.01849 seconds