Notes for Using ODBC Driver

ODBC on Linux

The NuoDB unixODBC driver is part of the NuoDB installation package. The unixODBC project is a complete, free/open, ODBC solution for Linux; see http://www.unixodbc.org for more information. If unixODBC is not installed on your system, download it from www.unixodbc.org, build and install it. References below to command line utilities including isql and odbcinst are part of the unixodbc build.

The NuoDB unixODBC driver is built against version 2.3.x of the unixODBC library. It does not support unixODBC versions 2.2.x or earlier. Some of the Linux distributions NuoDB supports ship with unixODBC 2.2.x. If your distribution uses unixODBC 2.2.x or earlier, upgrade unixODBC to version 2.3.x or later. You can use isql --version to determine the version of unixodbc installed.

There are two steps to getting NuoDB and unixODBC to work on your system.

  1. Install the NuoDB Driver

  2. Install a NuoDB Data Source.

Install the NuoDB Driver

Follow instructions at unixodbc.org to install a database driver. One way to do this for NuoDB, is to create a simple template with the following three lines:

Example: nuodb.template

[NuoDB]
Description     = NuoDB driver
Driver          = /opt/nuodb/lib64/libNuoODBC.so

If you installed NuoDB in a non-default directory, you will have to update the path to the libNuoODBC.so library.

Install the NuoDB driver, using the following odbcinst command, assuming the file above is called nuodb.template (it can be named whatever you choose):

$ sudo /usr/local/bin/odbcinst -i -d -f nuodb.template
odbcinst: Driver installed. Usage count is set to 1.
Target directory is /etc
This reports that the target directory is /etc and it updates a file called /etc/odbcinst.ini. The target directory is variable based on how you configured unixodbc; it may be different on your system.

Install a NuoDB Data Source

Let’s assume you have a QuickStart database built on your system. See Running the SQL QuickStart for information about creating the SQL QuickStart database. The database name is test, it runs on localhost and the broker port is the default 48004. Again we can create a simple template:

Example: nuodb_dsn.template

[QuickStart]
Description         = NuoDB QuickStart Database
Driver              = NuoDB
Database            = test
ServerName          = localhost
Port                = 48004

You can alternatively specify the User and Password in the template as well. If you don’t specify them, then any ODBC client connecting to the database will have to provide these.

The Data Source can be installed with odbcinst as well. Notice above we used -d to install the driver and here we use -s to install the data source.

$ sudo /usr/local/bin/odbcinst -i -s -f nuodb_dsn.template  -v
odbcinst: Sections and Entries from nuodb_dsn.template have been added to ODBC.INI

This should create or append to /etc/ocbc.ini with the contents above. If for some reason this does not work, you can edit /etc/odbc.ini directly. Again, the /etc target directory is variable based on the configuration of unixodbc.

An alternate format for the data source template above would be where we specify the SQL connection string, i.e. dbname@host:port for Dbname and omit Database, Server, and Port as in the following:

[QuickStart]
Description         = NuoDB QuickStart Database
Driver              = NuoDB
Dbname              = test@localhost:48004

Access Data Source

You can now access the Data Source, that is the QuickStart database, using isql as in the following example.

We are specifying the user (dba) and password (goalie) as command line arguments: 
$ isql QuickStart dba goalie -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SQL> select * from hockey.scoring;
+-----------+------------+------------+-------+---------+------------+------------+------------+---------------+
| PLAYERID  | YEAR       | STINT      | TEAMID| POSITION| GAMESPLAYED| GOALS      | ASSISTS    | PENALTYMINUTES|
+-----------+------------+------------+-------+---------+------------+------------+------------+---------------+
| aaltoan01 | 1997       | 1          | ANA   | C       | 3          | 0          | 0          | 0             |
| aaltoan01 | 1998       | 1          | ANA   | C       | 73         | 3          | 5          | 24            |
| aaltoan01 | 1999       | 1          | ANA   | C       | 63         | 7          | 11         | 26            |
| aaltoan01 | 2000       | 1          | ANA   | C       | 12         | 1          | 1          | 2             |
| abbeybr01 | 1975       | 1          | CIN   | D       | 17         | 1          | 0          | 12            |
...