ODBC plugins¶

Static linking¶

The procedure to compile these plugins is similar to that for the core of Orthanc. The following commands should work for most UNIX-like distribution (including GNU/Linux):

$ mkdir BuildOdbc
$ cd BuildOdbc
$ cmake ../Odbc -DSTATIC_BUILD=ON -DCMAKE_BUILD_TYPE=Release
$ make

The compilation will produce 2 shared libraries, each containing one plugin for Orthanc:

• OrthancOdbcIndex replaces the default SQLite index of Orthanc by ODBC.
• OrthancOdbcStorage makes Orthanc store the DICOM files it receives into ODBC.

Microsoft Windows and Apple OS X¶

Pre-compiled binaries for Microsoft Windows 32bit are also available. A package for Apple’s Mac OS X is available courtesy of Osimis.

Dynamic linking on Ubuntu 16.04¶

If static linking is not desired, here are build instructions for Ubuntu 16.04 (provided build dependencies for the core of Orthanc have already been installed):

$ sudo apt-get install libodbc1 unixodbc unixodbc-dev
$ mkdir BuildOdbc
$ cd BuildOdbc
$ cmake ../Odbc -DCMAKE_BUILD_TYPE=Release \
                -DALLOW_DOWNLOADS=ON \
                -DUSE_SYSTEM_GOOGLE_TEST=OFF \
                -DUSE_SYSTEM_ORTHANC_SDK=OFF
$ make

Microsoft SQL Server¶

1. Install the ODBC driver for SQL server (version 2017).

2. A non-persistent developer instance of MSSQL 2019 can be started using the Docker image provided by Microsoft as follows:

   $ docker run --name mssql --rm -t -i -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=MyStrOngPa55word!' \
     -e MSSQL_MEMORY_LIMIT_MB=512 -p 1433:1433 mcr.microsoft.com/mssql/server:2019-latest
   

3. Create a database dedicated to Orthanc in MSSQL:

   $ /opt/mssql-tools/bin/sqlcmd -S 192.168.0.17 -U sa -P 'MyStrOngPa55word!' -Q 'CREATE DATABASE orthanctest'
   

4. Create the following sample unixODBC configuration file:

   $ cat ~/.odbc.ini
   [orthanctest]
   Driver = ODBC Driver 17 for SQL Server
   Server = tcp:localhost,1433
   Database = orthanctest
   

   Note that there exist many more configuration options for Microsoft SQL Server. In particular, Encrypt and TrustServerCertificate and Connect Timeout can be interesting in the case of a connection to Microsoft Azure SQL.

5. Start Orthanc using the following configuration file for ODBC:

   {
     "Odbc" : {
       "EnableIndex" : true,
       "EnableStorage" : true,
       "IndexConnectionString" : "DSN=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!",
       "StorageConnectionString" : "DSN=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!"
     }
   }
   

   In the connection strings:

   • DSN corresponds to the name of the entry in ~/.odbc.ini.
   • Uid is the user name in MSSQL (by default, the Docker image uses sa).
   • Pwd is the password that has been provided in the SA_PASSWORD environment variable when starting Docker.
   • For security reasons, the Uid and Pwd parameters cannot be set in ~/.odbc.ini.

Remark: It is actually not necessary to create an entry in ~/.odbc.ini. All the parameters can indeed be provided directly in the connection strings, for instance:

{
  "Odbc" : {
    "EnableIndex" : true,
    "EnableStorage" : true,
    "IndexConnectionString" : "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;Database=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!",
    "StorageConnectionString" : "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;Database=orthanctest;Uid=sa;Pwd=MyStrOngPa55word!"
  }
}

Remark: On Windows systems, we have noticed that the ODBC drivers character encoding seems to depend on a system level configuration. This configuration needs to enforce UTF-8. Therefore, it is advised to configure the system locale as follow:

PostgreSQL¶

1. Install the odbc-postgresql package.

2. Create the following sample unixODBC configuration file:

   $ cat ~/.odbc.ini
   [orthanctest]
   Driver      = PostgreSQL Unicode
   Servername  = localhost
   Database    = orthanctest
   UserName    = postgres
   Password    = postgres
   Port        = 5432
   

3. Start Orthanc using the following configuration file for ODBC:

   {
     "Odbc" : {
       "EnableIndex" : true,
       "EnableStorage" : true,
       "IndexConnectionString" : "DSN=orthanctest",
       "StorageConnectionString" : "DSN=orthanctest"
     }
   }
   

MySQL¶

1. Install the official Connect/ODBC package (it is not packaged for Ubuntu 18.04).

2. Create the following sample unixODBC configuration file:

   $ cat ~/.odbc.ini
   [orthanctest]
   Driver      = MySQL ODBC 8.0 Unicode Driver
   Servername  = localhost
   Database    = orthanctest
   UID         = root
   PWD         = root
   Port        = 3306
   

3. Start Orthanc using the following configuration file for ODBC:

   {
     "Odbc" : {
       "EnableIndex" : true,
       "EnableStorage" : true,
       "IndexConnectionString" : "DSN=orthanctest;charset=utf8",
       "StorageConnectionString" : "DSN=orthanctest;charset=utf8"
     }
   }
   

   The charset=utf8 option is necessary if using MySQL 8.x.

SQLite¶

1. Install the libsqliteodbc package.

2. Create the following sample unixODBC configuration file:

   $ cat ~/.odbc.ini
   [index]
   Driver=SQLite3
   Database=/tmp/test-odbc-index.sqlite
   
   [storage]
   Driver=SQLite3
   Database=/tmp/test-odbc-storage.sqlite
   

   Note that we define two different data sources, one for the index and another for the storage area, because a SQLite database can only be opened by one client at once.

3. Start Orthanc using the following configuration file for ODBC:

   {
     "Odbc" : {
       "EnableIndex" : true,
       "EnableStorage" : true,
       "IndexConnectionString" : "DSN=index",
       "StorageConnectionString" : "DSN=storage",
       "IndexConnectionsCount" : 1
     }
   }
   

   Remark 1: As written just above, one SQLite database should only be opened by one client at a time. This implies that the IndexConnectionsCount must be set to 1, and that the index and storage area must never have connection strings corresponding to the same SQLite database.

   Remark 2: As written above, the ODBC plugin supports the revision mechanism. This contrasts with the built-in SQLite database of Orthanc. So, it might be interesting to use the ODBC index plugin instead of the built-in SQLite database of Orthanc, if you are a developer who wants to test revisions before a large-scale deployment.

Multiple writers or connections¶

Starting with Orthanc 1.9.2, it is possible to use multiple writers or connections in large-scale deployments. Here is the list of configuration that control this behaviour:

• MaximumConnectionRetries governs how many times Orthanc tries to connect to the database, as well as how many times Orthanc replays transactions to deal with collisions between multiple writers.
• IndexConnectionsCount controls the number of connections from the index plugin to the ODBC database. It is set to 1 by default, which corresponds to the old behaviour of Orthanc <= 1.9.1.
• ConnectionRetryInterval is only used when opening one database connection to ODBC.
• These options cannot be used in the case of SQLite databases, that only support one client at once.