HowTo Persistently Install and Configure the OpenLink Single-Tier ODBC drivers on Virtuoso Docker Container

What

Installation and configuration of the OpenLink Universal Data Access Single-Tier ODBC Drivers on a running Virtuoso 8.3 Docker container , such that the ODBC driver installation and configuration persists post docker container restart.

Why

An ODBC driver is required for Linking remote relational database tables into Virtuoso using the Virtual Database Engine. Care needs to be taken to ensure that the ODBC driver is installed on a persistent volume, such that it continues to exist post Virtuoso docker container restart, much as is done with the Virtuoso database files. The ODBC configuration files used by Virtuoso and the ODBC driver also need to be symlink’ed.

How

Create a Virtuoso 8.3 Docker container instance, bearing in mind the port 8000 needs to also be made available for accessing the OpenLink HTTP based iODBC Administrator for Administering the Single-Tier ODBC driver to be installed on the persistent volume of the docker container.

This document will use the OpenLink Single-Tier SQL Server ODBC driver to demonstrate how this is done. Although note that the same process of applies to setting up any other ODBC Driver for use with the Virtuoso docker container.

Virtuoso Docker Installation

  1. Pull the latest OpenLink Virtuoso Enterprise Edition 8 Docker Image image to your docker host environment:
https://hub.docker.com/r/openlink/virtuoso-closedsource-8
  1. Make a physical directory where the Virtuoso database files will be persisted and used as the mount volume for the Virtuoso docker image. In this case we will use directory v83-pgr7mt :
mkdir v83_pgr7mt
cd v83_pgr7mt
  1. Start the Virtuoso 8.3 docker container instance with the docker run command specifying the Virtuoso 1111 & 8890 and OpenLink HTTP based iODBC Administrator 8000 ports to be exposed:
docker run  --name v83-pgr7mt --interactive --tty --env DBA_PASSWORD=mysecret --publish 1111:1111 --publish  8890:8890 --publish 8000:8000 --volume `pwd`:/database  openlink/virtuoso-closedsource-8:latest
  1. Stop the Virtuoso 8.3 Docker container with the Ctrl ^C command
  2. Copy a valid virtuoso.lic license file to the v83_pgr7mt directory
  3. Restart the Virtuoso 8.3 Docker container with the command:
docker start v83-pgr7mt
  1. Connect to the Virtuoso conductor with HTTP URL:
http://localhost:8890

Complete output of steps performed above:

De-iMac-5:docker hwilliams$ docker pull openlink/virtuoso-closedsource-8
Using default tag: latest
latest: Pulling from openlink/virtuoso-closedsource-8
Digest: sha256:29becd24bc2a739bd94401d19a921c816fc758285524d255380c9b7d3e0d3c84
Status: Downloaded newer image for openlink/virtuoso-closedsource-8:latest
docker.io/openlink/virtuoso-closedsource-8:latest
De-iMac-5:docker hwilliams$ mkdir v83-pgr7mt
De-iMac-5:docker hwilliams$ cd v83-pgr7mt/
De-iMac-5:v83-pgr7mt hwilliams$ pwd
/Users/hwilliams/docker/v83-pgr7mt
De-iMac-5:v83-pgr7mt hwilliams$ docker run  --name v83-pgr7mt --interactive --tty --env DBA_PASSWORD=mysecret --publish 1111:1111 --publish  8890:8890 --publish 8000:8000 --volume `pwd`:/database  openlink/virtuoso-closedsource-8:latest
Starting the OpenLink License Manager
Creating initial database directory

- Configuring: database
  - Creating directory $VIRTUOSO_HOME/database/backup
  - Creating directory $VIRTUOSO_HOME/database/logs
  - Installing new virtuoso.ini in $VIRTUOSO_HOME/database
  - Installing new php.ini in $VIRTUOSO_HOME/database
  - Creating symlink to $VIRTUOSO_HOME/bin/virtuoso-iodbc-t
Checking Plugins section
Applying settings to virtuoso.ini
Generating password
Registering ODBC driver
Creating initial database

		Fri Nov 20 2020
23:46:12 { Loading plugin 1: Type `plain', file `evc' in `../hosting'
23:46:12   EVC version 3319 from OpenLink Software
23:46:12   EigenVector Centrality analytics
23:46:12   SUCCESS plugin 1: loaded from ../hosting/evc.so }
23:46:12 { Loading plugin 2: Type `plain', file `geos' in `../hosting'
23:46:12   plain version 1.1.3319 from OpenLink Software
23:46:12   GEOS plugin based on Geometry Engine Open Source library from Open Source Geospatial Foundation
23:46:12   SUCCESS plugin 2: loaded from ../hosting/geos.so }
23:46:12 { Loading plugin 3: Type `plain', file `im' in `../hosting'
23:46:12   IM version 0.63 from OpenLink Software
23:46:12   Support functions for Image Magick 6.9.10
23:46:12   SUCCESS plugin 3: loaded from ../hosting/im.so }
23:46:12 { Loading plugin 4: Type `plain', file `proj4' in `../hosting'
23:46:12   plain version 1.0.3319 from OpenLink Software
23:46:12   Cartographic Projections support based on Frank Warmerdam's proj4 library
23:46:12   SUCCESS plugin 4: loaded from ../hosting/proj4.so }
23:46:12 { Loading plugin 5: Type `plain', file `qrcode' in `../hosting'
23:46:12   QRcode version 0.1 from OpenLink Software
23:46:12   Support functions for ISO/IEC 18004:2006, using QR Code encoder (C) 2006 Kentaro Fukuchi <fukichi@megaui.net>
23:46:12   SUCCESS plugin 5: loaded from ../hosting/qrcode.so }
23:46:12 { Loading plugin 6: Type `plain', file `shapefileio' in `../hosting'
23:46:12   ShapefileIO version 0.1virt71 from OpenLink Software
23:46:12   Shapefile support based on Frank Warmerdam's Shapelib
23:46:12   SUCCESS plugin 6: loaded from ../hosting/shapefileio.so }
23:46:12 OpenLink Virtuoso Universal Server
23:46:12 Version 08.03.3319-pthreads for Linux as of Sep  9 2020
23:46:12 uses OpenSSL 1.0.2u  20 Dec 2019
23:46:12 uses parts of PCRE, Html Tidy
23:46:12 
23:46:12 License not found
23:46:12 Please make sure it is installed correctly
23:46:12 If you did not receive a license file,
23:46:12 contact sales@openlinksw.com or visit www.openlinksw.com
23:46:12 and a new license will be made available to you
23:46:12 
23:46:12 Demo license enabled
23:46:12 
23:46:13 SQL Optimizer enabled (max 1000 layouts)
23:46:13 Compiler unit is timed at 0.000130 msec
23:46:18 Checkpoint started
23:46:18 Roll forward started
23:46:18 Roll forward complete
23:46:18 Checkpoint started
23:46:18 Checkpoint finished, log reused
23:46:19 Checkpoint started
23:46:19 Checkpoint finished, log reused
23:46:21 Checkpoint started
23:46:21 Checkpoint finished, log reused
23:46:22 Checkpoint started
23:46:22 Checkpoint finished, log reused
23:46:22 Checkpoint started
23:46:22 Checkpoint finished, log reused
23:46:22 PL LOG: Installing Virtuoso Conductor version 1.00.8823 (DAV)
23:46:22 PL LOG: Installing with dependencies Virtuoso Conductor version 1.00.8823/2020-09-09 15:13 (DAV)
23:46:22 Checkpoint started
23:46:22 Checkpoint finished, log reused
23:46:27 Checkpoint started
23:46:27 Checkpoint finished, log reused
23:46:27 PL LOG: Installation with dependencies complete
23:46:27 PL LOG: Initializing DB.DBA.SYS_PROJ4_SRIDS
23:46:27 PL LOG: ... checking for data files in "/usr/share/proj"
23:46:28 PL LOG: DB.DBA.SYS_PROJ4_SRIDS now contains 8650 spatial reference systems
23:46:28 Checkpoint started
23:46:28 Checkpoint finished, log reused
23:46:28 Server exiting
Setting passwords

		Fri Nov 20 2020
23:46:29 { Loading plugin 1: Type `plain', file `evc' in `../hosting'
23:46:29   EVC version 3319 from OpenLink Software
23:46:29   EigenVector Centrality analytics
23:46:29   SUCCESS plugin 1: loaded from ../hosting/evc.so }
23:46:29 { Loading plugin 2: Type `plain', file `geos' in `../hosting'
23:46:29   plain version 1.1.3319 from OpenLink Software
23:46:29   GEOS plugin based on Geometry Engine Open Source library from Open Source Geospatial Foundation
23:46:29   SUCCESS plugin 2: loaded from ../hosting/geos.so }
23:46:29 { Loading plugin 3: Type `plain', file `im' in `../hosting'
23:46:29   IM version 0.63 from OpenLink Software
23:46:29   Support functions for Image Magick 6.9.10
23:46:29   SUCCESS plugin 3: loaded from ../hosting/im.so }
23:46:29 { Loading plugin 4: Type `plain', file `proj4' in `../hosting'
23:46:29   plain version 1.0.3319 from OpenLink Software
23:46:29   Cartographic Projections support based on Frank Warmerdam's proj4 library
23:46:29   SUCCESS plugin 4: loaded from ../hosting/proj4.so }
23:46:29 { Loading plugin 5: Type `plain', file `qrcode' in `../hosting'
23:46:29   QRcode version 0.1 from OpenLink Software
23:46:29   Support functions for ISO/IEC 18004:2006, using QR Code encoder (C) 2006 Kentaro Fukuchi <fukichi@megaui.net>
23:46:29   SUCCESS plugin 5: loaded from ../hosting/qrcode.so }
23:46:29 { Loading plugin 6: Type `plain', file `shapefileio' in `../hosting'
23:46:29   ShapefileIO version 0.1virt71 from OpenLink Software
23:46:29   Shapefile support based on Frank Warmerdam's Shapelib
23:46:29   SUCCESS plugin 6: loaded from ../hosting/shapefileio.so }
23:46:29 OpenLink Virtuoso Universal Server
23:46:29 Version 08.03.3319-pthreads for Linux as of Sep  9 2020
23:46:29 uses OpenSSL 1.0.2u  20 Dec 2019
23:46:29 uses parts of PCRE, Html Tidy
23:46:29 Starting for DBA password change.
23:46:29 
23:46:29 License not found
23:46:29 Please make sure it is installed correctly
23:46:29 If you did not receive a license file,
23:46:29 contact sales@openlinksw.com or visit www.openlinksw.com
23:46:29 and a new license will be made available to you
23:46:29 
23:46:29 Demo license enabled
23:46:29 
23:46:29 Database version 3126
23:46:29 SQL Optimizer enabled (max 1000 layouts)
23:46:30 Compiler unit is timed at 0.000174 msec
23:46:32 Roll forward started
23:46:32     3 transactions, 189 bytes replayed (100 %)
23:46:32 Roll forward complete
23:46:32 The DBA password is changed.
23:46:32 The DAV password is changed.
23:46:32 Checkpoint started
23:46:32 Checkpoint finished, log reused
23:46:32 Server exiting
Starting the OpenLink Virtuoso Universal Server

		Fri Nov 20 2020
23:46:33 { Loading plugin 1: Type `plain', file `evc' in `../hosting'
23:46:33   EVC version 3319 from OpenLink Software
23:46:33   EigenVector Centrality analytics
23:46:33   SUCCESS plugin 1: loaded from ../hosting/evc.so }
23:46:33 { Loading plugin 2: Type `plain', file `geos' in `../hosting'
23:46:33   plain version 1.1.3319 from OpenLink Software
23:46:33   GEOS plugin based on Geometry Engine Open Source library from Open Source Geospatial Foundation
23:46:33   SUCCESS plugin 2: loaded from ../hosting/geos.so }
23:46:33 { Loading plugin 3: Type `plain', file `im' in `../hosting'
23:46:33   IM version 0.63 from OpenLink Software
23:46:33   Support functions for Image Magick 6.9.10
23:46:33   SUCCESS plugin 3: loaded from ../hosting/im.so }
23:46:33 { Loading plugin 4: Type `plain', file `proj4' in `../hosting'
23:46:33   plain version 1.0.3319 from OpenLink Software
23:46:33   Cartographic Projections support based on Frank Warmerdam's proj4 library
23:46:33   SUCCESS plugin 4: loaded from ../hosting/proj4.so }
23:46:33 { Loading plugin 5: Type `plain', file `qrcode' in `../hosting'
23:46:33   QRcode version 0.1 from OpenLink Software
23:46:33   Support functions for ISO/IEC 18004:2006, using QR Code encoder (C) 2006 Kentaro Fukuchi <fukichi@megaui.net>
23:46:33   SUCCESS plugin 5: loaded from ../hosting/qrcode.so }
23:46:33 { Loading plugin 6: Type `plain', file `shapefileio' in `../hosting'
23:46:33   ShapefileIO version 0.1virt71 from OpenLink Software
23:46:33   Shapefile support based on Frank Warmerdam's Shapelib
23:46:33   SUCCESS plugin 6: loaded from ../hosting/shapefileio.so }
23:46:33 OpenLink Virtuoso Universal Server
23:46:33 Version 08.03.3319-pthreads for Linux as of Sep  9 2020
23:46:33 uses OpenSSL 1.0.2u  20 Dec 2019
23:46:33 uses parts of PCRE, Html Tidy
23:46:33 
23:46:33 License not found
23:46:33 Please make sure it is installed correctly
23:46:33 If you did not receive a license file,
23:46:33 contact sales@openlinksw.com or visit www.openlinksw.com
23:46:33 and a new license will be made available to you
23:46:33 
23:46:33 Demo license enabled
23:46:33 
23:46:33 Database version 3126
23:46:33 SQL Optimizer enabled (max 1000 layouts)
23:46:34 Compiler unit is timed at 0.000175 msec
23:46:35 Roll forward started
23:46:35 Roll forward complete
23:46:37 Checkpoint started
23:46:37 Checkpoint finished, log reused
23:46:37 HTTP Server threads exceed the number of licensed connections. Setting to 1
23:46:37 HTTP/WebDAV server online at 8890
23:46:37 Server online at 1111 (pid 1)
23:46:38 ZeroConfig registration virtuoso (2A345520F213)
^C
23:49:05 Server received signal 2
23:49:05 Initiating normal shutdown
23:49:05 Checkpoint started
23:49:05 Checkpoint finished, log reused
23:49:05 Server shutdown complete
De-iMac-5:v83-pgr7mt hwilliams$  cp ~/virtuoso.lic .
De-iMac-5:v83-pgr7mt hwilliams$ ls -ltr
total 135232
drwxr-xr-x  2 hwilliams  staff        64 20 Nov 23:46 backup
drwxr-xr-x  2 hwilliams  staff        64 20 Nov 23:46 logs
-rw-r--r--  1 hwilliams  staff      6569 20 Nov 23:46 virtuoso.ini
-rw-r--r--  1 hwilliams  staff       230 20 Nov 23:46 odbcinst.ini
-rw-r--r--  1 hwilliams  staff       262 20 Nov 23:46 odbc.ini
-rw-r--r--  1 hwilliams  staff         0 20 Nov 23:46 virtuoso.pxa
-rw-r--r--  1 hwilliams  staff   2097152 20 Nov 23:46 virtuoso-temp.db
-rw-r--r--  1 hwilliams  staff  67108864 20 Nov 23:49 virtuoso.db
-rw-r--r--  1 hwilliams  staff         0 20 Nov 23:49 virtuoso.trx
-rw-r--r--  1 hwilliams  staff      8731 20 Nov 23:49 virtuoso.log
-rw-r--r--@ 1 hwilliams  staff       739 20 Nov 23:50 virtuoso.lic.
De-iMac-5:v83-pgr7mt hwilliams$ docker start v83-pgr7mt
v83-pgr7mt
De-iMac-5:v83-pgr7mt hwilliams$ tail -30 virtuoso.log
14:03:25   Shapefile support based on Frank Warmerdam's Shapelib
14:03:25   SUCCESS plugin 6: loaded from ../hosting/shapefileio.so }
14:03:25 OpenLink Virtuoso Universal Server
14:03:25 Version 08.03.3319-pthreads for Linux as of Sep  9 2020
14:03:25 uses OpenSSL 1.0.2u  20 Dec 2019
14:03:25 uses parts of PCRE, Html Tidy
14:03:25 Registered to OpenLink Software (INTERNAL USE ONLY)
14:03:25 Personal Edition license for 50 connections
14:03:25 Issued by Patrick van Kleef
14:03:25 This license will expire on Sat Aug 21 13:28:48 2021 GMT
14:03:25 Enabled Cluster Extension
14:03:25 Enabled Column Store Extension
14:03:25 Enabled Virtual Database Extension
14:03:25 Enabled Replication Extension
14:03:25 Enabled Scalable ACL Extension
14:03:25 Enabled Custom Reasoning & Inference Rules
14:03:25 Enabled Shapes Constraint Language
14:03:25 Database version 3126
14:03:25 SQL Optimizer enabled (max 1000 layouts)
14:03:26 Compiler unit is timed at 0.000258 msec
14:03:28 Roll forward started
14:03:28 Roll forward complete
14:03:30 Checkpoint started
14:03:30 Checkpoint finished, log reused
14:03:30 HTTP/WebDAV server online at 8890
14:03:30 Server online at 1111 (pid 1)
14:03:31 ZeroConfig registration virtuoso (2A345520F213)
De-iMac-5:v83-pgr7mt hwilliams$ 

The Virtuoso Conductor can now be accessed on http://localhost:8890/conductor :

OpenLink Single-Tier SQL Server ODBC Driver installation

  1. Download the OpenLink Single-Tier SQL Server ODBC Driver for Generic Linux from the Download Wizard .
  2. In the v83-pgr7mt directory on the Docker host machine create a directory called uda .
  3. Copy the download OpenLink Single-Tier SQL Server ODBC Driver for Generic Linux file namely, install.sh , sql_lt.taz , odbc_admin.taz to the uda directory
De-iMac-5:v83-pgr7mt hwilliams$ cp ~/install.sh .
De-iMac-5:v83-pgr7mt hwilliams$ cp ~/sql_lt.taz .
De-iMac-5:v83-pgr7mt hwilliams$ cp ~/odbc_admin.taz .
  1. Copy the OpenLink Single-Tier SQL Server ODBC Driver license files sql_lt.lic to the v83-pgr7mt directory where all license files to be used on the docker container must be placed. This being /opt/virtuoso/database in the Virtuoso docker container where the virtuoso.lic license file is placed and is the location the OpenLink License Manager (oplmgr) running on the docker container check for ALL license files.
De-iMac-5:v83-pgr7mt hwilliams$ cp ~/sql_lt.lic .
  1. Connect to the Virtuoso docker container image with the command:
docker exec -it v83-pgr7mt /bin/bash
  1. Within the docker container the uda directory is at location /opt/virtuoso/database/uda where the install, sh script can be run to perform the installation as detailed in the OpenLink Single-Tier “Lite” Edition ODBC Driver Installation & Configuration documentation, with the command:
sh install.sh
  1. The Single-Tier SQL Server ODBC driver registration information in /opt/vurtuoso/database/uda/bin/odbcinist.ini needs to be merged into the Virtuoso /opt/virtuoso/database/odbcinist.ini file:
OpenLink SQL Server Lite Driver                           = Installed
OpenLink SQL Server Lite Driver (Multi Threaded)          = Installed

[OpenLink SQL Server Lite Driver (Multi Threaded)]
Driver = /opt/virtuoso/database/uda/lib/sql_mt_lt.so

[OpenLink SQL Server Lite Driver (Unicode)(Multi Threaded)]
Driver = /opt/virtuoso/database/uda/lib/sql_mt_lu.so
  1. With the following in the final /opt/virtuoso/database/odbcinst.ini file for both the Virtuoso and OpenLink Single-Tier Generic ODBC Client drivers:
[ODBC Drivers]
OpenLink Virtuoso           = Installed
OpenLink Virtuoso (Unicode) = Installed
OpenLink SQL Server Lite Driver                           = Installed
OpenLink SQL Server Lite Driver (Multi Threaded)          = Installed

[OpenLink SQL Server Lite Driver (Multi Threaded)]
Driver = /opt/virtuoso/database/uda/lib/sql_mt_lt.so

[OpenLink SQL Server Lite Driver (Unicode)(Multi Threaded)]
Driver = /opt/virtuoso/database/uda/lib/sql_mt_lu.so

[OpenLink Virtuoso]
Driver = /opt/virtuoso/lib/virtodbc_r.so

[OpenLink Virtuoso (Unicode)]
Driver = /opt/virtuoso/lib/virtodbcu_r.so
  1. On completion run the openlink.sh script to setup the Single-Tier ODBC environment with the command:
. ./openlink.sh
  1. Goto the the uda\bin directory and rename the odbc.ini and odbcinst.ini files
mv odbc.ini odbc.ini.orig
mv odbcinst.ini odbcinst.ini.org
  1. Create symlink between the odbc.ini and odbcinst.ini files in the /opt/virtuoso/database such that the Virtuoso Conductor and OpenLink HTTP based iODBC Administrator use the same files for ODBC drivers installation (odbcinst.ini) and ODBC DSN configuration (odbc.ini):
ln -s /opt/virtuoso/database/odbc.ini odbc.ini
ln -s /opt/virtuoso/database/odbciinst.ini odbcinst.ini 
  1. Start the OpenLink HTTP based iODBC Administrator ( iodbc-admin-httpd.sh ) for administration of Single-Tier ODBC client connection requests to the appropriate ODBC driver with the command
./iodbc-admin-httpd.sh start

Configuring ODBC DSN

  1. On the docker host machine load the OpenLink HTTP based iODBC Administrator in the Browser with the URL http://localhost:8000:

  1. Got to the Client Connection Administration -> Configure ODBC Data Sources by Form -> Configure ODBC Data Sources link and click on the Add button to add a new DSN:

  1. Select the New button of the OpenLink SQL Server Lite Driver to add a new Single-Tier ODBC DSN:

  1. Add Single-Tier ODBC DSN params for connecting to the target MS SQL Server database and click the Add button:

  1. Click on the Test DSN link to make a test connection:

  1. Enter valid password and click the Test button:

  1. The Connected OK message is displayed to indicate a successful connection:

  1. Click on the ODBC Data Source Names List (DSN) link to confirm the new DSN has been added:

  1. Login to the Virtuoso Conductor at http://localhost:8890/conductor:

  1. Goto to the Database -> External Data Sources -> Data Sources tab to see the newly available Single-Tier ODBC DSN for SQL Server (sqllu):

  1. Choose the Connect button of the SQL Server ODBC DSN to connect to the database:

  1. Enter the User name and Password for the database and click the Connect button:

  1. From the list of tables present select the table to be attached / linked into Virtuoso and click the Link button:

  1. Set suitable Catalog , Owner , Table Name and Primary keys to attached the table as in Virtuoso and click the Link button:

  1. The selected tables are then linked into Virtuoso and the hyperlinks for them can be clicked on to load into the Virtuoso Interactive SQL UI for execution:

  1. Click on the Execute button to query the remote table:

  1. The results of querying the remote table are displayed:

Related