SQL exception with SQL Server

[guusdk]

As reported in https://discourse.igniterealtime.org/t/openfire-messages-are-not-retrieved-4-6-7/

com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler - An unexpected exception occurred while processing:

<iq id="af533090-167d-4ef4-b049-42f08b46802c" to="119_98_95_99_104_97_116_105_100_95_49_48_48_49_48_48@conference.openfire.mydomain.ai" type="set" from="[email protected]/9n8xlbdgcj">
   <query xmlns="urn:xmpp:mam:2" queryid="on-initial-load">
      <x xmlns="jabber:x:data" type="submit">
         <field type="hidden" var="FORM_TYPE">
            <value>urn:xmpp:mam:2</value>
         </field>
      </x>
      <set xmlns="http://jabber.org/protocol/rsm">
         <max>100</max>
         <before />
      </set>
      <flip-page />
   </query>
</iq>

com.reucon.openfire.plugin.archive.impl.DataRetrievalException: com.reucon.openfire.plugin.archive.impl.DataRetrievalException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'.
    at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.retrieveMessages(IQQueryHandler.java:471) ~[?:?]
    at com.reucon.openfire.plugin.archive.xep0313.IQQueryHandler.lambda$handleIQ$2(IQQueryHandler.java:269) ~[?:?]
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_202]
    at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_202]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_202]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_202]
    at java.lang.Thread.run(Unknown Source) [?:1.8.0_202]

Totzkotz suggests:

yes thats seems to be a bug, because Sql Server does not know the "LIMIT" keyword. it uses "TOP" instead...
some code switches missing here:

1. openfire-monitoring-plugin/src/java/com/reucon/openfire/plugin/archive/impl/PaginatedMucMessageFromOpenfireDatabaseQuery.java

   Line 212 in c9b40dd

   sql += " LIMIT " + maxResults;

2. openfire-monitoring-plugin/src/java/org/jivesoftware/openfire/archive/ArchiveSearcher.java

   Line 344 in c9b40dd

   if (numResults != ArchiveSearch.NULL_INT) {

[mightymop]

2. openfire-monitoring-plugin/src/java/org/jivesoftware/openfire/archive/ArchiveSearcher.java

   Line 344 in c9b40dd

   if (numResults != ArchiveSearch.NULL_INT) {

only this one ;)

1. allready has a special method for sqlserver

maybe adding

... else if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.sqlserver) { query.append(" OFFSET ").append(startIndex).append(" ROWS FETCH NEXT ").append(numResults).append(" ROWS ONLY"); } ...

will do it...