GAMBAS BOOK 3.19.1

ODBC

ODBC is a standardised database interface developed by Microsoft® that uses SQL. It enables SQL databases from different manufacturers to be addressed from a (Gambas) programme using the same commands. The prerequisite is that the manufacturer of a database provides an ODBC driver for it and that the respective driver is installed on the system.

The use of this so-called “middleware” (software whose task is to mediate between two independent applications) brings with it increased complexity as well as disadvantages compared to the direct use of the DBMS SQLite, MySQL (MariaDB) and PostgreSQL → potentially more errors and poorer performance. The use of ODBC in Gambas programmes for SQLite, MySQL (MariaDB) and PostgreSQL is therefore not recommended.

For programmes for which it is not known which database they will find in their environment, ODBC is a good option for using different databases. One example is the LibreOffice® programme suite, where ODBC can be used to connect the Writer and Calc programmes (via Base) to different databases.

• You will use ODBC (Open Database Connectivity) as a database interface in a Gambas programme if you want to use a database that is not directly supported by Gambas.
• ODBC allows you to use the same commands for different SQL databases and thus (theoretically) simplifies the exchange of the database.
• You can only access the SQL databases after installing suitable ODBC drivers.

GAMBAS

In the following chapters, we will show you examples of how to set up ODBC drivers for SQlite, MySQL and PostgreSQL databases.

• Within the Gambas IDE, you can create databases and DB tables for the DBMS SQLite, MySQL (MariaDB) and PostgreSQL.
• You can insert, change or delete data in the DB tables.
• Gambas offers with gb.db the possibility to work in your own programmes independently of the underlying DBMS!

Remember:
It is actually absurd to use a Gambas programme to access SQLite, MySQL (MariaDB) and PostgreSQL databases via ODBC, which can be done more directly, easily and reliably in Gambas!

As the installation and configuration of the required ODBC drivers is not a trivial task, this and the following three chapters describe their installation and configuration in detail - always with the aim of enabling you to successfully use your databases and DB tables created with Gambas in odbc-capable applications!

To use ODBC as a wrapper for accessing databases and as a driver manager, a number of requirements must be met:

• Installation of the ODBC database interface from the 'unixODBC' package, as the ODBC interface (gb.db.odbc) of Gambas relies on 'unixODBC'.
• Installation of the special ODBC drivers for the DBMS used.
• Configuration of the special ODBC drivers in a system-wide file /etc/odbcinst.ini.
• Configuration of the DataSourceName (DSN) as a reference to the data source to be used, for example in the (hidden) file ~/.odbc.ini, which defines the ODBC driver to be used and the name of the database.
• For DBMS such as MySQL (MariaDB) or PostgreSQL, information on the DB user and the DB password for this DB user is also required.
• For testing purposes, basic knowledge of the syntax and meaning of the options should be available for an odbc-capable programme such as the console programme 'isql' from the 'unixODBC' package.

The explanations on 'ODBC and Gambas' in the first two sections were edited and supplemented by Christof Thalhofer as an ODBC user.

UnixODBC is an implementation of the Open Database Connectivity standard, a database abstraction layer that enables odbc-enabled applications to work with many different relational databases via a single library. This package also contains the ODBC client 'isql', a console programme with which SQL commands can be entered interactively, as well as the odbcinst and odbcinst1debian2 packages.

First install the ODBC driver via the 'unixodbc' package:

$ sudo apt-get install unixodbc
[sudo] Password for hans:     
Package lists are read... Done
Dependency tree is built.       
Status information is read.... Finished
The following additional packages are installed:
  odbcinst odbcinst1debian2
The following NEW packages are installed:
  odbcinst odbcinst1debian2 unixodbc
...

The following command 'odbcinst' with the -j option in a terminal provides initial information on the version and the paths of the system-wide and local configuration files:

$ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/hans/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The next chapters describe the installation and configuration of the special ODBC drivers for the DBMS PostgreSQL, SQlite and MySQL as well as tests with the odbc-capable programme 'isql'.