openlink/node-isql repository overview

⁠OpenLink Interactive ISQL demo (Node.js/odbc)

Copyright (C) 2019-2025 OpenLink Software

Table of Contents

• Introduction⁠
• Quick Start⁠
• Constructing a DSN-less connect string⁠
  • Using Docker environment variables⁠
  • Using the command line⁠
  • Example⁠

⁠Introduction

This docker image uses 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.

⁠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 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 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 } ]

⁠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 commandline 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

⁠Example

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