# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1763629561 -3600
# Node ID af1bc07ae7c2710d4b1e14e8c80460bf2d3e8432
# Parent  2bf85a1e2b8567fb317357a69e0d716e9be4b605# Parent  3cf91621c1da46eb4654541def3af91d6bcb1f3a
merge

diff -r 2bf85a1e2b85 -r af1bc07ae7c2 OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Thu Nov 20 10:05:19 2025 +0100
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Thu Nov 20 10:06:01 2025 +0100
@@ -43,6 +43,16 @@
 #include <boost/lexical_cast.hpp>
 
 
+/**
+ * WARNING: Do not use the RETURNING clause in the SQLite statements
+ * of this file. Indeed, "the RETURNING syntax has been supported by
+ * SQLite since version 3.35.0 (2021-03-12)". However, versions of
+ * SQLite < 3.35.0 are still widely used (e.g. Ubuntu 22.04 LTS, which
+ * is supported until 2030, comes with SQLite 3.31.1).
+ * https://sqlite.org/lang_returning.html
+ **/
+
+
 namespace Orthanc
 {  
   static std::string JoinRequestedMetadata(const FindRequest::ChildrenSpecification& childrenSpec)
@@ -2317,28 +2327,37 @@
       }
 
       // "reservedUntil <= ?" indicates that the reservation has expired
-      const std::string sql = ("WITH RowToDelete AS (SELECT id FROM Queues WHERE queueId=? AND "
-                               "(reservedUntil IS NULL OR reservedUntil <= ?) ORDER BY id " + order + " LIMIT 1) "
-                               "DELETE FROM Queues WHERE id IN (SELECT id FROM RowToDelete) "
-                               "RETURNING value");
-
-      SQLite::Statement s(db_, SQLITE_FROM_HERE_DYNAMIC(sql), sql);
-
-      s.BindString(0, queueId);
-      s.BindInt64(1, GetSecondsSinceEpoch() /* now */);
-
-      if (!s.Step())
+      const std::string sql = ("SELECT id, value FROM Queues WHERE queueId=? AND "
+                               "(reservedUntil IS NULL OR reservedUntil <= ?) ORDER BY id " + order + " LIMIT 1");
+
+      SQLite::Statement s1(db_, SQLITE_FROM_HERE_DYNAMIC(sql), sql);
+
+      s1.BindString(0, queueId);
+      s1.BindInt64(1, GetSecondsSinceEpoch() /* now */);
+
+      if (!s1.Step())
       {
         // No value found
         return false;
       }
       else
       {
-        if (!s.ColumnBlobAsString(0, &value))
+        if (!s1.ColumnBlobAsString(1, &value))
         {
           throw OrthancException(ErrorCode_NotEnoughMemory);
         }
 
+        SQLite::Statement s2(db_, SQLITE_FROM_HERE, "DELETE FROM Queues WHERE queueId=? AND id=?");
+        s2.BindString(0, queueId);
+        s2.BindInt64(1, s1.ColumnInt64(0));
+        s2.Run();
+
+        if (db_.GetLastChangeCount() != 1)
+        {
+          // This occurs if the acknowledgment occurs after the reservation has expired
+          throw OrthancException(ErrorCode_UnknownResource);
+        }
+
         return true;
       }    
     }
@@ -2427,6 +2446,10 @@
         // This occurs if the acknowledgment occurs after the reservation has expired
         throw OrthancException(ErrorCode_UnknownResource);
       }
+      else if (db_.GetLastChangeCount() > 1)
+      {
+        throw OrthancException(ErrorCode_InternalError);
+      }
     }
   };