Invalid column name 'time' when using Sybase XA

[Fazle Khan <fa...@gmail.com>]

When trying to use Sybase XA drivers as persistence for and ActiveMQ 5.1.0
Sybase throws the error 'Invalid column name 'time'.  I assume I have
configured something wrong - anyone have suggestions where my error could
be?

Here are the relevant Spring configurations

    <!--  lets create an embedded ActiveMQ Broker-->
    <amq:broker useJmx="false" persistent="true" id="broker">
        <amq:persistenceAdapter>
            <amq:journaledJDBC dataDirectory="${activemq.base}/data"
dataSource="#xaDataSource">
                <amq:adapter>
                    <bean
class="org.apache.activemq.store.jdbc.adapter.SybaseJDBCAdapter" />                    
                </amq:adapter>
            </amq:journaledJDBC>
        </amq:persistenceAdapter>
        <amq:transportConnectors>
            <amq:transportConnector
uri="vm://localhost:61616?marshal=false"/>
        </amq:transportConnectors>
    </amq:broker>

    <bean id="xaDataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName"
value="com.sybase.jdbc3.jdbc.SybXADataSource"/>
        <property name="url"
value="jdbc:sybase:Tds:localhost:5000/cithaeron"/>
        <property name="username" value="developer"/>
        <property name="password" value="developer"/>
        <property name="defaultTransactionIsolation" value="2"/>
    </bean>


Here is the log

Connected to server
 INFO [http-8080-1] (LocalSessionFactoryBean.java:689) - Building new
Hibernate SessionFactory
 WARN [http-8080-1] (DefaultJDBCAdapter.java:100) - Could not create JDBC
tables; they could already exist. Failure was: INSERT INTO ACTIVEMQ_LOCK(ID)
VALUES (1) Message: The column TIME in table ACTIVEMQ_LOCK does not allow
null values.
 SQLState: 23000 Vendor code: 233
ERROR:  ''
ERROR [ActiveMQ Cleanup Timer] (DefaultDatabaseLocker.java:109) - Failed to
update database lock: com.sybase.jdbc3.jdbc.SybSQLException: Invalid column
name 'time'.

com.sybase.jdbc3.jdbc.SybSQLException: Invalid column name 'time'.

	at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
	at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
	at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybStatement.updateLoop(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybStatement.executeUpdate(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeUpdate(Unknown Source)
	at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
	at
org.apache.activemq.store.jdbc.DefaultDatabaseLocker.keepAlive(DefaultDatabaseLocker.java:104)
	at
org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.databaseLockKeepAlive(JDBCPersistenceAdapter.java:458)
	at
org.apache.activemq.store.jdbc.JDBCPersistenceAdapter$3.run(JDBCPersistenceAdapter.java:260)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
	at
java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:317)
	at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:150)
	at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:98)
	at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:181)
	at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:205)
	at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
	at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
	at java.lang.Thread.run(Thread.java:619)
 WARN [ActiveMQ Cleanup Timer] (JDBCPersistenceAdapter.java:476) - Failed to
stop broker



-- 
View this message in context: http://www.nabble.com/Invalid-column-name-%27time%27-when-using-Sybase-XA-tp17872586p17872586.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: Invalid column name 'time' when using Sybase XA

[Fazle Khan <fa...@gmail.com>]

So debugging through the app and running the sql statement through isql the
problem turns out to be our database default columns to Not Null while the
activemq statement assume columns default to Allow Null.  Also, it seems
there is a mismatch is case for the TIME/time column for when the column is
created (TIME) and when it is used (time).  Defining my own ddl statement to
create the tables fixed the problem 

CREATE TABLE ACTIVEMQ_MSGS(ID INTEGER NOT NULL, CONTAINER VARCHAR(250),
MSGID_PROD VARCHAR(250), MSGID_SEQ INTEGER, EXPIRATION DECIMAL, MSG IMAGE,
PRIMARY KEY ( ID ) )
go

CREATE INDEX ACTIVEMQ_MSGS_MIDX ON ACTIVEMQ_MSGS (MSGID_PROD,MSGID_SEQ)
go

CREATE INDEX ACTIVEMQ_MSGS_CIDX ON ACTIVEMQ_MSGS (CONTAINER)
go

CREATE INDEX ACTIVEMQ_MSGS_EIDX ON ACTIVEMQ_MSGS (EXPIRATION)
go

CREATE TABLE ACTIVEMQ_ACKS(CONTAINER VARCHAR(250) NOT NULL, SUB_DEST
VARCHAR(250), CLIENT_ID VARCHAR(250) NOT NULL, SUB_NAME VARCHAR(250) NOT
NULL, SELECTOR VARCHAR(250), LAST_ACKED_ID INTEGER, PRIMARY KEY ( CONTAINER,
CLIENT_ID, SUB_NAME))
go

--the default activemq create statements assume columns default to allow
null, our default to not null
--the 'TIME' column seems to be unexpected case sensitive so 'time' is used
instead
CREATE TABLE ACTIVEMQ_LOCK( ID DECIMAL NOT NULL, time DECIMAL NULL,
BROKER_NAME VARCHAR(250) NULL, PRIMARY KEY (ID) )
go

INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1)
go

-- 
View this message in context: http://www.nabble.com/Invalid-column-name-%27time%27-when-using-Sybase-XA-tp17872586p17874705.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: Invalid column name 'time' when using Sybase XA

[Fazle Khan <fa...@gmail.com>]

btw I'm using Sybase ASE 15.02 
-- 
View this message in context: http://www.nabble.com/Invalid-column-name-%27time%27-when-using-Sybase-XA-tp17872586p17872704.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.