Skip to main content


Eclipse Community Forums

      Home
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 13:14 Go to next message
Eclipse UserFriend
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 :
 CALL PROD.SUBS(?, ?);


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
Re: Problems Executing Stored Procedure in DB2 with EclipseLink v1.2 [message #664190 is a reply to message #664167] Thu, 07 April 2011 15:13 Go to previous messageGo to next message
Eclipse UserFriend
I just wanted to inform everyone that I have fixed the problem by manually editing the DB2Platform.java source file in the org.eclipse.persistence.platform.database package in order to have the call formatted properly. I am stuck using v1.2 and I believe in v2.0 this is working fine but for us stuck in the JDK 5 world, this is necessary (well, my job dictates that we use JDK 5 and JPA v1.0).

If anyone is interested in the changes made, please reply.
Re: Problems Executing Stored Procedure in DB2 with EclipseLink v1.2 [message #664680 is a reply to message #664190] Mon, 11 April 2011 11:09 Go to previous messageGo to next message
Eclipse UserFriend
The issue is that the query is defined using named parameters, but DB2 does not support names. In 2.3 there will be a callByIndex option on the named query that allows the query to call by index.

You can also fix it in the platform, by always generating an indexed call as you have done. Please log the bug, attach your platform changes, and vote for it.
Re: Problems Executing Stored Procedure in DB2 with EclipseLink v1.2 [message #753474 is a reply to message #664680] Thu, 27 October 2011 08:22 Go to previous messageGo to next message
Eclipse UserFriend
I used EclipseLink 2.3 and DB2. I set the CallByIndex flag to true. However, SqlResultSetMapping does not work properly.

I have something like
@SqlResultSetMapping( name="subsResult", entities={
@EntityResult( entityClass=Subs.class , fields = {
@FieldResult( name="firstname", column="NAME_VALUE" )
}
}

However, the result column NAME_VALUE failed to set to Subs.class, unless I have

public class Subs {
@Column("NAME_VALUE")
private string firstname;
}

But it is not what I want. Please kindly let me know how to fix it. Did the change in DB2Platform.java can do the result set mapping properly?

Many Thanks
Re: Problems Executing Stored Procedure in DB2 with EclipseLink v1.2 [message #753475 is a reply to message #664680] Thu, 27 October 2011 08:22 Go to previous messageGo to next message
Eclipse UserFriend
I used EclipseLink 2.3 and DB2. I set the CallByIndex flag to true. However, SqlResultSetMapping does not work properly.

I have something like
@SqlResultSetMapping( name="subsResult", entities={
@EntityResult( entityClass=Subs.class , fields = {
@FieldResult( name="firstname", column="NAME_VALUE" )
}
}

However, the result column NAME_VALUE failed to set to Subs.class, unless I have

public class Subs {
@Column("NAME_VALUE")
private string firstname;
}

But it is not what I want. Please kindly let me know how to fix it. Did the change in DB2Platform.java can do the result set mapping properly?

Many Thanks
Re: Problems Executing Stored Procedure in DB2 with EclipseLink v1.2 [message #753545 is a reply to message #753475] Thu, 27 October 2011 11:59 Go to previous messageGo to next message
Eclipse UserFriend
Hello,

This thread was about a problem executing a stored procedure, while your problem seems to be with building the results afterward using a SQLResultSetMapping and so likely are not related. You might want to post the problem, the query and the generated SQL using EclipseLink logging set to finest in a new thread. Not much can be said on what is going wrong without information on how you set up the query and set it to use the SQLResultSetMapping you have defined.

Best Regards,
Chris
Re: Problems Executing Stored Procedure in DB2 with EclipseLink v1.2 [message #753547 is a reply to message #753475] Thu, 27 October 2011 11:59 Go to previous message
Eclipse UserFriend
Hello,

This thread was about a problem executing a stored procedure, while your problem seems to be with building the results afterward using a SQLResultSetMapping and so likely are not related. You might want to post the problem, the query and the generated SQL using EclipseLink logging set to finest in a new thread. Not much can be said on what is going wrong without information on how you set up the query and set it to use the SQLResultSetMapping you have defined.

Best Regards,
Chris
Previous Topic:create-tables doesn't create foreign key constraints
Next Topic:Cache Coordination issue
Goto Forum:
  


Current Time: Wed Jul 23 18:34:25 EDT 2025

Powered by FUDForum. Page generated in 0.43735 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