powered by Jive Software

Openfire Chat History Database Syntax Error

When i am trying to get the list of archived messages using xmpp

the excpected result should be

<iq type="result" id="pk1" to="myusername@openfire/10f87b53"> <list xmlns="urn:xmpp:archive"> <chat with="someone@openfire" start="2012-07-04T13:16:12.291Z"/> <chat with="someone@openfire" start="2012-07-05T08:25:31.555Z"/> <chat with="someone@openfire" start="2012-07-05T12:38:24.098Z"/> <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)"> <first index="0">15</first> <last>25</last> <count>3</count> </set> </list> </iq>

But the actual result which i received is

<iq type="result" id="pk1" to="myusername@openfire/10f87b53"> <list xmlns="urn:xmpp:archive"> <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)"> <count>2</count> </set> </list> </iq>

when i look through the error logs.i found that its the Database Query which is resulting the error

2014.02.24 07:10:12 org.jivesoftware.util.Log - Error selecting conversations

java.sql.SQLException: Incorrect syntax near ‘LIMIT’.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)

at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatem ent.java:777)

at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at $java.lang.AutoCloseable$$EnhancerByProxool$$ad4b85a7.executeQuery()

at org.jivesoftware.database.ProfiledConnection$TimedPreparedStatement.executeQuer y(ProfiledConnection.java:780)

at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.findConversation s(JdbcPersistenceManager.java:214)

at com.reucon.openfire.plugin.archive.xep0136.IQListHandler.list(IQListHandler.jav a:49)

at com.reucon.openfire.plugin.archive.xep0136.IQListHandler.handleIQ(IQListHandler .java:34)

at com.reucon.openfire.plugin.archive.xep0136.Xep0136Support$1.handleIQ(Xep0136Sup port.java:48)

at org.jivesoftware.openfire.handler.IQHandler.process(IQHandler.java:65)

at org.jivesoftware.openfire.IQRouter.handle(IQRouter.java:374)

at org.jivesoftware.openfire.IQRouter.route(IQRouter.java:121)

at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:76)

at org.jivesoftware.openfire.net.StanzaHandler.processIQ(StanzaHandler.java:337)

at org.jivesoftware.openfire.net.ClientStanzaHandler.processIQ(ClientStanzaHandler .java:93)

at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:302)

at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:194)

at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:181)

at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived (AbstractIoFilterChain.java:570)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.common.IoFilterAdapter.messageReceived(IoFilterAdapter.java:80)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimplePr otocolDecoderOutput.java:58)

at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:185)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java :239)

at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(Execut orFilter.java:283)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)

at java.lang.Thread.run(Unknown Source)

i would be greatful if any one provide me the soultion to this.

Note : I am using Sql Server 2008 with openfire 3.9.1

Any one who is looking for solution move to this post: