Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » DB2 Stored Proc Call - Query is wrong
DB2 Stored Proc Call - Query is wrong [message #1152118] Wed, 23 October 2013 20:41
Doug Young is currently offline Doug Young
Messages: 2
Registered: October 2013
Junior Member
I am brand new to JPA and I am trying to execute a DB2 stored procedure call. I have stepped through the code and based on what I saw and the log output it seems the generated call is incorrect.

I'm seeing - CALL prc_get_all_appl_var(ip_cus_set_c = ?, ip_cps_ver_n = ?, op_sqlstate = ?, op_sqlcode = ?, op_msg = ?)

but it should be - CALL prc_get_all_appl_var('CONS',500,?,?,?)

Here is what I have setup:

@NamedStoredProcedureQuery(name = "getApplVars", procedureName = "prc_get_all_appl_var", resultClasses = ApplVars.class,
parameters = {@StoredProcedureParameter(mode = ParameterMode.IN, name = "ip_cus_set_c", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "ip_cps_ver_n", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "op_sqlstate", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "op_sqlcode", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "op_msg", type = String.class)})


EntityManagerFactory emf = Persistence.createEntityManagerFactory("DB1");
EntityManager em = emf.createEntityManager();
StoredProcedureQuery spq = em.createNamedStoredProcedureQuery("getApplVars");
spq.setParameter("ip_cus_set_c","CONS");
spq.setParameter("ip_cps_ver_n",500);
spq.getResultList();

Here is my exception:

[10/23/13 13:43:15:703 MDT] 00000036 SystemOut O [EL Warning]: 2013-10-23 13:43:15.698--UnitOfWork(1084751114)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC==;ppl_var(ip_cus_set_c;+, DRIVER=4.17.28
Error Code: -104
Call: CALL prc_get_all_appl_var(ip_cus_set_c = ?, ip_cps_ver_n = ?, op_sqlstate = ?, op_sqlcode = ?, op_msg = ?)
bind => [5 parameters bound]
Query: ResultSetMappingQuery(name="getApplVars" )
[10/23/13 13:43:15:707 MDT] 00000036 SystemOut O e: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC==;ppl_var(ip_cus_set_c;+, DRIVER=4.17.28
Error Code: -104
Call: CALL prc_get_all_appl_var(ip_cus_set_c = ?, ip_cps_ver_n = ?, op_sqlstate = ?, op_sqlcode = ?, op_msg = ?)
bind => [5 parameters bound]
Query: ResultSetMappingQuery(name="getApplVars" )
[10/23/13 13:43:15:707 MDT] 00000036 SystemErr R javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC==;ppl_var(ip_cus_set_c;+, DRIVER=4.17.28
Error Code: -104
Call: CALL prc_get_all_appl_var(ip_cus_set_c = ?, ip_cps_ver_n = ?, op_sqlstate = ?, op_sqlcode = ?, op_msg = ?)
bind => [5 parameters bound]
Query: ResultSetMappingQuery(name="getApplVars" )
[10/23/13 13:43:15:735 MDT] 00000036 SystemErr R at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378)
[10/23/13 13:43:15:735 MDT] 00000036 SystemErr R at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
[10/23/13 13:43:15:735 MDT] 00000036 SystemErr R at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:316)
[10/23/13 13:43:15:735 MDT] 00000036 SystemErr R at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.getResultList(StoredProcedureQueryImpl.java:548)
[10/23/13 13:43:15:736 MDT] 00000036 SystemErr R at cpstools.CPSToolsServlet.doGet(CPSToolsServlet.java:62)
[10/23/13 13:43:15:736 MDT] 00000036 SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:575)
[10/23/13 13:43:15:736 MDT] 00000036 SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
[10/23/13 13:43:15:737 MDT] 00000036 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1240)
[10/23/13 13:43:15:737 MDT] 00000036 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:760)
[10/23/13 13:43:15:737 MDT] 00000036 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:443)
[10/23/13 13:43:15:738 MDT] 00000036 SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1033)
[10/23/13 13:43:15:738 MDT] 00000036 SystemErr R at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:4499)
[10/23/13 13:43:15:738 MDT] 00000036 SystemErr R at com.ibm.ws.webcontainer.osgi.DynamicVirtualHost$2.handleRequest(DynamicVirtualHost.java:282)
[10/23/13 13:43:15:738 MDT] 00000036 SystemErr R at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:954)
[10/23/13 13:43:15:738 MDT] 00000036 SystemErr R at com.ibm.ws.webcontainer.osgi.DynamicVirtualHost$2.run(DynamicVirtualHost.java:252)
[10/23/13 13:43:15:739 MDT] 00000036 SystemErr R at com.ibm.ws.http.dispatcher.internal.channel.HttpDispatcherLink$TaskWrapper.run(HttpDispatcherLink.java:584)
[10/23/13 13:43:15:739 MDT] 00000036 SystemErr R at com.ibm.ws.threading.internal.Worker.executeWork(Worker.java:439)
[10/23/13 13:43:15:739 MDT] 00000036 SystemErr R at com.ibm.ws.threading.internal.Worker.run(Worker.java:421)
[10/23/13 13:43:15:739 MDT] 00000036 SystemErr R at java.lang.Thread.run(Thread.java:744)
[10/23/13 13:43:15:740 MDT] 00000036 SystemErr R Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException

Previous Topic:EclipseLink + JPA + Generic Entity + SINGLE_TABLE Inheritance
Next Topic:Coonection timesout after 8 hours
Goto Forum:
  


Current Time: Thu Oct 23 19:14:08 GMT 2014

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

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