powered by Jive Software

Monitoring Plugin not compatible with MS SQL Server

I have Openfire 3.8.2 running on a Windows 2008R2 server, configured to use a MS SQL Server. I have installed the Monitoring plugin and can see that conversations are being recorded to the database. The admin screen also allows me to retrieve and view those conversations.

However from my IM client, I am sending the following XMPP request:


and receiving the following resposne:


From the error log, and from profiling SQL Server I can see that the request being sent to MS SQL Server is:

exec sp_executesql N’SELECT DISTINCT ofConversation.conversationID, ofConversation.room, ofConversation.isExternal, ofConversation.startDate, ofConversation.lastActivity, ofConversation.messageCount, ofConParticipant.joinedDate, ofConParticipant.leftDate, ofConParticipant.bareJID, ofConParticipant.jidResource, ofConParticipant.nickname, ofMessageArchive.fromJID, ofMessageArchive.toJID, ofMessageArchive.sentDate, ofMessageArchive.body FROM ofConversation INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE ofConversation.startDate >= @P0 AND ofConParticipant.bareJID = @P1 AND (ofMessageArchive.toJID = @P2 OR ofMessageArchive.fromJID = @P3 ) ORDER BY ofConversation.conversationID LIMIT 30 OFFSET 0’,N’@P0 bigint,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000)’,1377921970360,N’hey@blah.com’,N’blah@blah.com’,N’blah@blah.com’

However, MS SQL Server does not support the LIMIT and OFFSET commands that are assigned in JdbcPersistenceManager.java:

public Collection findConversations(Date startDate, Date endDate, String ownerJid, String withJid, XmppResultSet xmppResultSet) {

(around line 204).

Are there any alternative solutions ? I can change the source file to be compatible with MS SQL Server but am not too comfortable with all the pre-requisites to rebuild a plugin.

Any ideas?


If you got this far, you might as well bite the bullet, change the source code and rebuild the monitoring plugin.

Rebuilding a plugin is not that complicated.

  1. Make sure yiu have Java JDK and Ant installed
  2. Run ant plugin -Dplugin=monitoring
  3. Copy the monitoring.jar file from the targets folder to your Openfire plugins folder.