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