| Failed to map sql result set when execute stored procedure returning cursor [message #753604] |
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(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)
|
|
|
|
|
| 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 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.
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
|
|
|
Powered by
FUDForum. Page generated in 0.03449 seconds
Home