Re: ActiveMQ JDBC Persistence with SQL Server

James.Strachan wrote:

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@gmail.com> 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/