At the beginning, I would like to warmly welcome everyone — this is my first post on this forum.
I have been an Orthanc Server user since 2018 Windows instance. At that time, I downloaded the version for testing and configured it with the ultrasound (USG) machines operating in my clinic. Initially, there were two machines; now there are several more. Since then, the Orthanc server version has of course been updated several times now is “Version”: “1.12.4” Windows. The database has grown to several terabytes. It is still running on SQLite.
Since I am involved in software development, I wrote an application that allows me to retrieve measurement results taken on the ultrasound machines and insert them directly into the examination report issued to the patient.
I tested the code on a test installation of Orthanc Server (“Version”: “1.12.4” Windows instance) — I imported several studies there (SQLite database). Everything worked correctly. However, after moving the code to the production server, the code correctly searches for patient studies from a given day:
curl -X POST http://localhost:8042/tools/find -H "Content-Type: application/json" -d "{\"Level\":\"Study\",\"Query\":{\"StudyDate\":\"20250613\"},\"Expand\":true}"
However, when I try to retrieve the found patient Study:
curl -X POST http://localhost:8042/tools/find -H "Content-Type: application/json" -d "{\"Level\":\"Series\",\"Query\":{\"StudyInstanceUID\":\"1.2.840.113663.1500.1.393048428.1.1.20251024.113241.35\",\"Modality\":\"SR\"},\"Expand\":true}"
I get the error:
SQLite: Cannot run a cached statement.
I do not have this error in the test environment.
I have already prepared the production environment for migration to PostgreSQL; this problem will probably not occur there. Additionally, since importing into the test database (even on SQLite) allowed proper querying of Studies, I assume that after such a migration I will be able to run the code on the production database as well.
However, the whole operation will take some time and financial resources (I need to prepare appropriate disk space), so I would like to ask:
-
Could the fact that SQLite was initially initialized in 2018 have caused a missing index for API searches?
-
Will the planned migration to PostgreSQL allow querying Orthanc (via the API) at the Study level?
-
Is it possible to somehow rebuild the index of the current database on SQLite so that my queries work?
-
And finally, am I correct in interpreting the error “SQLite: Cannot run a cached statement” as an index-related problem, or is there another cause that should be addressed differently? I tried disabling plugins, according to the thread https://discourse.orthanc-server.org/t/help-with-index-migration-from-sqlite-to-postgresql-using-python-orthanc-tools/5930, but that did not help.