[Bugzilla user: sprog@mail.ru]
[Bugzilla date: 2024-03-05T05:52:13+00:00]

Any lookup by Orthanc Explorer or Orthanc Explorer 2 fails with error.

E0305 08:27:05.266052           HTTP-0 PluginsManager.cpp:153] Error with the database engine: Cannot execute ODBC statement:
42000 : 1/102 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'LIMIT'.
42000 : 2/8180 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

Configuration file:

    "Odbc" : {
        "EnableIndex" : true,
        "EnableStorage" : false,
        "IndexConnectionString" : "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;Database=Orthanc;Uid=...;Pwd=...",
        "StorageConnectionString" : "DSN=storage",
	"IndexConnectionsCount" : 1
    }

All studies lists as expected. Error flow up only when query contain count limitation.

[Bugzilla user: sprog@mail.ru]
[Bugzilla date: 2024-03-05T09:06:33+00:00]

workaround is set the LimitFindResults to 0 in orthanc.json

[Bugzilla user: sprog@mail.ru]
[Bugzilla date: 2024-03-05T12:59:53+00:00]

I think, it becouse orthanc-databases\Resources\Orthanc\Databases\ISqlLookupFormatter.cpp
contain
    if (limit != 0)
    {
      sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
    }
in Apply and ApplySingleLevel

[Bugzilla user: am@orthanc.team]
[Bugzilla date: 2024-03-08T10:06:06+00:00]

Hi,

I confirm I can reproduce it with the below setup based on https://github.com/orthanc-server/orthanc-setup-samples/tree/master/docker/mssql.

Note however that ODBC is usually considered quite a low priority for us.  You may get in touch with https://orthanc.team/ if you need this fixed with higher priority.

Alain.


Note for myself: we should add an integration tests with an explicit Limit argument in the tools/find.



version: "2"
services:
  orthanc-new-mssql:
    image: orthancteam/orthanc:24.3.3-full
    depends_on:
      db: 
        condition: service_healthy
    ports: ["8042:8042"]
    volumes: ["storage:/var/lib/orthanc/db"]
    environment:
      ORTHANC__ODBC__INDEX_CONNECTION_STRING: "Driver={ODBC Driver 18 for SQL Server};Server=tcp:db,1433;Database=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;"
      ORTHANC__ODBC__ENABLE_INDEX: "true"
      ORTHANC__ODBC__ENABLE_STORAGE: "false"
      ORTHANC__AUTHENTICATION_ENABLED: "false"
      VERBOSE_STARTUP: "true"
      VERBOSE_ENABLED: "true"
      ORTHANC__SAVE_JOBS: "false"
      ORTHANC__HTTP_THREADS_COUNT: "5"
      ORTHANC__ORTHANC_EXPLORER_2__UI_OPTIONS__STUDY_LIST_CONTENT_IF_NO_SEARCH: "empty"
      ORTHANC__ORTHANC_EXPLORER_2__UI_OPTIONS__STUDY_LIST_SEARCH_MODE: "search-button"
      ORTHANC__LIMIT_FIND_RESULTS: "100"

    restart: unless-stopped

  db:
    build: index
    ports: ["1434:1433"]
    volumes: ["index:/var/opt/mssql"]
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$$SA_PASSWORD" -Q "SELECT 1" || exit 1
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 10s
    environment:
      ACCEPT_EULA: "Y"
      SA_PASSWORD: "MyStrOngPa55word!"
    restart: unless-stopped


volumes:
  storage:
  index:

[Bugzilla user: am@orthanc.team]
[Bugzilla date: 2024-09-06T14:59:10+00:00]

This should now be fixed by this commit in the find-refactoring branch: https://orthanc.uclouvain.be/hg/orthanc-databases/rev/4ecf50a4521c