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. This documentation starts on the "friendofdevuan" wiki due ODBC information was very poor inclusivelly in oficial Debian wiki, and now this content under the following CC Attribution-Share Alike 3.0 Unported license.
ODBC Documentation
The firs part are provided due most administrators and developers must understand the ODBC infrastructure to property debug problems.Second part of documentation describe Devuan packages, references and status of, also install howto's and usage tests.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 driver modules to be able to interact with specific database types.
There two software choices in the Linux and Unix world: UnixODBC (www.unixodbc.org) and iODBC (www.iodbc.org).
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 driver modules 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 web site, and follow the compilation and installation instructions that come with the respective package.
The driver modules 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 driver module permits to the main UnixODBC infraestructure connects to a MySQL DBMS, are so called libmyodbc in Debian based Linux distributions. For manually compile those driver modules, will need the development header files of the UnixODBC installation. (Back to top index here Overall ODBC )
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 driver modules 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 driver module define a driver module 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 for access. (Back to top index here Overall ODBC )About DSN types and ODBC usage way
The Data Source Names or DSN are connections strings, can be defined in the
odbc.ini file or used directly in any ODBC implementation. The two types of odbc.ini files: System DSN's and User DSN's differ only in who can access them on the system.
In UnixODBC or iODBC there's two way of use a DSN, a selfcomplete DSN have all the necesary configurations in their definition, and a described DSN have only specific database configurations due the driver module are only referenced to the
odbcinst.ini file. Generally those selfcomplete DSN are in a form of “connection strings”. File DSN always contains connection strings.
The ODBC are used by calling these connection strings, a connection string its all the properties separated by a semi-colon, while in the
odbc.ini file connection properties are grouped by section names separated by new lines, each group represents a DSN connection and those groups are “ini file sections”. (Back to top index here Overall ODBC )File DNS notes
ODBC v3 specification also has a third sort of DSN, a File DSN, is not really a datasource. It is a file that contains a connection string or all the connection parameters used to connect directly to an ODBC driver.
They are useful things but of less use to UNIX's than NT. Its a mocosoft-like implementation, assumed that everyone should have Win32 on their desk; File DSN's are a fix to allow the information to be stored in a central server that is accessable to all the workstations by remote registry access.
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 was need in Debian past 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 and each project has their own manager GUI interface. (Back to top index here Overall ODBC )
| Feature | UnixODBC | iODBC |
|---|---|---|
| Unicode support | SQLWCHARs as 2 bytes UCS-2 | wchar_t UCS-2, UTF-8, UCS-4 |
| cursor library | yes | yes (today) |
| thread safety | yes | yes |
| specification | 2.0, 3.0, 3.5, 3.8 | 2.0, 3.0, 3.5 |
| 32 vs 64 bit sup | either 32-bit or 64-bit SQLLEN/SQLULEN types | (3.5+) 64-bit SQLLEN/SQLULEN types |
| gui manager | ODBCManageDataSourcesQ4 | iodbcadm-gtk |
| application widely | almost all | few and included in MacOS |
| system operatin widely | almost all | some distributions does not offer |
| Licensing | GPL+LGPL | BSD+LGPL |
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. (Back to top index here Overall ODBC )
UnixODBC Connection Configuration
The extents of the ODBC its as far of their driver modules are available for. The list of more available driver modules are in http://www.unixodbc.org/drivers.html, by example for connect to a DB2 DBMS a EasySoft driver module must be installed and configured in the UnixODBC installation system.
Configurations for driver module
The UnixODBC driver modules 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 driver module support for an ODBC sybase connection.
Mostly these configuration files are only for organization purposes respect upgrades. Driver modules can be used directly in any DSN configuration.
[FreeTDS] Description = FreeTDS Driver for Linux using guindosers DBs Driver = /usr/lib/odbc/libtdsodbc.so Setup = /usr/lib/odbc/libtdsS.so UsageCount = 1
NOTE1: On Devuan only ODBC driver modules for mysql and postgres at install, register automatically in the odbcinst.ini file. VenenuX packages are complete automatically register all modules.
NOTE2: On Devuan modules does not use absolute path, if fails set manually, path depends of multiarch installs. VenenuX packages are absolute path and not multiarch modules.
Configurations for 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. Remenber that diver module can be reference by registration of directly by the install path, see example below.
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 driver module database specific and may vary from database to database.
As you can see, in the following example, the configuration of the two first 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. The tirth are same as the first, but used directly the driver module that bypass usage by reference name registered, this way its more directly but less flexible when operating system upgrades are performed.
[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 [odbcmysql2] Driver = /usr/lib/i386-linux-gnu/odbc/libmyodbc.so Database = mysql Server = localhost Port = 2638 ReadOnly = No
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 driver module 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. (Back to top index here Overall ODBC )
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 respective home directory and a normal user. (Back to top index here Overall ODBC )Example of /etc/odbcinst.ini file system wide definition
Example of the
odbcinst.ini located in the /etc directory. This file contains all the driver module configurations and their options, are used by reference their names registered:[ODBC] Trace = Yes TraceFile = /tmp/odbcsql.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 optionally 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.[mysqlodbc-kinderdb] Description = MySQL kinderdb access Driver = /usr/lib/odbc/libmyodbc.so Host = 10.10.200.10 Database = kinderdb Port = 3369 ReadOnly = No Trace = Yes TraceFile = /tmp/errc.log [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 Database = saintnonmina Server = 192.168.1.100 Port = 1723 ReadOnly = No TDS_Version = 8.0 Trace = No
Example of \~/.odbc.ini file user wide
User wide
.odbc.ini are located in the /home/ 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:[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
Connecting 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 logged; 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 , the username and password are optional, but in some driver modules such as FreeTDS those are mandatory./bin/bash % isql ejabberd-useronly username secretpassword.123 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>select * from users
ODBC Connection Checks
Due ODBC setups are complex, Driver module Data Source Checks must be made for do not lost in the process, this its general recommended way of for discard any bad made step:
- Valid and resolvable hostname in case of the access its a remote DBMS service.
- Valid port number for the DBMS service to access in case of the access its a remote DBMS service.
- Valid and existing database/file name.
- Valid user name in case of such those like Sybase, PostgreSQL or MySQL/MariaDB.
- Valid password for the specified user in case of such those like Sybase, PostgreSQL or MySQL/MariaDB.
- Server DBMS service must be running in case of such those like Sybase, PostgreSQL or MySQL/MariaDB.
- Server DBMS service must allow remote connections in case of the access its a remote DBMS service.
- Server user/access must be right grated for the current setup connection.
About Devuan/Debian 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.
VenenuX ODBC related module drivers are complete revised and build agains UnixODBC. VenenuX packages cannot be used in others Debian related or derived systems due incompatibilities.
Some Debian packages such like SQLite related can be used in Devuan, due the odbc packages does not are related or close to the internal system or hierarchy, neither cause
systemd/sysvinit conflicts.Deb's ODBC managers Packages
There are the list of the current ODBC packages software, with some Debian reference search:
| ODBC software | status | Devuan package names | Debian reference search |
|---|---|---|---|
| iODBC | good | libiodbc2, libiodbc2-dev | https://packages.debian.org/source/sid/libiodbc2 |
| UnixODBC | madure | libodbc1,odbcinst,unixodbc,unixodbc-dev | https://packages.debian.org/source/sid/unixodbc |
There are the list of Devuan GUI ODBC managers, with some Debian reference search:
| GUI software | status | Devuan package names | Debian reference search |
|---|---|---|---|
| QtGUI | good | unixodbc-gui-qt | https://packages.debian.org/source/sid/unixodbc-gui-qt |
| GTKGUI | madure | iodbc | https://packages.debian.org/source/sid/iodbc |
Deb's ODBC driver modules packages
All the Devuan or Debian derived packages are build agains UnixODBC, due some does not compile agains iODBC.
There are the list of the current Devuan ODBC driver modules packages, with some Debian reference search:
| driver module | status | Devuan package names | Debian reference search |
|---|---|---|---|
| mysql-connector-odbc | madure | libmyodbc | https://packages.debian.org/source/sid/myodbc |
| postgresql-odbc | madure | odbc-postgresql | https://packages.debian.org/source/sid/psqlodbc |
| tds-odbc freetds | good | tdsodbc | https://packages.debian.org/source/sid/freetds |
| mdbtools-odbc | limited | libmdbodbc1 | https://packages.debian.org/source/sid/mdbtools |
There are the list of some extra debian ODBC driver modules packages, with some Debian reference search:
| driver module | status | Devuan package names | Debian reference search |
|---|---|---|---|
| sqliteodbc | madure | libsqlite3-mod-csvtable,libsqliteodbc | https://packages.debian.org/source/sid/sqliteodbc |
IMPORTANT NOTES:
- the sqlite odbc related package are not showed in Devuan repository but compatible
- the libmyodbc odbc related package works same for MariaDB or MySQL DBMS
- the freetds odbc related package are out of date and have lack of nvarchar, nchar and ntext right support
Debian and Devuan UnixODBC how to's
All the ODBC Devuan packages are compatible and same as those on Debian, is due there's no systemd depends or any system differences between packages, inclusively 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
All the steps here are using command line tool, the GUI parts make the same, but its less controlled, specially the iODBC GTK gui that always make a general “ODBC” hader section on the ini files.
(Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
(Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
2) install and prepare sqlite odbc
First install the odbc driver module, unfortunately by some strange reason the SQLite ODBC packages are not in default in first Devuan repositories for some reason does not auto mirror, so until Devuan administrator fixed or include it must setup and added a Debian repository temporally and then later remove it for install respective missing packages.
apt-get install unixodbc odbcinst odbcinst1debian2
While the Debian repository are temporally active, install the missing 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 and SQLite related.
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 module driver reference for usage was
SQLite3and that string are used in second line of theodbc.iniDSN definition, specificaly in theDriverparameter, must be same and are case senditive. - The Database name must be reference as absolute path in the
odbc.iniDSN definition, specificaly in theDatabaseparameter, must be exact as do a pwd + file (/tmp/workingdb/+testdb.sqlite) and are case sensitive. - The
isqlcommand line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name areodbcsqliteand its defined at theodbc.inisection of the DSN overall for specific database. - (Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
3) install and prepare postgres odbc
The PostgreSQL ODBC connector in Devuan are derived from Debian and are stable and up to date. The PostgreSQL ODBC connector was a MS only project to gran access to linux PostgreSQL database to win32 like systems.
PostgreSQL have two flavors: PostgreSQL Unicode is a Unicode enabled driver that will work well with huge range of languages such databases encoded as 'UTF-8'. PostgreSQL ANSI is an ANSI driver which is also able to handle some multibyte character sets such as EUC_JP, BIG5 and Shift-JIS, used with databases encoded using any of the LATIN character sets.
First install the odbc driver module and the postgresql minimal packages, unfortunately the postgresql installation are to complex to cover in this document so sill assumed defaul instalation with minimal setup:
apt-get install unixodbc odbcinst odbcinst1debian2 odbc-postgresql
Of course if use optionally a local server as we do here for practice (the “postgresql” package), the setup of PostgreSQL are out of scope, so we will use the default
postgres build in user and scheme to connect it.apt-get install postgresql
If all are good, the console installation will show the usage count of registered driver module for each postgresql odbc flavor:
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 two driver modules are registered as
PostgreSQL ANSI and PostgreSQL Unicode, due practical reasons now we can optionally create a PostgreSQL test table in the remote target DBMS (local if choose optionally the “postgresql” in install step), for this example we use default build-in scheme database postgres, created a root user and a table named “tabla1” with one column “col1” and then type /q to exit:su -s /bin/bash postgres psql -U root -d postgres -h 127.0.0.1 psql (9.4.12) Type "help" for help. postgres=# CREATE TABLE tabla1 (col1 TEXT); CREATE TABLE postgres=# CREATE ROLE root PASSWORD 'toor' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN; CREATE ROLE postgres=# \q
Create a ODBC DSN access entry point in the system wide
odbc.ini file for the postgres scheme database access:[odbcpgsql] Driver = PostgreSQL Unicode Description = PostgreSQL UTF8 odbc access to postgres db Database = postgres ServerName = 127.0.0.1 Port = 5432 User = root Password = toor ReadOnly = Yes RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings =
Here was only common minimal. The complete options for PostgreSQL ODBC must be reference: (https://odbc.postgresql.org/docs/config-opt.html) Take in consideration that the only common and same options in all odbc driver modules are the Driver parameter and the Database parameter, by example here ServerName are used and valid for PostgreSQL ODBC, but not for FreeTDS ODBC.
Now lest try to test the odbc DSN access with
isql command line tool:isql odbcpgsql 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 and setup a root user in the server DBMS, that choose optionally the “postgresql” package in install command step.
IMPORTANT CONSIDERATIONS
- The PostgreSQL ODBC module driver reference usage was
PostgreSQL Unicodea UTF-8 specific and that string are used in second line of theodbc.iniDSN definition section, specifically in theDriverparameter. - The Database name must be reference in the
odbc.inialways, specificaly in theDatabaseparameter, we use the default internalpostgresscheme and are case sensitive. - The
ServerNameparameter 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 useServernamefor all ODBC driver modules, by example the FreeTDS does not have same behaviour. - The
isqlcommand line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name areodbcpgsqland its define at theodbc.inisection of the DSN overall for specific database type. - (Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
4) install and prepare mariadb/mysql odbc
The MySQL connector in Devuan are derived from Debian and are very stable but out of date, also more important, MariaDB and MySQL has own connector driver modules, but the provided in Debian or Devuan repositories work same for both DBMS.
First install the odbc driver module and the mysql/mariadb minimal packages, unfortunately Devuan or Debian package not will register the driver module so you must be done manually until Devuan mantainers made it on 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 Devuan/Debian 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 scheme 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 mysql scheme database access:[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 =
Here was only common minimal. The complete options for MySQL ODBC connector must be reference: (https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-config-options)
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” package in install command step.
IMPORTANT CONSIDERATIONS
- The MySQL/MariaDB module driver reference for usage was
MySQLand that string are used in second line of theodbc.iniDSN definition, specifically in theDriverparameter, must be same and are case sensitive. - The Database name must be reference in the
odbc.inialways, specifically in theDatabaseparameter, we use the default internalmysqlscheme and are case sensitive. - The
Serverparameter 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 in a Mysql ODBC DSN definition. - The
isqlcommand line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name areodbcmysqland its define at theodbc.inisection of the DSN overall for specific database type. - (Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
5) install and prepare freetds odbc
Freetds Debian/Devuan package are very out of date, but enough stable and working, FreeTDS up to date added corrected 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 conversion size.
First install the odbc driver module and the FreeTDS minimal packages, unfortunately Devuan or Debian package not will register the driver module so you must be done manually until Devuan mantainers made it on the package. The VenenuX package do the registration of driver module automatically. We here use a Sybase as target DBMS but both MSSQL and Sybase are supported.
apt-get install freetds-bin tdsodbc
The MSSQL or Sybase related are out of scope of this document, but a special case must be take in when choose the type of the DBMS server to connect to, due TDS protocol must be defined: (http://www.freetds.org/userguide/choosingtdsprotocol.htm), by default FreeTDS has experimental support for iteratively trying protocol connections until it finds one the server accepts. IF doub use the 5.0 for Sybase DBMS servers and 7.1 for MsSQL DBMS servers.
If all are good, and due the package not will register a driver module now we must registered the ODBC FreeTDS module driver 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/tdsodbc/odbcinst.ini odbcinst: Driver installed. Usage count increased to 1. Target directory is /etc
Now a driver manager are registered as
FreeTDS, create a Sybase/MsSQL test table in the remote target DBMS (there's native support for Sybase Linux since version 12), for this example we use default demodb file database demodb, the table will named “tabla1” with one column “col1” and then exit from sybase command line, the scope of that are out of this documentation, you can install the VenenuX anywhere package for that, note that isql tool are also provided by Sybase, the invocation can be done as:syisql -Sdemodb -Udba -Psql
Create a ODBC DSN access entry point in the system wide
odbc.ini file for the mysql scheme database access:[odbcsybase] Driver = FreeTDS Description = FreeTDS odbc access to sybase or mssql db Database = demodb Server = 127.0.0.1 Port = 2638 User = dba Password = sql TDS_Version = 5.0
Here was only minimal most important, here we including the
TDS_Version parameter that must be always 5.0 for Sybase. The complete options for FreeTDS ODBC must be reference: (http://www.freetds.org/userguide/odbcconnattr.htm)
Now lest try to test the odbc DSN access with
isql command line tool:isql odbcsybase dba sql -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select count(*) from tabla1 +-----------+ | count(*) | +-----------+ | 0 | +-----------+ SQLRowCount returns 1 1 rows fetched
NOTE IMPORTANT FreeTDS ODBC has severall connection parameters that must be refered to configure: (http://www.freetds.org/userguide/odbcconnattr.htm).
IMPORTANT CONSIDERATIONS
- The FreeTDS module driver reference for usage was
FreeTDSand that string are used in second line of theodbc.iniDSN definition, specifically in theDriverparameter, must be same and are case sensitive. - The Database name must be reference in the
odbc.inialways, specifically in theDatabaseparameter, we use the default internaldemodbscheme with defaultdbauser. - The
Serverparameter 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 expected behaviour of ServerName its to combine with the FreeTDS specific settings and not usefull, confuses the odbc driver module behaviour. - The
isqlcommand line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name areodbcsybaseand its define at theodbc.inisection of the DSN overall for specific database type. - (Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
6) install and prepare mdbtools odbc
The MDBTools project provide and ODBC driver module.. but are poor documented and Devuan package does not offer any improvement over documentation.. currently supports
mdb, accdb and mdt file types and SQL support are very basic and limited.
First install the odbc driver module and the mdbtools manager packages, to provide the odbc driver module library:
apt-get install unixodbc odbcinst odbcinst1debian2 libmdbodbc1 mdbtools
If all are good, will registered automatically the MDBTools driver module for usage, console does not will show any output, due driver module are registered silently, and re-registering will show that count increase raised to 2 as:
odbcinst: Driver installed. Usage count increased to 2. Target directory is /etc
There's no way to create mdb files for linux, so we must have a mdb test file for that, we assumed have a mdb/accdb file prepared with a table named
table1 with at least one column named columna1 of type TEXT inside, localled at the /tmp/workingdb directory, the mdb-tool from mdbtools package can be used for testing:mdb-sql /tmp/workingdb/testdb.mdb 1 => SELECT * FROM tabla1 2 => go +--------------------------------------------------------------------------+ |columna1 | +--------------------------------------------------------------------------+ |asdasd | +--------------------------------------------------------------------------+ 1 Row retrieved 1 => quit
Create a ODBC DSN access entry point named
odbcsmdb in the system wide odbc.ini file for the testdb.mdb database file:[odbcsmdb] Description=MDB limited odbc access to testdb.mdb Driver = MDBTools Database =/tmp/workingdb/testdb.mdb
Now lest try to test the odbc DSN access with
isql command line tool:isql odbcmdb -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from tabla1 +--------------------------------------------------------------------------+ |columna1 | +--------------------------------------------------------------------------+ |asdasd | +--------------------------------------------------------------------------+ SQLRowCount returns 1 1 rows fetched SQL> quit
NOTE on MDBTools library the supported SQL syntax is a very limited subset and deficient in several ways.
IMPORTANT CONSIDERATIONS
- The MDB module driver reference for usage was
MDBToolsand that string are used in second line of theodbc.iniDSN definition, specificaly in theDriverparameter, must be same and are case senditive. - The Database name must be reference as absolute path in the odbc.ini DSN definition, specificaly in the
Databaseparameter, must be exact as do a pwd + file (/tmp/workingdb/+testdb.mdb) and are case sensitive. - The
isqlcommand line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name areodbcmdband its define at theodbc.inisection of the DSN overall for specific database. - (Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
Previous version of this document started by me in the "friend of devuan" wiki site.
Except where otherwise noted, content on this is licensed under the following license: CC Attribution-Share Alike 3.0 Unported- Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
- Back to top index here Overall ODBC )
