Re: ActiveMQ JDBC Persistence with SQL Server

> This looks like the new SQL for the exclusive locks doesn't work for
> SQL Server.  (For more background see....
> http://incubator.apache.org/activemq/jdbc-master-slave.html
>
> We are basically doing a 'SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE"
> which doesn't seem to be allowed for SQL Server despite being SQL 92
> AFAIK..
>
> I wonder if there's some way to refactor the SQL to make it work
> nicely on SQL Server - or we could maybe allow the exclusive lock to
> be disabled.
>
> On 7/30/06, jcarreira <jcarreira@...> wrote:
> >
> > I'm trying to get ActiveMQ working with SQL Server 2000 as the JDBC
> > PersistanceAdapter, and I'm running into this error:
> >
> > 2006-07-29 21:30:32,887 [main] ERROR
> > org.apache.activemq.store.jdbc.DefaultDatabaseLocker - Failed to acquire
> > lock: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE
> > clause allowed only for DECLARE CURSOR.
> > com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause
> > allowed only for DECLARE CURSOR.
> >         at
> > com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown
> > Source)
> >         at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
> >         at
> > com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown
> > Source)
> >         at
> > com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown
> > Source)
> >         at
> > com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown
> > Source)
> >         at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
> >         at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown
> > Source)
> >         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unknown
> > Source)
> >         at
> > com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:911)
> >         at
> > org.apache.activemq.store.jdbc.DefaultDatabaseLocker.start(DefaultDatabaseLocker.java:56)
> >         at
> > org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.start(JDBCPersistenceAdapter.java:172)
> >         at
> > org.apache.activemq.store.journal.JournalPersistenceAdapter.start(JournalPersistenceAdapter.java:216)
> >         at
> > org.apache.activemq.broker.BrokerService.createRegionBroker(BrokerService.java:1006)
> >         at
> > org.apache.activemq.broker.BrokerService.createBroker(BrokerService.java:964)
> >         at
> > org.apache.activemq.broker.BrokerService.getBroker(BrokerService.java:460)
> >         at org.apache.activemq.broker.BrokerService.start(BrokerService.java:363)
> >
> > I've gotten this exact same error with both jTDS 1.2 and Microsoft's JDBC
> > Driver version 1.1.1320.0. Here's my activemq.xml:
> >
> >     <amq:broker brokerName="cluster" useJmx="true">
> >         <!--  Use the following to set the broker memory limit (in bytes)
> > -->
> >         <amq:memoryManager>
> >             <amq:usageManager limit="1048576"/>
> >         </amq:memoryManager>
> >         <amq:persistenceAdapter>
> >             <amq:journaledJDBC journalLogFiles="5"
> > journalLogFileSize="32768"
> >                                dataDirectory="${java.io.tmpdir}/amq-data"
> >                                dataSource="#dataSource"
> >                                >
> >                 <amq:adapter><amq:imageBasedJDBCAdaptor/></amq:adapter>
> >             </amq:journaledJDBC>
> >         </amq:persistenceAdapter>
> >         <amq:transportConnectors>
> >             <amq:transportConnector uri="peer://nirvana-dev"/>
> >         </amq:transportConnectors>
> >     </amq:broker>
> >
> > My DataSource is a c3p0 datasource set up like this:
> >
> > <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
> >         <property name="driverClass" value="${jdbc.driverClassName}"/>
> >         <property name="jdbcUrl" value="${jdbc.url}"/>
> >         <property name="user" value="${jdbc.username}"/>
> >         <property name="password" value="${jdbc.password}"/>
> >         <property name="initialPoolSize"
> > value="${connectionpool.initialPoolSize}"/>
> >         <property name="minPoolSize" value="${connectionpool.minSize}"/>
> >         <property name="maxPoolSize" value="${connectionpool.maxSize}"/>
> >         ...
> > </bean>
> >
> > Anyone have any ideas?
> >
> > Thanks,
> >
> > Jason
> > --
> > View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5560296
> > Sent from the ActiveMQ - User forum at Nabble.com.
> >
> >
>
>
> --
>
> James
> -------
> http://radio.weblogs.com/0112098/
>