ODBC Documentation 🔼

The ODBC are API's provided to abstract database access, so provide a common access to DBMS using standards SQL sentences.

This documentation has two parts, and overall ODBC documentation and a specific Devuan ODBC documentation. The firs part are provided due most administrators and developers must understand the ODBC infrastructure to property debug problems.

Overall ODBC 🔼

Open Database Connectivity, commonly ODBC, is an open specification for providing application developers with a predictable API with which to access Data Sources, transferring SQL and meta-data queries to the database and conveying the results back. An ODBC engine needs module drivers to be able to interact with specific database types.

There two software choices in the Linux and Unix world: UnixODBC and iODBC.

From the end-user perspective, one application can be used quickly, easily and uniformly against many different backend sources of data.

From the programmer's perspective, ODBC is a C-based API that saves time and effort writing applications. Instead of writing using native access methods for Oracle, Sybase, PostgreSQL etc, you write once against ODBC and point the data-source wherever you desire at run-time.

From the system administrator's perspective, it's just another small package to maintain.

About ODBC installations 🔼

The Linux and Unix ODBC are divided into two types of software, the base driver manager software that provide API and manage the connectivity; and the driver modules that implements and provide specific API connectivity bridge to each DBMS.

In Unix and Linux there are packages for install it manually. For example, in the Debian distribution, the unixODBC package is divided into two parts, the basic unixodbc packages that provides base software manager, and the module drivers packages that contains specific connectivity depending of the DBMS.

The base manager defines and provide the API manager to use and connect to each DBMS using a common interface. If you prefer to compile the package yourself you can download the source code from the unixODBC (http://www.unixodbc.org) web site http://www.unixodbc.org, and follow the compilation and installation instructions that come with the package.

The module drivers are the necessary UnixODBC components to connect to each different DataBase types, and are not included or provided by the main unixODBC package in any Linux distribution. By example the mysql odbc connector related module driver permits to the main UnixODBC infraestructure connects to a MySQL DBMS, are so called myodbc in Debian based Linux distributions. For manually compile those module drivers, will need the development header files of the UnixODBC installation.

About ODBC Configurations 🔼

Any ODBC component relies on the configuration made in the ODBC driver modules configurations. The ODBC specification uses a so called Data Source Names definitios or DSN's to connect to differents DBMS.

UnixODBC uses the so called "ini" files. UnixODBC distinguishes between two types of ini files. System ini files (designed to be accessible but not modifiable by any user), and user ini file (that are private to a particular user, and may be modified by that user).

System ini files: There two, system file odbcinst.ini contains information about ODBC module drivers available to all users, and the odbc.ini file contains information about DSN's definitions to connect to databases, available to all users. These "System DSN's" are useful for application such as web servers that may not be running as a real user and so will not have a home directory to contain a .odbc.ini file.

User ini file: there only one, the .odbc.ini with a "dot" before the name, here there are the definitions for use only by the user and are located in the root home of the owner user.

The type of connection are defined by the odbcinst.ini configuration file, each module driver define a module driver type name, and then this definition are used by the DSN's connection definitions. The second configuration file odbc.ini usage are optional, can be used to DSN's database connection properties like the user name, and password are defined.

The DSN are connections strings, can be defined in the odbc.ini file or by a connection string, the connection string its all the properties separated by a semi-colon while in the odbc.ini file connection properties are separated by new lines and each connection are divided by ini file sections.

UnixODBC vs iODBC 🔼

Apparently is more widely supported UnixODBC due the iODBC need header sections to understand the definitions on each configurations. UnixODBC have better 64bit support, are more widely maintained (less activity but more stability solutions).

iODBC are need in Debian and Devuan due UnixODBC qt GUI in the past need a ODBC lib to build against. So Who is first, the egg or the chicken?, this problem are called circular dependency problem between packages: the main reason for not killing iODBC off in the past was indeed the wretched circular build-dep with Qt need for the tools, but today the qt GUI tool are packaged separately.

So UnixODBC has the most secure choice, but iODBC was the firs implementation and also the started work for, the Licensing indefinitios let door opens to the UnixODBC and today are API compatible in most cases.

UnixODBC Connection Configuration 🔼

The extents of the ODBC its as far of their driver modules are available for. The list of more available module drivers are in http://www.unixodbc.org/drivers.html, by example for connect to a DB2 DBMS a EasySoft module driver must be installed and configured in the UnixODBC installation system.

Configure a module driver 🔼

The UnixODBC module drivers are registered in the odbcinst.ini file, their files are library extensions that are also defined in same file. Here a example of a Sybase module driver support for an ODBC sybase connection.

[ODBC]
Trace       = Yes
TraceFile   = /var/log/odbcsql.log
ForceTrace  = Yes

[FreeTDS]
Description = FreeTDS Driver for Linux using guindosers DBs
Driver      = /usr/lib/odbc/libtdsodbc.so
Setup       = /usr/lib/odbc/libtdsS.so
UsageCount  = 1

NOTE1: All the ODBC driver modules at install, register automatically in the odbcinst.ini file except for FreeTDS.

NOTE2: most modules does not use absolute path, so may will faild and its better set manually

Configure a DSN connection 🔼

Once registered the driver module want to use, have to define a Data Source Name event by definition or by full ODBC connection string.

In the Data Source Name definition file odbc.ini you can also specify all the parameters needed to connect to the database. All the parameters are module driver database specific and may vary from database to database.

As you can see, in the following example, the configuration of the two DSN are different because the parameters needed are database specific. The first is for a MySQL database (that is running locally), the second is for a Sybase running on a remote server.

[odbcmysql1]
Driver      = MySQL
Database    = mysql
Server      = localhost
Port        = 2638
ReadOnly    = No

[odbcsybase1]
Driver      = FreeTDS
Database    = sybasedemo
Server      = 10.10.200.10
Port        = 2638
ReadOnly    = No
TDS_Version = 5.0

You must have one Database driver configured for each Database type, and one Data Source for each Database you want to use. For example, if you have two MySQL Databases, where the first is running on remote system A and the second on remote system B, you will have only one Database module driver configured (MySQL odbc connector), but two different Data Sources, one Data source will describe the connection (and all the relative parameters) to system A and one Data source will describe the connection (and all the relative parameters) for system B.

unixODBC Configuration Examples 🔼

There here are some ODBC setups for Linux. Those assumed the odbcinst. ini are in /etc and odbc.ini are also in /etc, for user asumed ".odbc.ini" in home directory and a normal user.

Example of the odbcinst.ini located in the /etc directory. This file contains all the driver configurations and the Driver Manager\s configuration options

Example of /etc/odbcinst.ini file system wide definition 🔼

This show a example general file of odbcinst.ini file for the most used ODBC module drivers:

odbcinst.ini

[ODBC]
Trace           = No
TraceFile       = /tmp/sql.log
ForceTrace      = No
Pooling         = No


[MySQL]
Description     =
Driver          = /usr/lib/odbc/libmyodbc.so
Setup           = /usr/lib/odbc/libodbcdrvcfg1S.so
UsageCount      = 1

[FreeTDS]
Description     = TDS driver Sybase SQL
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       = 
CPReuse         = 
UsageCount      = 1

[PostgreSQL]
Description     = PostgreSQL ODBC driver (Unicode version)
Driver          = /usr/lib/odbc/psqlodbcw.so
Setup           = /usr/lib/odbc/libodbcpsqlS.so
Debug           = 0
CommLog         = 1
UsageCount      = 1

Example of /etc/odbc.ini file system wide 🔼

System wide odbc.ini located in the /etc directory using the previous odbcinst. ini definition. The odbc.ini file permits passwords, but remember that the system wide odbc.ini file are readable by any loged user.

odbc.ini

[mysqlodbc-testdb]
Description     = MySQL
Driver          = MySQL
Host            = localhost
Database        = testdb
Port            =

[mysqlodbc-kinderdb]
Description     = MySQL
Driver          = MySQL
Host            = 10.10.200.10
Database        = kinderdb
UserName        = root
Password        = mysql.secret
Port            = 3369
ReadOnly        = No

[ejabberd-postgres]
Description     = PostgreSQL ejabberdb conection
Driver          = PostgreSQL
Trace           = No
Database        = ejabberdnode
Server          = 127.0.0.1 # if use Servername, must be equal of the node host ejabberd name
Port            = 5432
ReadOnly        = No
RowVersioning       = No
ShowSystemTables    = Yes
ShowOidColumn       = No

[sainthacking]
Description     = Conecion a saint to hack and crack
Driver          = FreeTDS
Trace           = Yes
TraceFile       = /tmp/errc.log
Database        = saintnonmina
Server          = 192.168.1.100
Port            = 1723
ReadOnly        = No
TDS_Version     = 8.0

Example of /.odbc.ini file user wide 🔼

User wide odbc.ini are located in the /home/<user> directory using the system wide odbcinst.ini definition. The user odbc.ini file permits passwords, and that file must be "chown" to able readable by owner home user.

User\s odbc configuration located in the user\s home directory .odbc.ini

.odbc.ini

[saintuseraccess]
Description     = Conecion a saint with auto user and password
Driver          = FreeTDS
Trace           = Yes
TraceFile       = /tmp/errc.log
Database        = saintnonmina
Server          = 192.168.1.100
UserName        = dba
Password        = sql
Port            = 1723
ReadOnly        = Yes
TDS_Version     = 8.0


[kinderdb-useronly]
Description     = MySQL
Driver          = MySQL
Host            = 10.10.200.10
Database        = kinderdb
UserName        = root
Password        = mysql.secret
Port            = 3369
ReadOnly        = No

[ejabberd-useronly]
Description     = PostgreSQL ejabberdb conection
Driver          = PostgreSQL
Trace           = No
Database        = ejabberdnode
Server          = 127.0.0.1 # if use Servername, must be equal of the node host ejabberd name
UserName        = root
Password        = postgres.secret
Port            = 5432
ReadOnly        = No
RowVersioning       = No
ShowSystemTables    = Yes
ShowOidColumn       = No

Connectiong and testing the ODBC DSN definitions 🔼

The access can be tested if the UnixODBC package are complete installed with the isql utility.

Depending of the ambit of the odbc.ini file.. system wide DSN of the system wide odbc.ini files can be tested and used by any users loged; user wide DSN from user wide odbc.ini files only can be used and tested by their users owners.

The format of the command are easy as:

isql \<DSN name> \<user> \<password>

, and the username and password are optional, but in some module drivers such FreeTDS are mandatory.

/bin/bash

% isql ejabberd-useronly username secretpassword.123
+---------------------------------------+
| Connected!                             |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>select * from users

About Devuan ODBC packages 🔼

All the ODBC Devuan packages are almost always same as Debian based ODBC modules, so for reference you can made usage of the search package interface of the Debian package page.

Devuan ODBC managers Packages 🔼

There are the list of the current ODBC packages software, with some Debian reference search:

Devuan ODBC module drivers packages 🔼

All the Devuan or Debian derived packages are build agains UnixODBC, and some does not compile agains iODBC.

There are the list of the current ODBC driver modules packages, with some Debian reference search:

Devuan UnixODBC how to's 🔼

All the ODBC Devuan packages are build and also are same as Debian tis is due there's no systemd depends or any system differences between two, inclusivelly for any other Debian based should work this document.

This documentation are focused on UnixODBC as its more compatible with most used libraries and software.

1) install and prepare UnixODBC 🔼

The libraries need for most programs and the setup driver module programs for module registrering:

apt-get install  libodbc1 odbcinst odbcinst1debian2

For isql test tool that permits consult and run querys in console:

apt-get install unixodbc

2) install and prepare sqlite odbc 🔼

First install the odbc driver module and the sqlite manager packages are not in default Devuan repositories for some reason does not auto mirror, so added a Debian repository temporally and then removeit.

apt-get install unixodbc odbcinst odbcinst1debian2 

While the Debian repository are temporally active, install the respective sqlite odbc packages, the package will register a driver module named "SQlite3" and another named "SQlite" for sqlite versions 3 and 2 respectively:

apt-get install libsqliteodbc sqlite3 sqlite

If all are good, will registered automatically the SQLite and SQLite3 driver modules for usage and console will outputs:

SQLite has been deleted (if it existed at all) because its usage count became zero
SQLite3 has been deleted (if it existed at all) because its usage count became zero
odbcinst: Driver installed. Usage count increased to 1. 
    Target directory is /etc
odbcinst: Driver installed. Usage count increased to 1. 
    Target directory is /etc

Now remove the Debian repositories and proceed with proper ODBC task.

Create a testdb.sqlite database, then create a table "tabla1" with one column "col1" and then type ".quit" to exit:

mkdir -p /tmp/workingdb/
sqlite3 /tmp/workingdb/testdb.sqlite
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tabla1 ( col1 TEXT );
sqlite> .quit

Create a ODBC DSN access entry point named "odbcsqlite" in the system wide odbc.ini file for the newer testdb.sqlite database file:

[odbcsqlite]
Description=Sqlite v3 odbc access to testdb
Driver=SQLite3
Database=/tmp/workingdb/testdb.sqlite
Timeout=2000

Now lest try to test the odbc DSN access with isql command line tool:

isql odbcsqlite -v
+---------------------------------------+
| Connected!                             |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from tabla1
+-----------+
| count(*)  |
+-----------+
| 0         |
+-----------+
SQLRowCount returns 0
1 rows fetched

NOTE there a more package that extends the features of the odbc sqlite funtionality: libsqlite3-mod-blobtoxy, libsqlite3-mod-csvtable, libsqlite3-mod-impexp, libsqlite3-mod-xpath, libsqlite3-mod-zipfile; but its only to work with sqlite version 3.

IMPORTANT CONSIDERATIONS

• The Sqlite3 driver reference for module usage was SQLite3 and that string are used in second line of the odbc.ini DSN definition, specificaly in the "Driver" parameter, must be same and are case

• The Database name must be reference as absolute path in the odbc.ini DSN definition, specificaly in the "Database" parameter, must be exact as do a pwd + file ("/tmp/workingdb/" + "testdb.sqlite") and are case sensitive.
• The isql command line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name are "odbcsqlite" and will define the odbc.ini section of the DSN overall for specific

3) install and prepare postgres odbc 🔼

4) install and prepare mariadb/mysql odbc 🔼

The MySQL connector in Deviuan are derived from Devian and are stable but out of date, also more important, MariaDB and MySQL has own connector driver modules, but the provided in Debian server same for both DBMS.

First install the odbc driver module and the mysql/mariadb minimal packages, unfortunately Devuan or Debian package not will register a driver module so you must be done manually until debian or devuan mantainers fixed the package. The VenenuX package do the registration of driver module automatically. We here use the MariaDB as target DBMS for MySQL example installation:

apt-get install unixodbc odbcinst odbcinst1debian2 libmyodbc mariadb-client

Of course if use optionally a local server as we do here for practice (the "mariadb-server" package), the setup of mariadb/mysql are out of scope, provide a password for root user and the take care of the ODBC related output.

apt-get install mariadb-server

If all are good, and due the package not will register a driver module now we must registered the ODBC MySQL driver manager for MySQL manually, the process are based on the old made for any ODBC in the Debian older versions as in this based on Postgres ODBC setup old guide:

odbcinst -i -d -f /usr/share/libmyodbc/odbcinst.ini
odbcinst: Driver installed. Usage count increased to 1. 
    Target directory is /etc

Now a driver manager are registered as "MySQL", now create a MySQL test table in the remote target DBMS (local if choose optionally the "mariadb-server" in install step), for this example we use default main database "mysql", the table will named "tabla1" with one column "col1" and then type ".quit" to exit:

mysql --user=root --password=root --host=127.0.0.1 mysql 
Welcome to the MariaDB monitor.   Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 5.3.13-1vnzsq1 (VenenuX)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others
MariaDB [mysql]> create table tabla0 ( col1 TEXT );
Query OK, 0 rows affected (0.14 sec)
MariaDB [mysql]>quit

Create a ODBC DSN access entry point in the system wide odbc.ini file for the newer "mysql" database access as:

[odbcmysql]
Driver       = MySQL
Description  = MySQL v3 odbc access to mysql db
Database     = mysql
Server       = 127.0.0.1
Port         = 3306
User         = root
Password     = toor
Option       = 3
Socket       = 

Now lest try to test the odbc DSN access with isql command line tool:

isql odbcmysql root toor -v
+---------------------------------------+
| Connected!                             |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from tabla1
+-----------+
| count(*)  |
+-----------+
| 0         |
+-----------+
SQLRowCount returns 1
1 rows fetched

NOTE due practical reasons, we emulates the remote target DBMS here as local, that's why a extra step witch install the server part, that choose optionally the "mariadb-server" in install command step.

IMPORTANT CONSIDERATIONS

• The MySQL/MariaDB driver reference for module usage was MySQL and that string are used in second line of the odbc.ini DSN definition, specificaly in the "Driver" parameter, must be same and are case

• The Database name must be reference in the odbc.ini always, specificaly in the "Database" parameter, we use the default internal "mysql" and are case sensitive.
• The Server parameter are important, must be ip address of the target DBMS, here we emulate remote server as "localhost" or "127.0.0.1" for connected database, please never use "Servername" parameter.
• The isql command line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name are "odbcmysql" and will define the odbc.ini section of the DSN overall for specific database type.

5) install and prepare freetds odbc 🔼

Freetds Debian/Devuan package are very out of date, due up to date added support for nvarchar, nchar and ntext sql types, very important for Sybase proxy tables and Unicode support due the size of the variables there's a implicit convertion.

6) install and prepare mdbtools odbc 🔼