search

an3l / my_playground

My playground with python, nginx, docker and server!
0 stars 0 forks source link

ODBC driver with mariadb-odbc-connector and Librebase #24

Open an3l opened 3 years ago

an3l commented 3 years ago

Installing ODBC (Open Database Connector) driver

Links

[1] https://mariadb.com/kb/en/about-mariadb-connector-odbc/ [2] https://askubuntu.com/questions/167491/connecting-ms-sql-using-freetds-and-unixodbc-isql-no-default-driver-specified [3]]https://help.interfaceware.com/kb/904 [4] https://unix.stackexchange.com/questions/313858/connect-to-mariadb-mysql-and-ms-access-mdb-databases-via-localhost-using-unixo/313859#313859

$ mkdir odbc_package
$cd odbc_package
$ wget https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.7/mariadb-connector-odbc-3.1.7-ga-debian-x86_64.tar.gz
$ tar -xvzf mariadb-connector-odbc-3.1.7-ga-debian-x86_64.tar.gz_
lib/mariadb/plugin/auth_gssapi_client.so
lib/mariadb/plugin/caching_sha2_password.so
lib/mariadb/plugin/client_ed25519.so
lib/mariadb/plugin/dialog.so
lib/mariadb/plugin/mysql_clear_password.so
lib/mariadb/plugin/sha256_password.so
share/doc/mariadb-connector-odbc/COPYING
share/doc/mariadb-connector-odbc/README
lib/libmaodbc.so
$ ls -la lib/
total 2256
drwxr-xr-x 3 anel anel    4096 Dec 10 02:05 .
drwxr-xr-x 4 anel anel    4096 Dec 10 02:05 ..
-rw-r--r-- 1 anel anel 2295400 Apr  7  2020 libmaodbc.so
drwxr-xr-x 3 anel anel    4096 Dec 10 02:05 mariadb
$ls -la lib/mariadb/plugin/
total 580
drwxr-xr-x 2 anel anel   4096 Dec 10 02:05 .
drwxr-xr-x 3 anel anel   4096 Dec 10 02:05 ..
-rw-r--r-- 1 anel anel  59816 Apr  7  2020 auth_gssapi_client.so
-rw-r--r-- 1 anel anel  55088 Apr  7  2020 caching_sha2_password.so
-rw-r--r-- 1 anel anel 335096 Apr  7  2020 client_ed25519.so
-rw-r--r-- 1 anel anel  42744 Apr  7  2020 dialog.so
-rw-r--r-- 1 anel anel  32880 Apr  7  2020 mysql_clear_password.so
-rw-r--r-- 1 anel anel  45296 Apr  7  2020 sha256_password.so

Packages

unix-dev and odbcinst packages.

unixodbc-dev package:

$ dpkg -s unixodbc-dev
ODBC libraries for UNIX (development files)
 This package contains the development files (headers and libraries) for
 unixODBC, an implementation of the Open DataBase Connectivity interface
 for Unix systems.  You should not need to install this package unless
 you intend to develop C language applications which use ODBC, or to
 compile ODBC-using applications from source.

unixodbc-dev doesn't have man. Need to install unixodbc in order to actually have a odbc driver.

odbcinst package:

odbcinst package: to install driver(odbc) or Data source name (DSN)

$dpkg -s odbcinst
Description: Helper program for accessing odbc ini files
 This package contains the odbcinst helper tool, which allows ODBC driver
 packages to install their own driver settings.

TEMPLATE FILES
       A typical driver template looks like this:
               [MySQL]
               Description     = MySQL driver
               Driver          = /usr/lib/odbc/libmyodbc.so
               Setup           = /usr/lib/odbc/libodbcmyS.so

       A DSN template looks like this:
               [Sample DSN]
               Description         = Test MySQL connection
               Driver              = MySQL
               Trace               = Yes
               TraceFile           = /tmp/odbc.log
               Database            = junk
               Server              = localhost
               Port                = 3306
               Socket              =
$ odbcinst -q -d or odbcinst -q -s
$ odbcinst action object options

       odbcinst  updates  the configuration files that control ODBC access to
       database  servers  on  the  current  host.   It  also   maintains   in
       /etc/odbcinst.ini  a count of the number of references to a particular
       driver, which can be used to determine whether it  should  be  removed
       from the file (only when the reference count drops to 0).

I cannot see ODBCConfig (gui) ^ but I should (it is said to use unixodbc-bin). MariaDB doesn't support this currently.

unixodbc package

$ sudo apt install unixodbc
$ dpkg -L unixodbc
/.
/usr
/usr/bin
/usr/bin/isql  #utility to submit SQL queries to a data source
/usr/bin/iusql #Unicode version of isql.
/usr/lib
/usr/lib/odbc #this
/usr/share
/usr/share/doc
/usr/share/doc/unixodbc
/usr/share/doc/unixodbc/AUTHORS
/usr/share/doc/unixodbc/NEWS.gz
/usr/share/doc/unixodbc/README
/usr/share/doc/unixodbc/copyright
/usr/share/man
/usr/share/man/man1
/usr/share/man/man1/isql.1.gz
/usr/share/doc/unixodbc/NEWS.Debian.gz
/usr/share/doc/unixodbc/changelog.Debian.gz
/usr/share/man/man1/iusql.1.gz

Creating the datasource

Installing the driver ODBC for mariadb:

$ sudo odbcinst -i -d -f MariaDB_odbc_driver_template.ini 
odbcinst: Driver installed. Usage count increased to 1. 
    Target directory is /etc

$ cat /etc/odbcinst.ini 
[MariaDB ODBC 3.1 Driver]
Description=MariaDB Connector/ODBC v.3.1
Driver=/usr/lib64/libmaodbc.so
UsageCount=1

Installing the DSN (Data Source Name):

$ odbcinst -i -s -h -f MariaDB_odbc_data_source_template.ini
$ cat ~/.odbc.ini 
[MariaDB-server]
Description=MariaDB server
Driver=MariaDB ODBC 3.1 Driver
SERVER=MariaDB
USER=anel
PASSWORD=
DATABASE=test
PORT=3306

Connecting with SQLConnect?

$ isql MariaDB-server # Doesn't work \G
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 
SQL> \s
[ISQL]ERROR: Could not SQLPrepare
$ isql MariaDB-server anel -v

Librebase

Start LibreOffice Base (database manager part of the LibreOffice productivity suite) Base is a powerful database manager, part of the LibreOffice productivity suite. It allows you to store, manage and maintain different collections of data. Base makes it easy to keep track of your finances, customers, invoices, or even just the contacts in your address book!

For users that are new to databases, Base offers helpful wizards to create tables, queries, forms and reports. It's a solution for people requiring an easy-to-understand, simple-to-use system.

For power users and enterprise requirements, it provides native-support drivers for some of the most-widely employed multi-user database engines: PostgreSQL, MySQL and MS Access. In addition, the built-in support for JDBC- and ODBC-standard drivers allows you to connect to virtually any other existing database engine as well.

https://www.libreoffice.org/discover/base/ Needs libreoffice-java-common

Building MariaDB connector from source:

Links: [1] https://mariadb.com/kb/en/building-mariadb-connectorodbc-from-source/ [2] git clone https://github.com/MariaDB/mariadb-connector-odbc.git

packages needed openssl openssl-devel unixODBC unixODBC-devel libssl-dev unixodbc odbcinst unixodbc-dev

In my case unixodbc-dev is already installed

$ dpkg -L unixodbc-dev # no man
/.
/usr
/usr/include
/usr/include/autotest.h
/usr/include/odbcinst.h
/usr/include/odbcinstext.h
/usr/include/sql.h
/usr/include/sqlext.h
/usr/include/sqlspi.h
/usr/include/sqltypes.h
/usr/include/sqlucode.h
/usr/include/uodbc_extras.h
/usr/include/uodbc_stats.h
/usr/include/x86_64-linux-gnu
/usr/include/x86_64-linux-gnu/unixodbc_conf.h
/usr/lib
/usr/lib/x86_64-linux-gnu
/usr/lib/x86_64-linux-gnu/libodbc.a
/usr/lib/x86_64-linux-gnu/libodbccr.a
/usr/lib/x86_64-linux-gnu/libodbcinst.a
/usr/share
/usr/share/doc
/usr/share/doc/unixodbc-dev
/usr/share/doc/unixodbc-dev/copyright
/usr/lib/x86_64-linux-gnu/libodbc.so
/usr/lib/x86_64-linux-gnu/libodbccr.so
/usr/lib/x86_64-linux-gnu/libodbcinst.so
/usr/share/doc/unixodbc-dev/NEWS.Debian.gz
/usr/share/doc/unixodbc-dev/changelog.Debian.gz

Questions:

Looking into https://downloads.mariadb.org/connector-odbc/+releases/ there is no 3.17 as here https://downloads.mariadb.com/Connectors/odbc/ and even the latest here https://downloads.mariadb.org/connector-odbc/3.1.10/ is 3.10 not 3.17?

No MariaDB driver showed here http://www.unixodbc.org/ try synaptic package manager for ubuntu?