Eclipse Community Forums: EclipseLink » Failed to map sql result set when execute stored procedure returning cursor

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

Eclipse User

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)

Re: Failed to map sql result set when execute stored procedure returning cursor [message #754052 is a reply to message #753602]

Mon, 31 October 2011 11:32

Eclipse User

First of all don't use,

q.setHint("eclipselink.cursor", true);

This is for streaming results, and has nothing to do with stored procedures. If you really wanted to stream results, you would need to set this hint in your named query, not when executing it.

Also,

result-class="Result"
result-set-mapping="ProductMapping"

You can't set both of these, either one or the other.

But, since you don't really seem to want objects back, just data, don't set either. Then you do not need a dummy class, and do not need to worry about mapping the result set. You will just get back the data.

Re: Failed to map sql result set when execute stored procedure returning cursor [message #754144 is a reply to message #754052]

Mon, 31 October 2011 23:31

Eclipse User

Thanks a lot !

Re: Failed to map sql result set when execute stored procedure returning cursor [message #755310 is a reply to message #754144]

Mon, 07 November 2011 22:52

Eclipse User

I finally choose the approach of setting hint "eclipselink.cursor" in named stored procedure query because we called an existing stored procedure which return cursor, and result-set-mapping="ProductMapping"

Unfortunately, I got the error below
Internal Exception: java.lang.IllegalArgumentException: Query null, query hint eclipselink.cursor is not valid for this type of query.

Please kindly help....thanks in advance.

Re: Failed to map sql result set when execute stored procedure returning cursor [message #755311 is a reply to message #754144]

Mon, 07 November 2011 22:52

Eclipse User

Re: Failed to map sql result set when execute stored procedure returning cursor [message #755536 is a reply to message #755310]

Tue, 08 November 2011 13:02

Eclipse User

As James pointed out, the "eclipselink.cursor" hint is not used to specify that the type returned is a cursor. It is used to wrap the results returned in a cursor so that they can be returned one at a time instead of all at once from the resultset, which may not be what you want.

Please try your query without the hint, and also try without your resultset mapping definition to see if it is contributing to the problem.

Best Regards,
Chris

Re: Failed to map sql result set when execute stored procedure returning cursor [message #755537 is a reply to message #755310]

Tue, 08 November 2011 13:02

Eclipse User

Previous Topic:	Calling preparedStatement/Function the JPA way
Next Topic:	lazy loading problem

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 11:44:45 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter