SQL syntax error with monitoring plugin IQ Query Handler
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.