OpenLink Interactive ISQL demo (Node.js/odbc)

Copyright (C) 2019 OpenLink Software

NOTE: This is a draft document not yet ready for publication

Introduction

This docker image uses Node.js and the node-odbc module, which uses the unixODBC driver manager and a compliant ODBC driver to connect to an OpenLink Virtuoso database.

To make the docker image as small as possible, we used the node:11-alpine docker image, as well as an Alpine port of the latest version of the OpenLink Virtuoso Open Source Edition Unicode driver virtodbcu_r.so.

Quick Start

First we need to pull the docker image to our local system using the following command:

$ docker pull openlink/node-isql

We can now make a test connection to the OpenLink Demo Instance using the following command:

$ docker run -i -t openlink/node-isql demo.openlinksw.com:1111 demo demo

OpenLink Interactive SQL (Node.js)
Version 0.2 as of January 2019
Copyright (C) 2019 OpenLink Software
SQL>

and run a simple query like:

SQL> select top 1 * from demo..customers
[ { CustomerID: 'ALFKI',
    CompanyName: 'Alfreds Futterkiste',
    ContactName: 'Maria Anders',
    ContactTitle: 'Sales Representative',
    Address: 'Obere Str. 57',
    City: 'Berlin',
    Region: null,
    PostalCode: '12209',
    Country: 'Germany',
    CountryCode: 'gm',
    Phone: '030-0074321',
    Fax: '030-0076545' } ]
SQL> quit
done
$

The node-odbc module returns the resultset as a JSON structure as this makes it very easy to process the resulting dataset.

NOTE: this simple ISQL demo program only works with single line SQL statement.

As the Virtuoso Database Engine is also able to parse and execute SPASQL queries which extends the SQL standard to enable the execution of SPARQL queries over an SQL/ODBC connection, we can also make queries such as:

SQL> sparql select count(1) as ?triple_count where {?s ?p ?o}
[ { triple_count: 170245468 } ]

Connect string

This demo uses several mechanisms to create an ODBC connect string.

Using Docker environment variables

Docker provides a standard way to passing information to the running image using environment variables using -e NAME=VALUE arguments as part of the docker create or docker run command.

The following variables are used:

environment		default

VIRT_HOST		host.docker.internal:1111
VIRT_UID		dba
VIRT_PWD            dba

Note that as the application is running in a container, connecting to localhost would normally result in an attempt to connect to a virtuoso instance running inside the container itself and not to a virtuoso instance that is running on the host that started the docker container.

To avoid any confusion the docker-entrypoint.sh script replaces localhost with host.docker.internal in the VIRT_HOST environment which is a special network mapping to the host that started the docker container.

Using the command line

The docker-entrypoint.sh script that is run on startup of the docker container optionally takes the first 3 arguments of the docker run command to overrule the environment variables described above.

$ docker run -i -t openlink/node-isql HOST:PORT UID PWD

    argument         environment

    HOST:PORT        VIRT_HOST
    UID              VIRT_UID
    PWD              VIRT_PWD

DSN-less connections

Before calling the node-isql.js script, the docker-entrypoint.sh script creates an environment variable called ODBC_CONNECT_STRING which concats the following key=value;key=value pairs to form the ODBC connect string.

The node-isql.js passes this DSN-less connection string via the db.open() call to the SQLDriverConnectW call in the ODBC Driver Manager without the need for creating entries in the odbc.ini file.

    key              value

    DRIVER           /opt/virtuoso-opensource/lib/virtodbcu_r.so
    HOST             $VIRT_HOST
    UID              $VIRT_UID
    PWD              $VIRT_PWD
    WideAsUTF16      1
    CHARSET          UTF-8

Example

$ docker run -i -t -e VIRT_HOST=example.com:4321 openlink/node-isql localhost:1234 demo

which the docker-entrypoint.sh script interprets as:

1. environment variable VIRT_HOST is specified and set to example.com:4321
2. environment variable VIRT_UID is not set, so defaults to dba
3. environment variable VIRT_PWD is not set, so defaults to dba
4. argument 1 is supplied, so VIRT_HOST is set to localhost:1234
5. argument 2 is supplied, so VIRT_UID is set to demo
6. argument 3 is not supplied, so VIRT_PWD remains dba
7. VIRT_HOST is rewritten from localhost:1234 to host.docker.internal:1234

    environment		value

    VIRT_HOST		host.docker.internal:1234
    VIRT_UID		demo
    VIRT_PWD            dba

and the final connect string is:

export ODBC_CONNECT_STRING="DRIVER=/opt/virtuoso-opensource/lib/virtodbcu_r.so;HOST=host.docker.internal.1234;UID=demo;PWD=dba;WideAsUTF16=1;CHARSET=UTF-8"