Veja como conectar-se usando ODBC aos bancos de dados MariaDB e .mdb no Debian 8.6 (Jessie-stable) usando o gerenciador unixODBC.
Primeiro carregue algumas coisas:
- load package: mariadb-server-10.1 (isso funciona com o MySQL 5.6)
- load package: unixodbc 2.3.1.3 (unixODBC 2.3.0-4)
-
biblioteca baixada do MariaDB.com: mariadb-connector-odbc-2.0.12-ga-linux-x86_64
-
você tem que obter isso diretamente do MariaDB.com como o Debian Jessie ainda não tem um pacote para isso.
-
Em seguida: mova o arquivo libmaodbc.so descompactado para /usr/lib/x86_64-linux-gnu/libmaodbc.so
-
-
opcionalmente carrega o pacote unixodbc-bin 2.3.0-4 (as ferramentas gráficas para gerenciamento e navegação de ODBC)
(Nota: Eu não tentei fazer com que o gerenciador iODBC funcionasse, mas ele é semelhante.)
O unixODBC tem dois arquivos de configuração /etc/odbcinst.ini e /etc/odbc.ini . Eles podem ser editados diretamente ou configurados com a ferramenta GUI. A ferramenta GUI não configurará os DNSs se os parâmetros do driver não estiverem corretos. Você receberá algo como uma mensagem de erro de parâmetro.
/etc/odbcinst.ini configura as informações do driver ODBC (o arquivo lib do driver ODBC) e Em seguida, /etc/odbc.ini se refere a essas informações de driver para configurar o DSN ou o nome do servidor de dados (o banco de dados que você acessará).
Além disso, é necessário haver dois drivers referenciados em /etc/odbcinst.ini, o driver odbc principal (ou Driver = ..) e também o driver "Setup" (ou Setup = ..). Parece que o driver de configuração é um auxiliar com o editor da GUI e configura os parâmetros padrão (e, possivelmente, os verifica). Mas a distribuição Maria ODBC só lhe dá o primeiro desses drivers. Descobri que usar o driver de instalação do mysql funciona para o segundo driver. Observe que os drivers têm um 'S' neles, por exemplo /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so. Você encontrará os drivers disponíveis listados no synaptic ao selecionar um pacote, depois clique em propriedades e na guia Arquivos instalados.
Aqui está o meu arquivo /etc/odbcinst.ini com comentários. (Abaixo está meu arquivo /etc/odbc.ini.)
Observe que os drivers parecem exigir um nome de caminho completo. Eu também incluí os parâmetros de configuração para mdbtools (para acessar arquivos MS Access * .mdb usando ODBC). Observe também que a senha e o nome de caminho / nome do banco de dados mdb não parecem precisar de aspas em torno deles. Eles tomam espaços e outros caracteres especiais muito bem. (Exceto por talvez "=")?
Também incluí opções comentadas que aparecem magicamente no editor da GUI abaixo para referência futura.
[mariadb]
Description=Maria DB
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmaodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
#The rest of these magically appear in the GUI driver (advanced) setup utility:
#Driver64=
#Setup64=
UsageCount=1
#CPTimeout=
#CPTImeToLive=
#DisableGetFunctions=
#DontDLCLose=
#ExFetchMapping=
#Threading=
#FakeUnicode=
#IconvEncoding=
#Trace=
#TraceFile=
#=================
[mdbtools]
Description=MDBTools Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
#The rest of these magicaly appear in the GUI driver (Advanced) setup utility:
#Driver64=
#Setup64= Get error if include this param for mdbtools
# (even though it appears in the GUI list as null)
UsageCount=1
#CPTimeout=
#CPTImeToLive=
#DisableGetFunctions=
#DontDLCLose=
#ExFetchMapping=
#Threading=
#FakeUnicode=
#IconvEncoding=
#Trace=
#TraceFile=
#TraceLibrary=
/etc/odbc.ini (note que o socket precisa ser alterado abaixo para apontar para o socket padrão que o MariaDB expõe no Debian):
[mariadb-bb]
#Description - defaults to $Driver
Description=Bigbase
#Driver: Required. The name of the MariaDB ODBC Driver. Must be always {MariaDB ODBC 1.0 Driver}
# the 1.0 comment above is possibly outdated
Driver=mariadb
#Server: name or IP of the MariaDB database server. Aliases: Servername - defaults to null
Server=localhost
#Database: default database. Aliases: DB - defaults to 'test'
Database=somedatabase
#Port: TCP/IP Port of the database server - defaults to null
Port=3306
#When using Server=localhost we will be connecting thru the socket exposed by mariadb.
#You can find this socket file location&name in /etc/mysql/my.ini - defaults to null if missing
Socket=/var/run/mysqld/mysqld.sock
#Option: For MySQL Connector/ODBC compatibility. Aliases: Options - defaults to null
#(not sure what this is used for?)
#Option=
#Stmt - defaults to null (not sure what this is used for?)
#Stmt=
#---Optional----------------:
#DSN: Name of the DSN (not sure what this is used for?)
#DSN=
#User: user name for database authentication. Aliases: UID,
User=root
#Password: password for database authentication. Aliases: PWD
Password=bigdog
#=========
[PW]
Description=mdbtools
#Driver needs to point to a driver in /etc/odbcinst.ini
Driver=mdbtools
Database=/home/user/Shared/Documents/test.mdb
Você pode testar seu ODBC com as duas ferramentas a seguir:
/usr/bin/isql - Utility to submit SQL queries to a data source.
/usr/bin/iusql - Unicode version of isql.
Observação: você pode enviar um comando sql como entrada padrão, por exemplo, echo "select * from tableA" | isql ou solicitará linhas sql com sql> quando executado. Veja man isql . Se você der opções de isql como, por exemplo, sua senha, ela substituirá a senha em seu arquivo /etc/odbc.ini.
Espero que isso ajude alguém.
Finalmente, aqui estão as anotações sobre quais arquivos estão em quais pacotes relacionados:
* unixodbc:
unixodbc-bin 2.3.0-4 - Graphical tools for ODBC management and browsing
/usr/bin/ODBCManageDataSourcesQ4 - 1&2 ODBC Administrator
/usr/bin/ODBCCreateDataSourceQ4 - 2 create Data Source Name wizard
These are mentioned in the README file but are not in the package:
/usr/bin/odbcinstQ4 - a Qt based installer library/plugin (don't have)
/usr/bin/ODBCTestQ4 - an interactive test program (don't have)
/usr/bin/ODBCDataManagerQ4 - Data Manager (not done)
/usr/share/doc/unixodbc-bin/README
/usr/share/menu/unixodbc-bin - some sort of menu data file
Notes: Only works if both main driver and setup drivers exist.
unixodbc 2.3.1-3 - Basic ODBC tools
/usr/bin/isql - Utility to submit SQL queries to a data source.
/usr/bin/iusql - Unicode version of isql.
notes: Can either pipe standard input into them, e.g. echo "select * from m" | isql ...
or it will prompt for the sql> when run.
/usr/lib/odbc - the lib
/usr/share/doc/unixodbc/README
/usr/share/man/man1/isql.1.gz
/usr/share/man/man1/iusql.1.gz
libodbc1 2.3.1-3 ODBC library for Unix
/usr/lib/x86_64-linux-gnu/libodbc.so.1 -> libodbc.so.2
/usr/lib/x86_64-linux-gnu/libodbc.so.2 -> libodbc.so.2.0.0
/usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0 - lib odbc
/usr/lib/x86_64-linux-gnu/libodbccr.so.1 -> libodbccr.so.2
/usr/lib/x86_64-linux-gnu/libodbccr.so.2 -> libodbccr.so.2.0.0
/usr/lib/x86_64-linux-gnu/libodbccr.so.2.0.0 - lib odbc cr ?? cr, but I think like .rc (setup?)
/usr/lib/x86_64-linux-gnu/odbc/libnn.so - lib nn
/usr/share/lintian/overrides/libodbc1 - ?
-----------------------------------------------------------------------------------------
* myodbc:
libmyodbc 5.1.10-3 the MySQL ODBC driver
/usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so - the mysql driver
/usr/share/libmyodbc/odbcinst.ini - table of drivers
/usr/share/doc/libmyodbc/examples/odbc.ini - Example table of data sources
/usr/share/doc/libmyodbc/README.Debian - note about not building a -dev package
-----------------------------------------------------------------------------------------
* odbcinst:
libodbcinstq4-1 2.3.0-4 Qt-based ODBC configuration library
/usr/lib/x86_64-linux-gnu/libodbcinstQ4.so.1 -> libodbcinstQ4.so.1.1.1
/usr/lib/x86_64-linux-gnu/libodbcinstQ4.so.1.1.1
odbcinst 2.3.1-3 Helper program for accessing odbc ini files
/etc/odbc.ini - Setup table for list of DSN's. Each one points to a driver in /etc/odbcinst.ini
notes: don't need to quote special characters in file names like spaces or parens.
/usr/bin/odbcinst - - command line tool for batch ODBC configuration
run /usr/bin/odbinst or 'man odbcinst' for usage, e.g. syntax and options
/usr/share/man/man1/odbcinst.1.gz
odbcinst1debian2 2.3.1-3 Support library for accessing odbc ini files
/etc/ODBCDataSources - etc
/usr/lib/x86_64-linux-gnu/libodbcinst.so.1 -> libodbcinst.so.2
/usr/lib/x86_64-linux-gnu/libodbcinst.so.2 -> libodbcinst.so.2.0.0
/usr/lib/x86_64-linux-gnu/libodbcinst.so.2.0.0 - inst
/usr/lib/x86_64-linux-gnu/odbc/libesoobS.so - setup driver
/usr/lib/x86_64-linux-gnu/odbc/libmimerS.so - setup driver
/usr/lib/x86_64-linux-gnu/odbc/libodbcdrvcfg1S.so - setup driver - drvcfg1
/usr/lib/x86_64-linux-gnu/odbc/libodbcdrvcfg2S.so - setup driver - drvcfg2
/usr/lib/x86_64-linux-gnu/odbc/libodbcminiS.so - setup driver - mini
/usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so - setup driver - my (mysql)
/usr/lib/x86_64-linux-gnu/odbc/libodbcnnS.so - setup driver - nn
/usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so - setup driver - psql
/usr/lib/x86_64-linux-gnu/odbc/libodbctxtS.so - setup driver - txt
/usr/lib/x86_64-linux-gnu/odbc/liboplodbcS.so - setup driver - opl odbc
/usr/lib/x86_64-linux-gnu/odbc/liboraodbcS.so - setup driver - ora odbc
j /usr/lib/x86_64-linux-gnu/odbc/libsapdbS.so - setup driver - sap db
/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so - setup driver - tds
/usr/share/lintian/overrides/odbcinst1debian2 - inst1 debian2
-----------------------------------------------------------------------------------------
* MDB:
odbc-mdbtools 0.7.1-2 MDB tools ODBC driver
/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so - driver
/usr/lib/x86_64-linux-gnu/odbc/libmdbodbcW.so - ?
mdbtools 0.7.1-2 JET / MS Access database (MDB) tools
/etc/bash_completion.d/mdbtools - bash command completion extension
/usr/bin/mdb-array - Export data in an MDB database table to a C array
/usr/bin/mdb-export - Export data in an MDB database table to CSV format
/usr/bin/mdb-header - dump the names and types of the tables and columns
/usr/bin/mdb-hexdump - Hexdump utility from MDB Tools
/usr/bin/mdb-parsecsv - Convert CSV table dump into C file
/usr/bin/mdb-prop - Get properties list from MDB database
/usr/bin/mdb-schema - produces DDL (data definition language) output
/usr/bin/mdb-sql - allows querying MDB database using a limited SQL subset
/usr/bin/mdb-tables - Get listing of tables in an MDB database
/usr/bin/mdb-ver - Return the format of a given MDB database.
/usr/share/man/man1/mdb-array.1.gz
/usr/share/man/man1/mdb-export.1.gz
/usr/share/man/man1/mdb-header.1.gz
/usr/share/man/man1/mdb-hexdump.1.gz
/usr/share/man/man1/mdb-parsecsv.1.gz
/usr/share/man/man1/mdb-prop.1.gz
/usr/share/man/man1/mdb-schema.1.gz
/usr/share/man/man1/mdb-sql.1.gz
/usr/share/man/man1/mdb-tables.1.gz
/usr/share/man/man1/mdb-ver.1.gz
mdbtools-doc
/usr/share/doc-base/mdbtools-faq
/usr/share/doc-base/mdbtools-install
/usr/share/doc/mdbtools-doc/HACKING.gz
/usr/share/doc/mdbtools-doc/NEWS.gz
/usr/share/doc/mdbtools-doc/README.gz
/usr/share/doc/mdbtools-doc/TODO
/usr/share/doc/mdbtools-doc/faq.html
/usr/share/doc/mdbtools-doc/html/index.html - A Guide to Installing and Configuring MDB Tools
/usr/share/doc/mdbtools-doc/html/a304.htm
/usr/share/doc/mdbtools-doc/html/book1.htm
/usr/share/doc/mdbtools-doc/html/c29.htm
/usr/share/doc/mdbtools-doc/html/c42.htm
/usr/share/doc/mdbtools-doc/html/c98.htm
/usr/share/doc/mdbtools-doc/html/c180.htm
/usr/share/doc/mdbtools-doc/html/c202.htm
/usr/share/doc/mdbtools-doc/html/c260.htm
/usr/share/doc/mdbtools-doc/html/f19.htm
/usr/share/doc/mdbtools-doc/html/x53.htm
/usr/share/doc/mdbtools-doc/html/x86.htm
/usr/share/doc/mdbtools-doc/html/x90.htm
/usr/share/doc/mdbtools-doc/html/x94.htm
/usr/share/doc/mdbtools-doc/html/x196.htm
/usr/share/doc/mdbtools-doc/html/x199.htm
/usr/share/doc/mdbtools-doc/html/x231.htm
/usr/share/doc/mdbtools-doc/html/x244.htm
/usr/share/doc/mdbtools-doc/html/x251.htm
/usr/share/doc/mdbtools-doc/html/x276.htm
/usr/share/doc/mdbtools-doc/html/x289.htm
/usr/share/doc/mdbtools-doc/html/x314.htm
/usr/share/doc/mdbtools-doc/html/x324.htm
/usr/share/doc/mdbtools-doc/html/x328.htm
/usr/share/doc/mdbtools-doc/html/x334.htm
/usr/share/doc/mdbtools-doc/html/x370.htm
/usr/share/doc/mdbtools-doc/html/x375.htm
/usr/share/doc/mdbtools-doc/html/x379.htm
/usr/share/doc/mdbtools-doc/html/x383.htm
/usr/share/doc/mdbtools-doc/html/x386.htm
/usr/share/doc/mdbtools-doc/html/x389.htm
/usr/share/doc/mdbtools-doc/html/x394.htm
/usr/share/doc/mdbtools-doc/install.sgml.gz
mdbtools-gmdb 0.7.1-2 - JET / MS Access database (MDB) file viewer
/etc/bash_completion.d/mdbtools-gmdb - bash command completion extension
/usr/bin/gmdb2 - THE application (graphical interface)
/usr/share/applications/mdbtools-gmdb.desktop - setup?
/usr/share/gmdb/glade/gmdb-debug.glade - graphical
/usr/share/gmdb/glade/gmdb-export.glade
/usr/share/gmdb/glade/gmdb-prefs.glade
/usr/share/gmdb/glade/gmdb-props.glade
/usr/share/gmdb/glade/gmdb-schema.glade
/usr/share/gmdb/glade/gmdb-sql.glade
/usr/share/gmdb/glade/gmdb-tabledef.glade
/usr/share/gmdb/glade/gmdb.glade
/usr/share/gmdb/glade/code.xpm - images
/usr/share/gmdb/glade/debug.xpm
/usr/share/gmdb/glade/form_big.xpm
/usr/share/gmdb/glade/forms.xpm
/usr/share/gmdb/glade/logo.xpm
/usr/share/gmdb/glade/macro_big.xpm
/usr/share/gmdb/glade/macros.xpm
/usr/share/gmdb/glade/module_big.xpm
/usr/share/gmdb/glade/pk.xpm
/usr/share/gmdb/glade/query.xpm
/usr/share/gmdb/glade/query_big.xpm
/usr/share/gmdb/glade/report_big.xpm
/usr/share/gmdb/glade/reports.xpm
/usr/share/gmdb/glade/table.xpm
/usr/share/gmdb/glade/table_big.xpm
/usr/share/pixmaps/gmdb2.xpm
/usr/share/gmdb/glade/stock_export-16.png
/usr/share/gmdb/glade/stock_export.png
/usr/share/gnome/help/gmdb/C/figures/gmdb2_sql_window.png
/usr/share/gnome/help/gmdb/C/figures/gmdb2_window.png
/usr/share/gnome/help/gmdb/C/gmdb.xml
/usr/share/gnome/help/gmdb/C/legal.xml
/usr/share/man/man1/gmdb2.1.gz - GNOME UI for MDB Tools
/usr/share/menu/mdbtools-gmdb
-----------------------------------------------------------------------------------------
* mariadb-connect-engine=10.1
/etc/mysql/conf.d/connect.cnf
/usr/lib/mysql/plugin/ha_connect.so
* mariadb-connector-odbc-2.0.12-ga-linux-x86_64
/lib/libmaodbc.so -- moved this to: /usr/lib/x86_64-linux-gnu/libmaodbc.so
/share/doc/mariadb_connector_odbc/README
-----------------------------------------------------------------------------------------
* iodbc:
libiodbc2 3.52.9-2 iODBC Driver Manager
iodbc 3.52.9-2 GTK+ config frontend for the iODBC Driver Manager
/usr/bin/iodbcadm-gtk - graphical ODBC administration program using the GTK+ widget set.
/usr/bin/iodbctest - iODBC Demonstration programs
/usr/lib/x86_64-linux-gnu/libdrvproxy.so.2 - a link? lib drv proxy so
/usr/lib/x86_64-linux-gnu/libdrvproxy.so.2.1.20
/usr/lib/x86_64-linux-gnu/libiodbcadm.so.2 - a link? lib iodb adm
/usr/lib/x86_64-linux-gnu/libiodbcadm.so.2.1.20
/usr/share/doc/iodbc/README.Debian - not much help
/usr/share/lintian/overrides/iodbc
/usr/share/man/man1/iodbcadm-gtk.1.gz - graphical ODBC administration program
/usr/share/man/man1/iodbctest.1.gz - iODBC Demonstration programsq