ODBC Devuan and Debian complete how to and docu - McKAY brothers, multimedia emulation and support

About McKAY's blog

ads

Post Top Ad

Your Ad Spot

2017/06/05

ODBC Devuan and Debian complete how to and docu

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 )
FeatureUnixODBCiODBC
Unicode supportSQLWCHARs as 2 bytes UCS-2wchar_t UCS-2, UTF-8, UCS-4
cursor libraryyesyes (today)
thread safetyyesyes
specification2.0, 3.0, 3.5, 3.82.0, 3.0, 3.5
32 vs 64 bit supeither 32-bit or 64-bit SQLLEN/SQLULEN types(3.5+) 64-bit SQLLEN/SQLULEN types
gui managerODBCManageDataSourcesQ4iodbcadm-gtk
application widelyalmost allfew and included in MacOS
system operatin widelyalmost allsome distributions does not offer
LicensingGPL+LGPLBSD+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:
  1. Valid and resolvable hostname in case of the access its a remote DBMS service.
  2. Valid port number for the DBMS service to access in case of the access its a remote DBMS service.
  3. Valid and existing database/file name.
  4. Valid user name in case of such those like Sybase, PostgreSQL or MySQL/MariaDB.
  5. Valid password for the specified user in case of such those like Sybase, PostgreSQL or MySQL/MariaDB.
  6. Server DBMS service must be running in case of such those like Sybase, PostgreSQL or MySQL/MariaDB.
  7. Server DBMS service must allow remote connections in case of the access its a remote DBMS service.
  8. Server user/access must be right grated for the current setup connection.
(Back to top index here Overall ODBC )

About Devuan/Debian ODBC packages

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 softwarestatusDevuan package namesDebian reference search
iODBCgoodlibiodbc2, libiodbc2-devhttps://packages.debian.org/source/sid/libiodbc2
UnixODBCmadurelibodbc1,odbcinst,unixodbc,unixodbc-devhttps://packages.debian.org/source/sid/unixodbc
There are the list of Devuan GUI ODBC managers, with some Debian reference search:
GUI softwarestatusDevuan package namesDebian reference search
QtGUIgoodunixodbc-gui-qthttps://packages.debian.org/source/sid/unixodbc-gui-qt
GTKGUImadureiodbchttps://packages.debian.org/source/sid/iodbc
(Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )

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 modulestatusDevuan package namesDebian reference search
mysql-connector-odbcmadurelibmyodbchttps://packages.debian.org/source/sid/myodbc
postgresql-odbcmadureodbc-postgresqlhttps://packages.debian.org/source/sid/psqlodbc
tds-odbc freetdsgoodtdsodbchttps://packages.debian.org/source/sid/freetds
mdbtools-odbclimitedlibmdbodbc1https://packages.debian.org/source/sid/mdbtools
(Back to Debian/Devuan ODBC index here About Debian and Devuan ODBC packages )
There are the list of some extra debian ODBC driver modules packages, with some Debian reference search:
driver modulestatusDevuan package namesDebian reference search
sqliteodbcmadurelibsqlite3-mod-csvtable,libsqliteodbchttps://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
(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       = 
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 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 nvarcharnchar 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 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 )

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 mdbaccdb 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


No hay comentarios.:

Publicar un comentario

no stupid winbuntu users allowed!

Entradas populares

Post Top Ad

Your Ad Spot