You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by jcarreira <jc...@gmail.com> on 2006/07/30 05:00:08 UTC
ActiveMQ JDBC Persistence with SQL Server
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.
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by James Strachan <ja...@gmail.com>.
On 7/30/06, jcarreira <jc...@gmail.com> wrote:
>
>
> James.Strachan wrote:
> >
> > Am not too sure what your XML looks like - but the peer: transport is
> > typically only used on the client side - there is no server side
> > transport for peer
> >
>
> Well, as per my original post
> (http://www.nabble.com/Getting-started-with-ActiveMQ-tf2004874.html) I'm
> trying to set it up so that I have independent cluster nodes which can be
> transparently dropped into the cluster and automatically discover each
> other. From the docs and what Hiram said, peer seemed like a good fit. If
> it's creating embedded brokers in each app, why is it client-side, not
> server side?
>
> In any case, I'm setting it up using multicast discovery and a tcp port like
> it shows in the network of brokers example now.
Some transports are purely client side (like multicast and peer) -
some transports have a server side to them (like tcp and http).
Just don't use the peer:// transport connector in the activemq.xml
--
James
-------
http://radio.weblogs.com/0112098/
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by jcarreira <jc...@gmail.com>.
James.Strachan wrote:
>
> Am not too sure what your XML looks like - but the peer: transport is
> typically only used on the client side - there is no server side
> transport for peer
>
Well, as per my original post
(http://www.nabble.com/Getting-started-with-ActiveMQ-tf2004874.html) I'm
trying to set it up so that I have independent cluster nodes which can be
transparently dropped into the cluster and automatically discover each
other. From the docs and what Hiram said, peer seemed like a good fit. If
it's creating embedded brokers in each app, why is it client-side, not
server side?
In any case, I'm setting it up using multicast discovery and a tcp port like
it shows in the network of brokers example now.
Jason
--
View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5564157
Sent from the ActiveMQ - User forum at Nabble.com.
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by James Strachan <ja...@gmail.com>.
Am not too sure what your XML looks like - but the peer: transport is
typically only used on the client side - there is no server side
transport for peer
On 7/30/06, jcarreira <jc...@gmail.com> wrote:
>
> Ok, replaced the journaledJDBC with a memoryPersistenceAdapter and I can get
> to the next error:
>
> 2006-07-29 22:48:20,106 [main] ERROR
> org.apache.activemq.broker.BrokerService - Failed to start ActiveMQ JMS
> Message Broker. Reason: java.io.IOException: This protocol does not support
> being bound.
> java.io.IOException: This protocol does not support being bound.
> at
> org.apache.activemq.transport.peer.PeerTransportFactory.doBind(PeerTransportFactory.java:113)
> at
> org.apache.activemq.transport.TransportFactory.bind(TransportFactory.java:108)
> at
> org.apache.activemq.broker.TransportConnector.createTransportServer(TransportConnector.java:245)
>
> I guess I'm not clear on exactly how one uses the peer transport? See my
> config above for how I was trying to use it...
>
> Is it basically the same as setting up the Network of Brokers with multicast
> discovery as shown here:
>
> http://incubator.apache.org/activemq/networks-of-brokers.html
>
> ???
> --
> View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5560343
> Sent from the ActiveMQ - User forum at Nabble.com.
>
>
--
James
-------
http://radio.weblogs.com/0112098/
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by jcarreira <jc...@gmail.com>.
Ok, replaced the journaledJDBC with a memoryPersistenceAdapter and I can get
to the next error:
2006-07-29 22:48:20,106 [main] ERROR
org.apache.activemq.broker.BrokerService - Failed to start ActiveMQ JMS
Message Broker. Reason: java.io.IOException: This protocol does not support
being bound.
java.io.IOException: This protocol does not support being bound.
at
org.apache.activemq.transport.peer.PeerTransportFactory.doBind(PeerTransportFactory.java:113)
at
org.apache.activemq.transport.TransportFactory.bind(TransportFactory.java:108)
at
org.apache.activemq.broker.TransportConnector.createTransportServer(TransportConnector.java:245)
I guess I'm not clear on exactly how one uses the peer transport? See my
config above for how I was trying to use it...
Is it basically the same as setting up the Network of Brokers with multicast
discovery as shown here:
http://incubator.apache.org/activemq/networks-of-brokers.html
???
--
View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5560343
Sent from the ActiveMQ - User forum at Nabble.com.
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by James Strachan <ja...@gmail.com>.
From: jcarreira <jc...@gmail.com>
Date: Jul 31, 2006 6:17 PM
Subject: Re: ActiveMQ JDBC Persistence with SQL Server
To: activemq-users@geronimo.apache.org
James.Strachan wrote:
>
> If you set the useDatabaseLock="false" attribute on the
> <jdbcPersistenceAdapter/> element it should disable the use of the
> JDBC exclusive lock and so avoid your problem with SQL Server.
>
> I've just committed a similar attribute on <journaledJDBC/> as well.
>
How long does it take for snapshots to come out?
Usually 24 hours. Note that <jdbcPersistenceAdapter/> has had the
useDatabaseLock="false" for some time.
--
James
-------
http://radio.weblogs.com/0112098/
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by jcarreira <jc...@gmail.com>.
James.Strachan wrote:
>
> If you set the useDatabaseLock="false" attribute on the
> <jdbcPersistenceAdapter/> element it should disable the use of the
> JDBC exclusive lock and so avoid your problem with SQL Server.
>
> I've just committed a similar attribute on <journaledJDBC/> as well.
>
How long does it take for snapshots to come out?
--
View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5580099
Sent from the ActiveMQ - User forum at Nabble.com.
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by James Strachan <ja...@gmail.com>.
If you set the useDatabaseLock="false" attribute on the
<jdbcPersistenceAdapter/> element it should disable the use of the
JDBC exclusive lock and so avoid your problem with SQL Server.
I've just committed a similar attribute on <journaledJDBC/> as well.
On 7/30/06, James Strachan <ja...@gmail.com> 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 <jc...@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/
>
--
James
-------
http://radio.weblogs.com/0112098/
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by Hiram Chirino <hi...@hiramchirino.com>.
Instead of "Switch Databases" how about, workaround: "Use the default
persistence configuration" ;-) Sounds better.
Anybody out there know how to get sql server to do select for update stuff?
On 7/31/06, jcarreira <jc...@gmail.com> wrote:
>
>
>
> James.Strachan wrote:
> >
> > The workaround is to use Derby or Oracle and not SQLServer.
> >
>
> "Switch Databases" is not a workaround since it's plainly not a viable
> alternative in most cases.
> --
> View this message in context:
> http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5578221
> Sent from the ActiveMQ - User forum at Nabble.com.
>
>
--
Regards,
Hiram
Blog: http://hiramchirino.com
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by jcarreira <jc...@gmail.com>.
James.Strachan wrote:
>
> The workaround is to use Derby or Oracle and not SQLServer.
>
"Switch Databases" is not a workaround since it's plainly not a viable
alternative in most cases.
--
View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5578221
Sent from the ActiveMQ - User forum at Nabble.com.
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by James Strachan <ja...@gmail.com>.
The workaround is to use Derby or Oracle and not SQLServer.
On 7/31/06, jcarreira <jc...@gmail.com> wrote:
>
> So is there a workaround or a fix coming?
>
>
> 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 <jc...@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/
> >
> >
> --
> View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5577924
> Sent from the ActiveMQ - User forum at Nabble.com.
>
>
--
James
-------
http://radio.weblogs.com/0112098/
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by jcarreira <jc...@gmail.com>.
So is there a workaround or a fix coming?
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 <jc...@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/
>
>
--
View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5577924
Sent from the ActiveMQ - User forum at Nabble.com.
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by jcarreira <jc...@gmail.com>.
This isn't some sort of plan to make me look at SwiftMQ is it? ;-)
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.
>
> --
>
> James
> -------
> http://radio.weblogs.com/0112098/
>
>
--
View this message in context: http://www.nabble.com/ActiveMQ-JDBC-Persistence-with-SQL-Server-tf2022248.html#a5564170
Sent from the ActiveMQ - User forum at Nabble.com.
Re: ActiveMQ JDBC Persistence with SQL Server
Posted by James Strachan <ja...@gmail.com>.
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 <jc...@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/