powered by Jive Software

Monitoring plugin - wrong SQL for Oracle DB

I updated Openfire to the newest version. Now the IQ Query Handler from the monitoring plugin won’t work anymore.

The logs show a “ORA-00907: missing right parenthesis” error. I tracked it down to this prepared statement:

SELECT * FROM ( SELECT ofMessageArchive.fromJID, ofMessageArchive.toJID, ofMessageArchive.sentDate, ofMessageArchive.stanza, ofMessageArchive.messageID FROM ofMessageArchive AND ofMessageArchive.messageID IS NOT NULL AND ofMessageArchive.sentDate >= ? AND ofMessageArchive.conversationID in ( SELECT DISTINCT ofConParticipant.conversationID FROM ofConParticipant WHERE ofConParticipant.bareJID = ? ) AND ( ofMessageArchive.toJID = ? OR ofMessageArchive.fromJID = ? ) ORDER BY ofMessageArchive.sentDate ) WHERE rownum BETWEEN 1 AND 50

As can be seen here, the first ‘AND’ seems to be wrong and should be a ‘WHERE’. I tested it and replaced the ‘AND’ with a ‘WHERE’ in line 459 of JdbcPersistenceManager.java and it seems to work.

It might be worth a look to investigate the two other occurences in the file where ‘WHERE’ was replaced with ‘AND’.

I am using an Oracle 11.2 database.

Thanks, filed as [OF-1304] SQL syntax error with monitoring plugin IQ Query Handler - IgniteRealtime JIRA and will PR your suggestion shortly