Topic
6 replies Latest Post - ‏2010-12-22T20:51:46Z by sutter
steve_6401
steve_6401
4 Posts
ACCEPTED ANSWER

Pinned topic CNTR0020E - Invalid argument: parameter index 0 is out of range.

‏2010-12-17T15:57:52Z |
I'm getting a "CNTR0020E" error on a simple javax.persistence.EntityManager query using WS 7 and the OpenJPA features. The exception states "Invalid argument: parameter index 0 is out of range."

I've sanitized and formatted the log below for and I'm stumped as to where to look.

Any help/tips is greatly appreciated.
  • Steve
12/16/10 17:26:13:543 EST 0000001d AccountDao I com.myplace.dao.AccountDao loadEntityHeader query:
select T2.ACCT_NBR, t1.MY_DATE1, t1.MY_DATE2 from MYSCHEMA.MY_TABLE t1, MYSCHEMA.MVI_ACCOUNT t2 where t1.ACCT_NBR = ?
and t1.OBJ_NBR = ? and t2.ACCT_NBR = t1.ACCT_NBR
12/16/10 17:26:13:559 EST 0000001d BusinessExcep E CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "loadEntityHeader" on bean "BeanId(app-business-logic#db-service-0.1.jar#MyServiceBean, null)".
Exception data: <openjpa-2.0.1-SNAPSHOT-r422266:980199 fatal general error> org.apache.openjpa.persistence.PersistenceException:
Invalid argument: parameter index 0 is out of range.
FailedObject: select T2.ACCT_NBR, t1.MY_DATE1, t1.MY_DATE2 from MYSCHEMA.MY_TABLE t1, MYSCHEMA.MVI_ACCOUNT t2 where t1.ACCT_NBR = ? and t1.OBJ_NBR = ? and t2.ACCT_NBR = t1.ACCT_NBR http://java.lang.String
at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4832)
at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4792)
at org.apache.openjpa.jdbc.sql.DB2Dictionary.newStoreException(DB2Dictionary.java:564)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:118)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:70)
at org.apache.openjpa.jdbc.kernel.GenericResultObjectProvider.handleCheckedException(GenericResultObjectProvider.java:125)
at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:40)
at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1246)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1005)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:861)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:792)
at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542)
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:288)
at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:302)
====================================================================================================
wsjpaversion>
WSJPA 2.0.1-SNAPSHOT
version id: WSJPA-2.0.1-SNAPSHOT-r1118:2100
WebSphere JPA svn revision: 1118:2100

OpenJPA 2.0.1-SNAPSHOT
version id: openjpa-2.0.1-SNAPSHOT-r422266:980199
Apache svn revision: 422266:980199

os.name: Windows XP
os.version: 5.1 build 2600 Service Pack 3
os.arch: x86

java.version: 1.6.0
java.vendor: IBM Corporation

java.class.path:
{WAS-INSTALL-ROOT}\feature_packs\jpa\dev\jpa_api.jar
{WAS-INSTALL-ROOT}\dev\JavaEE\j2ee.jar
{WAS-INSTALL-ROOT}\feature_packs\jpa\plugins\com.ibm.ws.jpa.jar
{WAS-INSTALL-ROOT}\plugins\com.ibm.ws.prereq.commons-collections
.jar

user.dir: {WAS-INSTALL-ROOT}\profiles\AppSrv01\bin
Updated on 2010-12-22T20:51:46Z at 2010-12-22T20:51:46Z by sutter
  • steve_6401
    steve_6401
    4 Posts
    ACCEPTED ANSWER

    Re: CNTR0020E - Invalid argument: parameter index 0 is out of range.

    ‏2010-12-17T17:10:43Z  in response to steve_6401
    Got an idea that somehow this error is related to our DB2 version (V8R1M0) and the settings in the persistence.xml file.

    In the ffdc log I see:
    com.ibm.db2.jcc.b.SqlException: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
    ....
    at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecuteQuery(WSJdbcStatement.java:1621)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeQuery(WSJdbcStatement.java:925)
    at org.apache.openjpa.lib.jdbc.DelegatingStatement.executeQuery(DelegatingStatement.java:131)

    • Steve
    • sutter
      sutter
      93 Posts
      ACCEPTED ANSWER

      Re: CNTR0020E - Invalid argument: parameter index 0 is out of range.

      ‏2010-12-17T17:31:02Z  in response to steve_6401
      Hi Steve,
      The two issues may be related, but not sure at this point. You seem to think that maybe something in your persistence.xml might be affecting this. Can you post your persistence.xml? Also, can you tell us a bit more about your scenario?

      A quick guess is that maybe the Prepared SQL Cache is hitting a snag with the queries you are attempting to use. If you want to try a quick change to your persistence.xml to see if this helps or not, it could potentially help isolate the problem. It's just a guess at this point.

      <property name="openjpa.jdbc.QuerySQLCache" value="false">

      Also, are there other messages in the logs, traces, or ffdc that could help explain the situation? The pieces are not all fitting together yet for me...

      Thanks,
      Kevin
      • steve_6401
        steve_6401
        4 Posts
        ACCEPTED ANSWER

        Re: CNTR0020E - Invalid argument: parameter index 0 is out of range.

        ‏2010-12-17T18:38:49Z  in response to sutter
        Kevin,
        Thanks for the reply.

        I tried the persistence.xml suggestion and got the same result.

        The persistence.xml
        
        <?xml version=
        "1.0" encoding=
        "UTF-8"?> <persistence xmlns=
        "http://java.sun.com/xml/ns/persistence" xmlns:xsi=
        "http://www.w3.org/2001/XMLSchema-instance" version=
        "1.0" xsi:schemaLocation=
        "http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"> <persistence-unit name=
        "myPersistence"> <provider> org.apache.openjpa.persistence.PersistenceProviderImpl </provider> <jta-data-source>jdbc/MyDataSource</jta-data-source> <non-jta-data-source>jdbc/MyDataSource</non-jta-data-source> <class>com.mycompany.MyEntity</class> <snip> ... bunch of entity classes </snip>   <exclude-unlisted-classes>true</exclude-unlisted-classes>   <properties> <property name=
        "openjpa.TransactionMode" value=
        "managed" /> <property name=
        "openjpa.ConnectionFactoryMode" value=
        "managed" /> <property name=
        "openjpa.jdbc.DBDictionary" value=
        "db2" /> <property name=
        "openjpa.jdbc.QuerySQLCache" value=
        "false"/> </properties>   </persistence-unit> </persistence>
        


        First and second ffdc message:
        12/17/10 13:14:03:329 EST FFDC Exception:com.ibm.db2.jcc.b.SqlException SourceId:com.ibm.ws.rsadapter.jdbc.WSJccStatement.pmiExecuteQuery ProbeId:1315 Reporter:com.ibm.ws.rsadapter.jdbc.WSJccStatement@a7a0a7a
        com.ibm.db2.jcc.b.SqlException: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
        Third ffdc message:
        12/17/10 13:14:04:813 EST FFDC Exception:com.ibm.db2.jcc.b.SqlException SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.findColumn ProbeId:384 Reporter:com.ibm.ws.rsadapter.jdbc.WSJccResultSet@6e586e58
        com.ibm.db2.jcc.b.SqlException: Invalid argument: unknown column name ID
        at com.ibm.db2.jcc.b.m.a(m.java:1265)
        Fourth ffdc message:
        12/17/10 13:14:04:829 EST FFDC Exception:com.ibm.db2.jcc.b.SqlException SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getLong ProbeId:1509 Reporter:com.ibm.ws.rsadapter.jdbc.WSJccResultSet@6e586e58
        com.ibm.db2.jcc.b.SqlException: Invalid argument: parameter index 0 is out of range.
        Fifth ffdc message (I suspect this is an attempted rollback):
        12/17/10 13:14:04:845 EST FFDC Exception:com.ibm.db2.jcc.b.SqlException SourceId:com.ibm.ws.rsadapter.jdbc.WSJccPreparedStatement.closeWrapper ProbeId:310 Reporter:com.ibm.ws.rsadapter.jdbc.WSJccPreparedStatement@3de03de0
        com.ibm.db2.jcc.b.SqlException: Invalid cursor name.

        More details:

        {db2-path}db2>java -cp db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version
        IBM DB2 JDBC Universal Driver Architecture 2.6.80
        • sutter
          sutter
          93 Posts
          ACCEPTED ANSWER

          Re: CNTR0020E - Invalid argument: parameter index 0 is out of range.

          ‏2010-12-20T16:29:17Z  in response to steve_6401
          Hi Steve,
          Thanks for trying the QuerySQLCache setting. It was a just a guess based on the error you were receiving. Since that didn't help at all, please remove it from your persistence.xml so that we don't confuse future efforts. Thanks.

          So, let's get back to the problem... The error message from DB2 indicates that you are probably running in v8 "compatibility" mode. Have you tried running in "new function" mode? I'm not sure what aspect of the database interaction is triggering this exception message. But, we need to get around that first.

          Concerning your persistence.xml properties...

          <property name="openjpa.TransactionMode" value="managed" />
          <property name="openjpa.ConnectionFactoryMode" value="managed" />
          <property name="openjpa.jdbc.DBDictionary" value="db2" />

          Why are you explicitly setting the TransactionMode and ConnectionFactoryMode? If you are using WebSphere JPA within the managed WebSphere app server (ejbs or servlets), then these configuration properties are automatically set for you. And, since you are using the <jta-data-source> and <non-jta-data-source> elements, it looks like you are relying on WebSphere's management of resources.

          The DBDictionary setting is also not a requirement, but it does prevent an extra call to the configured data-source to determine the proper dictionary. So, leaving this problem is no problem. Just curious.

          If you can provide some information on your Entity definitions, especially the ones involved with these error path, that would also be helpful. It looks like you are getting several errors from our interaction with DB2. Hopefully, most of those would clear up once you are running with "new function" mode.

          Thanks,
          Kevin
          • steve_6401
            steve_6401
            4 Posts
            ACCEPTED ANSWER

            Re: CNTR0020E - Invalid argument: parameter index 0 is out of range.

            ‏2010-12-22T17:01:23Z  in response to sutter
            1. Back from creating a OpenJPA/dbunit test to simulate what the server was doing .. alas, no joy, my unit test ended up working fine except that I was using OpenJpa 1.2.2 which was as close to OpenJPA 1.2.3-SNAPSHOT as I could find. Kevin, got any tips on using JUNIT to simulate OpenJPA transactions on Websphere supplied OpenJPA classes?

            2. Took out the property settings you questioned. Don't know why they were there. I didn't write this project; it is being ported from JBOSS to Websphere and as the original developer never ceases to remind me "It worked fine on JBOSS!".

            3. Turned up the tracing on openjpa and now I have some more information.
            Specifically:
            
            WSRdbDataSour W   DSRA9542W: The JDBC Driver that is configured with the data source 
            
            for the Application Server does not support the extending data source properties feature. The Application Server will not honor the extended data source properties. InternalGener I   DSRA8203I: Database product name : DB2 InternalGener I   DSRA8204I: Database product version : DSN08010 InternalGener I   DSRA8205I: JDBC driver name  : IBM DB2 JDBC Universal Driver Architecture InternalGener I   DSRA8206I: JDBC driver version  : 2.6.80 InternalDB2Un I   DSRA8212I: DataStoreHelper name is: com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper@59be59be. WSRdbDataSour I   DSRA8208I: JDBC driver type  : 4
            


            So, off to the Google! I'll report back what I find.
            • Steve
            • sutter
              sutter
              93 Posts
              ACCEPTED ANSWER

              Re: CNTR0020E - Invalid argument: parameter index 0 is out of range.

              ‏2010-12-22T20:51:46Z  in response to steve_6401
              Hi Steve,
              > 1. Back from creating a OpenJPA/dbunit test to simulate what the server was doing .. alas, no joy, my unit test ended up working fine except that I was using OpenJpa 1.2.2 which was as close to OpenJPA 1.2.3-SNAPSHOT as I could find. Kevin, got any tips on using JUNIT to simulate OpenJPA transactions on Websphere supplied OpenJPA classes?
              >
              Not exactly sure what you are asking for... OpenJPA transactions can be invoked directly by obtaining the Transaction object from the EntityManager and then invoking begin(), rollback(), commit(), etc. These calls are easily done from a JUnit.

              Or, are you asking how to simulate WebSphere demarcated transactions through the OpenJPA Transaction interface? This will depend on how much work you want to go to. You can mock up a UserTransaction object and just have it call through the EM.Transaction object. But, that might be overkill. Normally, just having your Junit tests drive the EM.Transaction demarcation is sufficient from testing your Entity interactions perspective.

              > 2. Took out the property settings you questioned. Don't know why they were there. I didn't write this project; it is being ported from JBOSS to Websphere and as the original developer never ceases to remind me "It worked fine on JBOSS!".
              >
              Sure. Like I said, the properties you had defined shouldn't hurt anything. I was just curious why they were being used. It could be that JBoss doesn't integrate as well with the JPA providers and, thus, OpenJPA needed these hints to know what was going on. With WebSphere, the OpenJPA provider is configured so that these hints are not necessary.

              > 3. Turned up the tracing on openjpa and now I have some more information.
              > Specifically:
              >
              
              > WSRdbDataSour W   DSRA9542W: The JDBC Driver that is configured with the data source 
              
              for the Application Server does not support the extending data source properties feature. The Application Server will not honor the extended data source properties. > InternalGener I   DSRA8203I: Database product name : DB2 > InternalGener I   DSRA8204I: Database product version : DSN08010 > InternalGener I   DSRA8205I: JDBC driver name  : IBM DB2 JDBC Universal Driver Architecture > InternalGener I   DSRA8206I: JDBC driver version  : 2.6.80 > InternalDB2Un I   DSRA8212I: DataStoreHelper name is: com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper@59be59be. > WSRdbDataSour I   DSRA8208I: JDBC driver type  : 4 >
              

              >
              Okay, this is the area that we need to focus on. Earlier you said that this application worked okay with JBoss. Was that with the same db2 instance? Or, some other database? Along those lines, can you verify the application using some other db2 instance (ie. a Distributed instance, not z/OS)? I suppose this depends on the requirements and expectations of this exercise.

              Good luck,
              Kevin
              > So, off to the Google! I'll report back what I find.
              > - Steve