2 Replies Latest reply on Dec 13, 2005 2:28 PM by jason1

    Error: Missing IN or OUT param when using JBoss Declared SQL

      Hi,

      I am trying to modify a "finder" to only return the list of PKs not all fields in the entity bean. I see in the logs that JBoss has parsed the declaredSQL properly, but debug through it, I see that it errors because the 'parameters' attribute in JDBCAbstractQueryCommand is size = 0. It would think that it should be 1 based on my ejb-jar.xml and jbosscmp-jdbc.xml configuration (see below).

      If anyone can either help me resolve this or recommend a better way to configure the finder to only return the list of PKs I'd appreciate it.


      version: JBoss 4.0.2


      exception:

      javax.ejb.FinderException: Find failed: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:245)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:128)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:40)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:598)
      at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:322)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntities(CachedConnectionInterceptor.java:321)
      at org.jboss.ejb.EntityContainer.findLocal(EntityContainer.java:649)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
      at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1113)
      at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:90)
      at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:90)
      at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:192)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:212)
      at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:90)
      at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:117)
      at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
      at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
      at org.jboss.ejb.plugins.CallValidationInterceptor.invokeHome(CallValidationInterceptor.java:41)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:109)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:146)
      at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:116)
      at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:121)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
      at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:508)
      at org.jboss.ejb.Container.invoke(Container.java:894)
      at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invokeHome(BaseLocalProxyFactory.java:342)
      at org.jboss.ejb.plugins.local.LocalHomeProxy.invoke(LocalHomeProxy.java:118)
      at $Proxy148.findByDeviceId(Unknown Source)
      at cdot.ctms.layer.services.comm.test.TestDeviceExtService.testFindByDeviceId(TestDeviceExtService.java:375)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at junit.framework.TestCase.runTest(TestCase.java:154)
      at junit.framework.TestCase.runBare(TestCase.java:127)
      at net.sourceforge.junitejb.EJBTestCase.runBare(EJBTestCase.java:90)
      at net.sourceforge.junitejb.JMXTestRunner.runTestCase(JMXTestRunner.java:233)
      at net.sourceforge.junitejb.JMXTestRunner.run(JMXTestRunner.java:83)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:121)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:127)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.jmx.connector.invoker.InvokerAdaptorService.invoke(InvokerAdaptorService.java:251)
      at sun.reflect.GeneratedMethodAccessor94.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:121)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:127)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
      at org.jboss.invocation.jrmp.server.JRMPProxyFactory.invoke(JRMPProxyFactory.java:164)
      at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
      at org.jboss.invocation.jrmp.server.JRMPInvoker$MBeanServerAction.invoke(JRMPInvoker.java:805)
      at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:406)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
      at sun.rmi.transport.Transport$1.run(Transport.java:148)
      at java.security.AccessController.doPrivileged(Native Method)
      at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
      at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
      at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
      at java.lang.Thread.run(Thread.java:534)
      Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
      at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1539)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2883)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
      at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:78)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:228)
      ... 92 more

      Logging confirming JBoss parsed declared SQL properly:

      2005-12-12 11:27:25,361 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.DeviceExtRO#findByDeviceId] SQL: SELECT DEVICE_EXT_ID FROM DEVICE_EXT WHERE DEVICE_ID = ? ORDER BY DEVICE_EXT_ID ASC

      Defaults section from standardjbosscmp-jdbc.xml

       <defaults>
       <datasource>java:/OracleDS</datasource>
       <!-- optional since 4.0 <datasource-mapping>Hypersonic SQL</datasource-mapping> -->
      
       <create-table>true</create-table>
       <remove-table>false</remove-table>
       <read-only>false</read-only>
       <read-time-out>300000</read-time-out>
       <row-locking>false</row-locking>
       <pk-constraint>true</pk-constraint>
       <fk-constraint>false</fk-constraint>
       <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
      
       <!-- Default
       <read-ahead>
       <strategy>on-load</strategy>
       <page-size>1000</page-size>
       <eager-load-group>*</eager-load-group>
       </read-ahead>
       -->
      
       <!-- Performance modification. This will load all attributes
       immediately into the CMP -->
       <read-ahead>
       <strategy>on-find</strategy>
       <page-size>255</page-size>
       <eager-load-group>*</eager-load-group>
       </read-ahead>
      
       <list-cache-max>1000</list-cache-max>
      
       <clean-read-ahead-on-load>false</clean-read-ahead-on-load>
      
       <unknown-pk>
       <key-generator-factory>UUIDKeyGeneratorFactory</key-generator-factory>
       <unknown-pk-class>java.lang.String</unknown-pk-class>
       <jdbc-type>VARCHAR</jdbc-type>
       <sql-type>VARCHAR(32)</sql-type>
       </unknown-pk>
      
       <!-- Default: Removed since CTMS generates its own PKs
       <entity-command name="default"/>
       -->
      
       <!-- Added. entity-command that
       prevents extra SELECT COUNT(*) check for duplicate IDs -->
       <entity-command name="no-select-before-insert"/>
      
       <!-- Default
       <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLCompiler</ql-compiler>
       -->
      
       <!-- This is the new SQL92 compliant compiler that generates more efficient
       and robust CMP SQL -->
       <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler>
       </defaults>
      
      


      jboss.xml configuration:

      <entity>
       <ejb-name>DeviceExtRO</ejb-name>
       <local-jndi-name>DeviceExtRO</local-jndi-name>
       <read-only>true</read-only>
       <configuration-name>Standard CMP 2.x EntityBean with cache invalidation</configuration-name>
       <cache-invalidation>true</cache-invalidation>
       <cache-invalidation-config>
       <invalidation-group-name>DeviceExtGroup</invalidation-group-name>
       </cache-invalidation-config>
       </entity>
      


      jbosscmp-jdbc.xml configuration:

      <entity>
       <ejb-name>DeviceExtRO</ejb-name>
       <datasource>java:/OracleDS</datasource>
       <datasource-mapping>Oracle9i</datasource-mapping>
       <create-table>false</create-table>
       <remove-table>false</remove-table>
      
       <read-only>true</read-only>
       <!-- 5 minutes -->
       <read-time-out>300000</read-time-out>
       <row-locking>false</row-locking>
      
       <table-name>DEVICE_EXT</table-name>
      
       <cmp-field>
       <field-name>deviceExtId</field-name>
       <column-name>DEVICE_EXT_ID</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>nameTxt</field-name>
       <column-name>NAME_TXT</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>valueTxt</field-name>
       <column-name>VALUE_TXT</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>deviceId</field-name>
       <column-name>DEVICE_ID</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>typeDeviceCd</field-name>
       <column-name>TYPE_DEVICE_CD</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>typeDatatypeCd</field-name>
       <column-name>TYPE_DATATYPE_CD</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>createDt</field-name>
       <column-name>CREATE_DT</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>createUserTxt</field-name>
       <column-name>CREATE_USER_TXT</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>lastUpdateDt</field-name>
       <column-name>LAST_UPDATE_DT</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>updateUserTxt</field-name>
       <column-name>UPDATE_USER_TXT</column-name>
      
       </cmp-field>
       <query>
       <description>Loads only list of PKs since rest of entity data should be cached</description>
       <query-method>
       <method-name>findByDeviceId</method-name>
       <method-params>
       <method-param>java.math.BigDecimal</method-param>
       </method-params>
       </query-method>
       <declared-sql>
       <where><![CDATA[ DEVICE_ID = ? ]]></where>
       <order><![CDATA[ DEVICE_EXT_ID ASC ]]></order>
       </declared-sql>
       <read-ahead>
       <strategy>none</strategy>
       </read-ahead>
       </query>
      <!-- jboss 3.2 features -->
      <!-- optimistic locking does not express the exclusions needed -->
       </entity>