Skip to main content


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 [message #753604] Fri, 28 October 2011 01:18 Go to next message
Ronny is currently offline RonnyFriend
Messages: 10
Registered: July 2009
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(8)
)
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

Re: Failed to map sql result set when execute stored procedure returning cursor [message #753762 is a reply to message #753604] Fri, 28 October 2011 16:18 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
For #1, I'm not sure whats going wrong; if something is missing, or if it is a bug. It might be better to file a bug with a test case to have some one look at it. If it is a bug, specifying a result class or a resultset mapping to return just a field would be the workaround.

For #2, You must specify a result class OR a resultset mapping to tell the query how the results are to be returned. The problem here is that the resultset mapping is ignored in favor of the result-class tag. Because the field returned is "PRODUCT" (I assume anyway based on your resultmapping) and the entity requires/expects a "STRINGVALUE" field, the exception occurs. Removing the result-class tag from the query definition should resolve the issue since the entity will then only be built using the SQL resultset mapping.

Unless your application requires it, there is no reason to create an entity for the return values. A resultset mapping can be defined to return the "PRODUCT" using a simple <column-result name="PRODUCT"/> tag instead of the entity-result tag.

Best Regards,
Chris

Report message to a moderator

Re: Failed to map sql result set when execute stored procedure returning cursor [message #753764 is a reply to message #753604] Fri, 28 October 2011 16:18 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
For #1, I'm not sure whats going wrong; if something is missing, or if it is a bug. It might be better to file a bug with a test case to have some one look at it. If it is a bug, specifying a result class or a resultset mapping to return just a field would be the workaround.

For #2, You must specify a result class OR a resultset mapping to tell the query how the results are to be returned. The problem here is that the resultset mapping is ignored in favor of the result-class tag. Because the field returned is "PRODUCT" (I assume anyway based on your resultmapping) and the entity requires/expects a "STRINGVALUE" field, the exception occurs. Removing the result-class tag from the query definition should resolve the issue since the entity will then only be built using the SQL resultset mapping.

Unless your application requires it, there is no reason to create an entity for the return values. A resultset mapping can be defined to return the "PRODUCT" using a simple <column-result name="PRODUCT"/> tag instead of the entity-result tag.

Best Regards,
Chris

Report message to a moderator

Re: Failed to map sql result set when execute stored procedure returning cursor [message #754054 is a reply to message #753604] Mon, 31 October 2011 15:32 Go to previous message
James is currently offline JamesFriend
Messages: 272
Registered: July 2009
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 : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Report message to a moderator

Previous Topic:persisting a hashmap
Next Topic:double post
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 20:12:35 GMT 2024

Powered by FUDForum. Page generated in 0.03072 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly