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.
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.
(Back to top index here Overall ODBC )
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:
There are the list of Devuan
GUI ODBC managers, with some Debian reference search:
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:
There are the list of some extra debian ODBC driver modules packages, with some Debian reference search:
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
(Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
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 )
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 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 senditive.
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 its defined at the odbc.ini section 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 Unicode a UTF-8 specific and that string are used in second line of the odbc.ini DSN definition section, specifically in the Driver parameter.
The Database name must be reference in the odbc.ini always, specificaly in the Database parameter, we use the default internal postgres scheme and are case sensitive.
The ServerName 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 for all ODBC driver modules, by example the FreeTDS does not have same behaviour.
The isql command line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name are odbcpgsql and its define at the odbc.ini section 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 =
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 MySQL and that string are used in second line of the odbc.ini DSN definition, specifically in the Driver parameter, must be same and are case sensitive.
The Database name must be reference in the odbc.ini always, specifically in the Database parameter, we use the default internal mysql scheme 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 in a Mysql ODBC DSN definition.
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 its define at the odbc.ini section 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
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
IMPORTANT CONSIDERATIONS
The FreeTDS module driver reference for usage was FreeTDS and that string are used in second line of the odbc.ini DSN definition, specifically in the Driver parameter, must be same and are case sensitive.
The Database name must be reference in the odbc.ini always, specifically in the Database parameter, we use the default internal demodb scheme with default dba user.
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 expected behaviour of ServerName its to combine with the FreeTDS specific settings and not usefull, confuses the odbc driver module behaviour.
The isql command line invocation its just only for test or DSN odbc access using the DSN odbc name, the ODBC DSN name are odbcsybase and its define at the odbc.ini section of the DSN overall for specific database type.
- (Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
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 MDBTools 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 senditive.
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.mdb) 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 odbcmdband its define at the odbc.ini section 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
