Error when using Oracle 9i in 1.0.1.CR2: Missing IN or OUT

[heat23]

I setup Persistence using Oracle 9i. It works with 1.0 GA, however with 1.0.1.CR2 I get this error:

12:22:51,253 ERROR [ChannelSupport] Failed to handle message
java.sql.SQLException: Missing IN or OUT parameter at index:: 16
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:145)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(Or
aclePreparedStatement.java:1680)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:3279)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
edStatement.java:3367)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdat
e(WrappedPreparedStatement.java:251)
at org.jboss.messaging.core.plugin.JDBCPersistenceManager.addReference(J
DBCPersistenceManager.java:1645)
at org.jboss.messaging.core.ChannelState.addReference(ChannelState.java:
209)
at org.jboss.messaging.core.ChannelSupport.handle(ChannelSupport.java:12
9)
at org.jboss.jms.server.endpoint.ServerConnectionEndpoint.sendMessage(Se
rverConnectionEndpoint.java:769)
at org.jboss.jms.server.endpoint.ServerSessionEndpoint.send(ServerSessio
nEndpoint.java:376)
at org.jboss.jms.server.endpoint.advised.SessionAdvised.org$jboss$jms$se
rver$endpoint$advised$SessionAdvised$send$aop(SessionAdvised.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.
java:112)
at org.jboss.jms.server.container.SecurityAspect.handleSend(SecurityAspe
ct.java:150)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.aop.advice.PerInstanceAdvice.invoke(PerInstanceAdvice.java:
130)

[timfox]

I think this is because you are using the 1.0 version of the db schema with the 1.0.1 version of the server.

The 1.0.1 version has one less column in the JMS_MESSAGE table (it has 15 columns, 1.0 has 16 columns).

Easiest thing to do is drop the messaging tables and messaging will recreate them on startup.

If that's not possible you need to remove the 16th column, I believe it was call ref_count or reference_count.

[heat23]

I dropped the tables and I still get that error. Here is the DB schema I used.

CREATE_MESSAGE=CREATE TABLE JMS_MESSAGE (MESSAGEID INTEGER, RELIABLE CHAR(1), EXPIRATION INTEGER, TIMESTAMP INTEGER, PRIORITY INTEGER, COREHEADERS BLOB, PAYLOAD BLOB, CHANNELCOUNT INTEGER, TYPE INTEGER, JMSTYPE VARCHAR2(255), CORRELATIONID VARCHAR2(255), CORRELATIONID_BYTES RAW(254), DESTINATION_ID INTEGER, REPLYTO_ID INTEGER, JMSPROPERTIES BLOB, PRIMARY KEY (MESSAGEID))

LOAD_MESSAGES=SELECT MESSAGEID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, COREHEADERS, PAYLOAD, CHANNELCOUNT, TYPE, JMSTYPE, CORRELATIONID, CORRELATIONID_BYTES, DESTINATION_ID, REPLYTO_ID, JMSPROPERTIES FROM JMS_MESSAGE

INSERT_MESSAGE=INSERT INTO JMS_MESSAGE (MESSAGEID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, COREHEADERS, PAYLOAD, CHANNELCOUNT, TYPE, JMSTYPE, CORRELATIONID, CORRELATIONID_BYTES, DESTINATION_ID, REPLYTO_ID, JMSPROPERTIES) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

[timfox]

The error is in the DDL you are using:

INSERT_MESSAGE=INSERT INTO JMS_MESSAGE (MESSAGEID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, COREHEADERS, PAYLOAD, CHANNELCOUNT, TYPE, JMSTYPE, CORRELATIONID, CORRELATIONID_BYTES, DESTINATION_ID, REPLYTO_ID, JMSPROPERTIES) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Note you are naming 15 columns in the statement, but you have 16 question marks.

[timfox]

For Oracle it's best to use the DDL we ship in the distro rather than write your own, since we know it works (it's been tested)

[heat23]

Sorry about the stupid user error. It works now. Thanks!

[timfox]

No problem.

(BTW I of course meant DML not DDL in my previous reply)