Fix MS SQL limit (fix #4958)

5 files changed, 28 insertions, 26 deletions

diff --git a/src/main/java/org/traccar/database/CommandsManager.java b/src/main/java/org/traccar/database/CommandsManager.java
index 764ea637b..df399cd7a 100644
--- a/src/main/java/org/traccar/database/CommandsManager.java
+++ b/src/main/java/org/traccar/database/CommandsManager.java
@@ -31,7 +31,6 @@ import org.traccar.storage.Storage;
 import org.traccar.storage.StorageException;
 import org.traccar.storage.query.Columns;
 import org.traccar.storage.query.Condition;
-import org.traccar.storage.query.Limit;
 import org.traccar.storage.query.Order;
 import org.traccar.storage.query.Request;
 
@@ -102,8 +101,7 @@ public class CommandsManager implements BroadcastInterface {
             var commands = storage.getObjects(QueuedCommand.class, new Request(
                     new Columns.All(),
                     new Condition.Equals("deviceId", deviceId),
-                    new Order(false, "id"),
-                    new Limit(count)));
+                    new Order("id", false, count)));
             for (var command : commands) {
                 storage.removeObject(QueuedCommand.class, new Request(
                         new Condition.Equals("id", command.getId())));
diff --git a/src/main/java/org/traccar/handler/FilterHandler.java b/src/main/java/org/traccar/handler/FilterHandler.java
index 3722f2a22..994276bb6 100644
--- a/src/main/java/org/traccar/handler/FilterHandler.java
+++ b/src/main/java/org/traccar/handler/FilterHandler.java
@@ -30,7 +30,6 @@ import org.traccar.storage.Storage;
 import org.traccar.storage.StorageException;
 import org.traccar.storage.query.Columns;
 import org.traccar.storage.query.Condition;
-import org.traccar.storage.query.Limit;
 import org.traccar.storage.query.Order;
 import org.traccar.storage.query.Request;
 
@@ -90,8 +89,7 @@ public class FilterHandler extends BaseDataHandler {
                 new Condition.And(
                         new Condition.Equals("deviceId", deviceId),
                         new Condition.Compare("fixTime", "<=", "time", date)),
-                new Order(true, "fixTime"),
-                new Limit(1)));
+                new Order("fixTime", true, 1)));
     }
 
     private boolean filterInvalid(Position position) {
diff --git a/src/main/java/org/traccar/storage/DatabaseStorage.java b/src/main/java/org/traccar/storage/DatabaseStorage.java
index 884c8fca8..a049a641c 100644
--- a/src/main/java/org/traccar/storage/DatabaseStorage.java
+++ b/src/main/java/org/traccar/storage/DatabaseStorage.java
@@ -24,7 +24,6 @@ import org.traccar.model.GroupedModel;
 import org.traccar.model.Permission;
 import org.traccar.storage.query.Columns;
 import org.traccar.storage.query.Condition;
-import org.traccar.storage.query.Limit;
 import org.traccar.storage.query.Order;
 import org.traccar.storage.query.Request;
 
@@ -43,12 +42,19 @@ public class DatabaseStorage extends Storage {
     private final Config config;
     private final DataSource dataSource;
     private final ObjectMapper objectMapper;
+    private final String databaseType;
 
     @Inject
     public DatabaseStorage(Config config, DataSource dataSource, ObjectMapper objectMapper) {
         this.config = config;
         this.dataSource = dataSource;
         this.objectMapper = objectMapper;
+
+        try {
+            databaseType = dataSource.getConnection().getMetaData().getDatabaseProductName();
+        } catch (SQLException e) {
+            throw new RuntimeException(e);
+        }
     }
 
     @Override
@@ -62,7 +68,6 @@ public class DatabaseStorage extends Storage {
         query.append(" FROM ").append(getStorageName(clazz));
         query.append(formatCondition(request.getCondition()));
         query.append(formatOrder(request.getOrder()));
-        query.append(formatLimit(request.getLimit()));
         try {
             QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString());
             for (Map.Entry<String, Object> variable : getConditionVariables(request.getCondition()).entrySet()) {
@@ -302,15 +307,16 @@ public class DatabaseStorage extends Storage {
             if (order.getDescending()) {
                 result.append(" DESC");
             }
-        }
-        return result.toString();
-    }
-
-    private String formatLimit(Limit limit) {
-        StringBuilder result = new StringBuilder();
-        if (limit != null) {
-            result.append(" LIMIT ");
-            result.append(limit.getValue());
+            if (order.getLimit() > 0) {
+                if (databaseType.equals("Microsoft SQL Server")) {
+                    result.append(" OFFSET 0 ROWS FETCH FIRST ");
+                    result.append(order.getLimit());
+                    result.append(" ROWS ONLY");
+                } else {
+                    result.append(" LIMIT ");
+                    result.append(order.getLimit());
+                }
+            }
         }
         return result.toString();
     }
diff --git a/src/main/java/org/traccar/storage/query/Order.java b/src/main/java/org/traccar/storage/query/Order.java
index d12acc83b..b41f145e9 100644
--- a/src/main/java/org/traccar/storage/query/Order.java
+++ b/src/main/java/org/traccar/storage/query/Order.java
@@ -19,14 +19,16 @@ public class Order {
 
     private final String column;
     private final boolean descending;
+    private final int limit;
 
     public Order(String column) {
-        this(false, column);
+        this(column, false, 0);
     }
 
-    public Order(boolean descending, String column) {
+    public Order(String column, boolean descending, int limit) {
         this.column = column;
-        this.descending = descending;
+        this.descending = false;
+        this.limit = limit;
     }
 
     public String getColumn() {
@@ -37,4 +39,8 @@ public class Order {
         return descending;
     }
 
+    public int getLimit() {
+        return limit;
+    }
+
 }
diff --git a/src/main/java/org/traccar/storage/query/Request.java b/src/main/java/org/traccar/storage/query/Request.java
index 41aa37bf1..6e9cecc63 100644
--- a/src/main/java/org/traccar/storage/query/Request.java
+++ b/src/main/java/org/traccar/storage/query/Request.java
@@ -20,7 +20,6 @@ public class Request {
     private final Columns columns;
     private final Condition condition;
     private final Order order;
-    private final Limit limit;
 
     public Request(Columns columns) {
         this(columns, null, null);
@@ -42,7 +41,6 @@ public class Request {
         this.columns = columns;
         this.condition = condition;
         this.order = order;
-        this.limit = limit;
     }
 
     public Columns getColumns() {
@@ -57,8 +55,4 @@ public class Request {
         return order;
     }
 
-    public Limit getLimit() {
-        return limit;
-    }
-
 }