The ODBC data and image sources allow for collection data to be based on a query from an external data source.

Below are some guides for installing and configuring ODBC drivers for different databases.

SQL Server

In order to use the ODBC data source plugin with SQL Server the correct ODBC driver needs to be installed.

Windows

For Windows installations it is recomended that the Microsoft ODBC Driver 13.1 for SQL Server be installed. The driver can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=53339. Make sure to download the x64 (amd64) version of the driver.

Once the driver has been installed it is then possible to connect to a SQL Server instance.

The connection string for Windows Authentication is

    Driver={ODBC Driver 13 for SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes

The connection string for SQL Server Authentication is

    Driver={ODBC Driver 13 for SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Linux

For Linux see the official documentation https://docs.microsoft.com/en-us/sql/connect/odbc/linux/installing-the-microsoft-odbc-driver-for-sql-server-on-linux.

PostgreSQL

Windows

Download and install the latest x64 PostgreSQL driver from https://www.postgresql.org/ftp/odbc/versions/msi/.

Once installed open the ODBC Data Source Administrator (64-bit) by running odbcad32

Create a new System DSN:

  1. Click Add...
  2. Select the PostgreSQL driver (either ANSI or Unicode depending on your database)
  3. Fill in the Setup by entering in the Database name, server address, port (default is 5432), User Name and Password.
  4. Click Test to confirm the details are correct
  5. Click Save to create the DSN Create Windows DSN

Connection string

When creating a collection use the DSN name in the connection string. For example if the DSN is called 'Zegami' the connection string will be:

    DSN=Zegami

Windows DSN Connection String

Ubuntu 16.04

Install PostgreSQL (if not installed already):

    sudo apt-get update
    sudo apt-get install postgresql postgresql-contrib

Install ODBC driver for PostgreSQL:

    sudo apt-get install odbc-postgresql

You can test if the driver is installed properly with following command:

    odbcinst –q –d

If it is istalled correctly you should see:

    [PostgreSQL ANSI]
    [PostgreSQL Unicode]

Configure the path to psqlodbca.so and libodbcpsqlS.so files for Driver and Setup in odbcinst.ini located in /etc directory.

The file should look like this:

    [PostgreSQL ANSI]
    Description=PostgreSQL ODBC driver (ANSI version)
    Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
    Setup=/usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so
    Debug=0
    CommLog=1
    UsageCount=1

    [PostgreSQL Unicode]
    Description=PostgreSQL ODBC driver (Unicode version)
    Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
    Setup=/usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so
    Debug=0
    CommLog=1
    UsageCount=1

Create 'zegami' role in PostgreSQL and grant with appropriate permissions.

To enter PostgreSQL terminal:

sudo -i -u postgres
psql

Create 'zegami' role:

CREATE ROLE zegami WITH PASSWORD‘password’;

Change login attribute of the zegami user to true with:

ALTER USER zegami LOGIN;

Grant SELECT privileges zegami user in order to be able to execute select queries against the desired table:

GRANT SELECT ON [table] TO zegami;

To create a collection with ODBC data source use following SQL query and Connection String: SQL query (zegami user must have select privileges):

SELECT * FROM [data source table];

Connection string

Driver={PostgreSQL ANSI};Server=127.0.0.1;Port=5432;Database=myDataBase;
Uid=zegami;Pwd=myPassword;

OR

Driver={PostgreSQL UNICODE};Server=127.0.0.1;Port=5432;Database=myDataBase;
Uid=zegami;Pwd=myPassword;

To create a collection with ODBC image source use following SQL query and Connection String: SQL query (zegami user must have select privileges):

SELECT [Filename column] as filename, [Blobdata] column as blobdata
FROM [image source table]
WHERE [id column in image source table] = {{ [id column in data source table] }};

Connection string

Driver={PostgreSQL ANSI};Server=127.0.0.1;Port=5432;Database=myDataBase;
Uid=zegami;Pwd=myPassword;

OR

Driver={PostgreSQL UNICODE};Server=127.0.0.1;Port=5432;Database=myDataBase;
Uid=zegami;Pwd=myPassword;

MySQL

Ubuntu 16.04

Install MySQL (if not installed already):

sudo apt-get update
sudo apt-get install mysql-server

Download ODBC driver for MySQL from https://dev.mysql.com/downloads/connector/odbc/

Extract the driver with:

gunzip mysql-connector-odbc-5.3.7-linux-ubuntu16.04-x86-64bit.tar.gz
tar xvf mysql-connector-odbc-5.3.7-linux-ubuntu16.04-x86-64bit.tar

Create 'odbc' folder in /usr/lib/x86_64-linux-gnu/

sudo mkdir /usr/lib/x86_64-linux-gnu/odbc

Copy the files from driver’s lib directory to /usr/lib/x86_64-linux-gnu/odbc

sudo cp /home/username/Downloads/mysql-connector-odbc-5.3.7-linux-ubuntu16.04-x86-64bit/lib/libmyodbc5* /usr/lib/x86_64-linux-gnu/odbc/

Create odbc.ini and odbcinst.ini files in /etc/

sudo touch /etc/odbc.ini
sudo touch /etc/odbcinst.ini

Edit odbc.ini and odbcinst.ini

odbc.ini

[MySQL_ODBC_dsn]                         
Description = description of your DSN
Driver      = MySQL_ODBC
Server      = localhost                    
Port        = 3306                         
Socket      = /var/run/mysqld/mysqld.sock  
Database    = zegamidb                     
Option      = 3
ReadOnly    = No

odbcinst.ini

[MySQL_ODBC]
Description     = ODBC for MySQL
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc5a.so
Setup           = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc5S.so
UsageCount      = 1

To create a collection with ODBC data source use following SQL query and Connection String:

SQL query

SELECT * FROM [data source table];

Connection string

Driver={MySQL_ODBC};Server=127.0.0.1;Database=myDataBase;Uid=root;Pwd=myPassword;

To create a collection with ODBC image source use following SQL query and Connection String:

SQL query (zegami user must have select privileges)

SELECT [Filename column] as filename, [Blobdata] column as blobdata
FROM [image source table]
WHERE [id column in image source table] = {{ [id column in data source table] }};

Connection string:

Driver={MySQL_ODBC};Server=127.0.0.1;Database=myDataBase;Uid=root;Pwd=myPassword;