Home » Eclipse Projects » EclipseLink » Problems Executing Stored Procedure in DB2 with EclipseLink v1.2
| Problems Executing Stored Procedure in DB2 with EclipseLink v1.2 [message #664167] |
Thu, 07 April 2011 17:14  |
 No real name
Messages: 2
Registered: April 2011 |
Junior Member |
|
|
Hello,
I am having an issue executing a stored procedure using Eclipselink v1.2 running on Weblogic 10.0 (JDK 5) and DB2 v9.1 on z/OS. I have the following stored procedure:
CREATE PROCEDURE
PROD.SUBS
(IN GROUP_ID_VAR DECIMAL(10)
,OUT SQLCODE_OUT INTEGER
) VERSION VERSION1
LANGUAGE SQL
VALIDATE BIND
DYNAMICRULES BIND
QUALIFIER YUT
DISABLE DEBUG MODE
DYNAMIC RESULT SET 1
ASUTIME NO LIMIT
COMMIT ON RETURN NO
P1: BEGIN
DECLARE SQLCODE INT;
DECLARE TEMP_GROUP_ID DEC (10);
--DECLARE SUB_COUNT INT;
DECLARE ESA CURSOR WITH RETURN FOR
... SOME BORING SQL
OPEN ESA;
SET SQLCODE_OUT = SQLCODE;
END P1
I am executing this via a NamedStoredProcedure annotation :
@NamedStoredProcedureQueries( {
@NamedStoredProcedureQuery(
name="subs",
procedureName="PROD.SUBS",
returnsResultSet=true,
parameters={
@StoredProcedureParameter(queryParameter="groupIdVar",direction=Direction.IN,type=BigDecimal.class),
@StoredProcedureParameter(queryParameter="sqlCodeOut",direction=Direction.OUT,type=Integer.class)
},
resultClass=Subs.class,
resultSetMapping="subsResult"
)
} )
@SqlResultSetMapping( name="subsResult", entities={
@EntityResult( entityClass=Subs.class , fields = {
@FieldResult( name="firstName", column="FIRSTNAME" ),
@FieldResult( name="lastName", column="LASTNAME" ),
@FieldResult( name="streetAddressLine1", column="STREET" ),
@FieldResult( name="streetAddressLine2", column="STREETNUMBER" ),
@FieldResult( name="city", column="CITY" ),
@FieldResult( name="state", column="STATE" ),
@FieldResult( name="zipCode", column="ZIPCODE" ),
@FieldResult( name="country", column="COUNTRY" ),
@FieldResult( name="dayPhone", column="BUSINESSPHONE" ),
@FieldResult( name="evePhone", column="PRIVATEPHONE" ),
@FieldResult( name="email", column="EMAIL" )
})
}
)
The interesting bit is the following:
This is being executed from a JPA Controller via the following:
EntityManager em = null;
try {
em = getEntityManager();
Query q = em.createNamedQuery( "subs" );
q.setParameter( "groupIdVar", new BigDecimal(groupId) );
Subs subs = (Subs)q.getSingleResult();
} catch( Exception e ) {
e.printStackTrace();
}
When I am running the query I get the following error:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.2.0.v20091016-r5565): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.b.eo: [jcc][10413][10940][3.53.95] Literal replacement parsing failed for procedure call to DB2 for z/OS. Failing SQL text CALL PROD.SUBS(groupIdVar = ?, sqlCodeOut = ?). ERRORCODE=-4463, SQLSTATE=42601
Error Code: -4463
Call: CALL PROD.SUBS(groupIdVar = ?, sqlCodeOut = ?)
bind => [13488062, => sqlCodeOut]
Query: ReadAllQuery(name="subs" referenceClass=Subs )
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1411)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:677)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:528)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:599)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:455)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:997)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:670)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:958)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:432)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1021)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2858)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1299)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1299)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1299)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1299)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:420)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:669)
The interesting bit is the following:
Call: CALL PROD.SUBS(groupIdVar = ?, sqlCodeOut = ?)
As I am not sure exactly what SQL that EclipseLink is generating, because the correct syntax for executing a stored procedure in DB2 is :
I have tried using specifying the name of the parameters with the same result.
Can anyone shed some light on success/failure with executing stored procedures to DB2?
Thank you,
Chuck
|
|
| | | | | | |
Goto Forum:
Current Time: Fri Apr 19 18:14:41 GMT 2024
Powered by FUDForum. Page generated in 0.04083 seconds |
] 
Search
Help
Register
Login
Home
