title: JPA stored procedure call not working - wrong sql?
body: <p>I am new to JPA and I am trying to call a DB2 stored procedure.  However from what I can tell the SQL being generated is wrong so it is failing.</p>
  
  <p>Here is how I have the stored procedure setup:</p>
  
  <pre><code>@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)})
  </code></pre>
  
  <p>Here is my code that is calling the stored procedure:</p>
  
  <pre><code>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();
  </code></pre>
  
  <p>Based of what I saw when I stepped through the code and what is in the logs this is the sql statement that I see.</p>
  
  <p>CALL prc_get_all_appl_var(ip_cus_set_c = ?, ip_cps_ver_n = ?, op_sqlstate = ?, op_sqlcode = ?, op_msg = ?)</p>
  
  <p>but it should be </p>
  
  <p>CALL prc_get_all_appl_var("CONS",500,?,?,?)</p>
  
  <p>Here is the stack trace.</p>
  
  <blockquote>
    <p>[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</p>
  </blockquote>