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 next 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)

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 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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.





James : Wiki : Book : Blog : Twitter
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 Go to previous messageGo to next message
Ronny  is currently offline Ronny
Messages: 7
Registered: October 2011
Junior Member
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 Go to previous messageGo to next message
Ronny  is currently offline Ronny
Messages: 7
Registered: October 2011
Junior Member
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 Go to previous messageGo to next message
Ronny is currently offline Ronny
Messages: 10
Registered: July 2009
Junior Member
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 #755536 is a reply to message #755310] Tue, 08 November 2011 13:02 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1018
Registered: July 2009
Senior Member
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 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1018
Registered: July 2009
Senior Member
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
Previous Topic:Calling preparedStatement/Function the JPA way
Next Topic:lazy loading problem
Goto Forum:
  


Current Time: Tue Sep 02 05:23:23 EDT 2014

Powered by FUDForum. Page generated in 0.12729 seconds