OpenLink Interactive ISQL demo (Node.js/odbc)

Tools & Utilities iODBC DM & SDK
, , , ,
1

What?

A docker image using Node.js and the node-odbc module, which uses the unixODBC implementation of an ODBC 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 enabled ODBC driver named virtodbcu_r.so.

Why?

Demonstrates how node.js application can make ODBC binding for connecting to ODBC compliant databases, using an available ODBC Driver for the target database.

How?

Constructing a DSN-less connect string

The docker-entrypoint.sh startup script first constructs an ODBC connect string from information provided via docker environment settings as well as the command-line options, before executing the node-isql.js script.

The node-isql.js script calls the node-odbc module which in turn passes the connect string to the ODBC Driver Manager using the SQLDriverConnectW ODBC API function. The ODBC Driver Manager loads the DRIVER listed in the connect string, in this example the OpenLink Virtuoso Unicode ODBC driver, which it then uses to make the actual connection to the Virtuoso database engine, execute the queries and retrieve the result set.

The following key=value pairs are needed to make a working connection to Virtuoso:

  key              value

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

The combined connect string looks like this:

  DRIVER=/opt/virtuoso-opensource/lib/virtodbcu_r.so;HOST=demo.openlinksw.com:1111;UID=demo;PWD=dema;WideAsUTF16=1;CHARSET=UTF-8

As this form of the connect string has all the information needed to make a connection, there are no entries needed in either the odbc.ini or odbcinst.ini configuration files. This is also known as a DSN-less connection.

Using Docker environment variables

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

The following table lists the variables names that are used including the default values that are assigned in case these names are not provided on either the command line or in the .yaml file:

  name             default value

  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 after the openlink/node-isql image name of the docker run or docker create command to overrule the environment variables described above.

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

  1  HOST:PORT     VIRT_HOST
  2  UID           VIRT_UID
  3  PWD           VIRT_PWD

ODBC Connection Options via a Connection String

The following example shows all the steps the docker-entrypoint.sh script takes to create the ODBC_CONNECT_STRING:

  $ 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

The final values of the environment variables are:

  variable         value

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

and the final connect string is:

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

Live Usage Example

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 Live OpenLink Virtuoso Demo Instance, using a sandboxed user account ‘vdb’ via the following command:

  $ docker run -i -t openlink/node-isql demo.openlinksw.com:21118 vdb vdb
  OpenLink Interactive SQL (Node.js)
  Version 1.0 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 query solution in JSON format.

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

Courtesy of Virtuoso’s dual support for both the SQL and SPARQL query languages for operating on Relations (Tables or Graphs), you can also use this iSQL instance to issue queries targeting local RDF-based structured data and/or data across the massive Linked Open Data Cloud Knowledge Graph.

SQL> SELECT TOP 10 s 
     FROM (
            SPARQL 
            SELECT DISTINCT ?s 
            WHERE {
                    ?s a schema:Organization. 
                    FILTER (! CONTAINS(STR(?s),'demo'))
                   }
          ) AS X  

[ { s: 'http://dbpedia.org/resource/Al-Andalus_University_for_Medical_Sciences' },
  { s: 'http://dbpedia.org/resource/Al-Baath_University' },
  { s: 'http://dbpedia.org/resource/Al-Furat_University' },
  { s: 'http://dbpedia.org/resource/Al-Shahba_University' },
  { s: 'http://dbpedia.org/resource/Al-Wataniya_Private_University' },
  { s: 'http://dbpedia.org/resource/Aljazeera_University' },
  { s: 'http://dbpedia.org/resource/Arab_International_University' },
  { s:
     'http://dbpedia.org/resource/Faculty_of_Medicine_of_Syrian_Private_University' },
  { s:
     'http://dbpedia.org/resource/Higher_Institute_for_Applied_Sciences_and_Technology' },
  { s:
     'http://dbpedia.org/resource/International_University_for_Science_and_Technology' } ]

Related