Error: Missing IN or OUT param when using JBoss Declared SQL
jason1 Dec 12, 2005 2:09 PMHi,
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>