ODBC Component Documentation

The Gambas ODBC component uses the API provided by the unixODBC driver manager, thus, having unixODBC installed and working is a prerequisite to use the odbc functions in a Gambas application.

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. An ODBC engine needs module drivers to be able to interact with specific database types.

There two choices: UnixODBC and iODBC. Gambas only support and builds againts UnixODBC and apparently is more widely supported. The ODBC specification uses a so called Data Source Names definitios or DSN's to connect to differents DBMS.

About UnixODBC installations

Linux distributions offers packages for install it manually. For example, in the OpenSuse distribution, the unixODBC package is divided into two parts, the basic unixODBC package that provides all the libraries and the basic functionality (but that does not provide the GUI part), and the package that contains the GUI part (you have to search for and install the GUI part as a separate package). If you prefer to compile the unixODBC package yourself you can download the source code from the unixODBC web site http://www.unixodbc.org, and follow the compilation and installation instructions that come with the package. Take in consideration that some distributions comes with development files separated in a "devel" package, by example in OpenSuse and RPM related are called unixodbc-devel.

The module drivers are the necesary 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 module driver permits to the main UnixODBC infraestructure connects to a MySQL DBMS, are so called myodbc in Debian based Linux distributions. For manually compile those module drivers, will need the development header files of the UnixODBC installation.

About UnixODBC Gambas installations

To enable the Gambas ODBC component, linux distributions offers respective packages, by example in Debian based distribution are named gambas3-gb-db-odbc; generally all the gambas related module packages are installed when main gambas package are installed in mayor linux distributions.

In the other hand, if gambas installation was manually and by the compile & install way, need to compile Gambas after the unixODBC package was installed with development headers included. If you have already compiled Gambas before the unixODBC package installation, then you must re-compile the Gambas again. At the compilation's end, you'll see all the Gambas Modules that are configured and enabled in the Gambas environment, if all has worked fine, the ODBC component should be enabled and installed.

About UnixODBC Configurations

The Gambas ODBC component relies on the configuration made in the unixODBC driver modules configurations.

UnixODBC uses the so called "ini" files. UnixODBC distingushes between two types of ini files. System ini files (designed to be accessable but not modifable 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 module drivers available to all users, and the odbc.ini file contains information about DSN's denifintios 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 definitios 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 module driver define a module driver type name, and then this definition are used by the DSN's connection definitions. The second configuration file odbc.ini usage are optionall, can be used to DSN's database connection properties like the user name, and password are defined.

The DSN are connections strings, can be defined in the odbc.ini file or by a connection string, the connection string its all the properties separated by a semi-colon while in the odbc.ini file connection properties are separated by new lines and each connection are divided by ini file sections.

Gambas and UnixODBC overall

Before connecting to a database, must need the ODBC module driver of the specific connection type for the specific DBMS. For the DSN data source can be from system , from user ini file or DSN-less Connection Strings inside code, according the database you want to connect to.

The Gambas ODBC component only parse the calls to the API provided by the unixODBC driver manager, so the DLL SQL or DDL SQL sentences will depend of the unixODBC driver manager implementation layer API. So basically if the ODBC Standar does not implement specific feature, the Gambas component will limit to return TRUE (-1) or FALSE. By example ODBC API definition for a SELECT statement does not implement a return count so the Gambas ODBC API implementation returns -1 always (TRUE) that means at least one row are returned by the sentence; in otherwise cases, no resulset will be returned.

Installation component

The Gambas installation coverts ODBC in respective documentation Gambas Install but as

Component	Requirement libs
gb.db.odbc	libodbc.so

Component Technical Details

Using ODBC via DSN file definition needs the Connection.Type, Connection.Host where the DSN definition name are set from the "odbc.ini" file, and Connection.Login/Pass properties correctly set. For furter documentation see Host property from Connection class.

Using ODBC via ConnectionString needs only the Connection.Type (so that gambas knows it should use the ODBC component) and Connection.Host (for the connection data) properties correctly set.

The component will, first and foremost, scan the Host property looking for semicolons (";"). If it finds at least one, it will assume the property holds a ConnStr. If there are no semicolons, it will connect as it always did, with a DSN. Otherwise, it will use the ConnStr in Host to call ODBC's SQLDriverConnect() function. But when the component calls that ODBC function, no other (gambas) component properties are used, and thus are ignored. It is assumed that all necessary data to connect is present in the ConnStr.

See also the specific documentation pages: How to create and use UnixODBC connection and Connection class documentation.

UnixODBC Connection Configuration

The extens of the ODBC its as far of their driver modules are available for. The list of available module drivers are in http://www.unixodbc.org/drivers.html, by example for connect to a Sybase DBMS a FreeTDS module driver must be installed and configured in the UnixODBC installation system.

Configure a module driver

The UnixODBC module drivers 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 module driver support for an ODBC sybase connection.

By general Linux distributions installs and configured the module drivers by packages.

Configure a DSN connection

Once you've configured the driver you want to use, you have to define a Data Source Name event by definition or by full ODBC connection string.

Since Gambas 3.8.1 allow the Connection.Host property to be a full ODBC connection string.

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 module driver database specific and may vary from database to database.

As you can see, in the following example, the configuration of the two 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.

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 module driver 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.

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 home directory and a normal user.

Example of the odbcinst.ini located in the /etc directory. This file contains all the driver configurations and the Driver Manager's configuration options

Example of /etc/odbcinst.ini file system wide definition

This show a example general file of odbcinst.ini file for the most used ODBC module drivers:

Example of /etc/odbc.ini file system wide

System wide odbc.ini located in the /etc directory using the previous odbcinst.ini definition. The odbc.ini file permists passwords, but remenber that the system wide odbc.ini file are readable by any loged user.

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 permists passwords, and that file must be "chown" to able readable by ower home user.

User's odbc configuration located in the user's home directory .odbc.ini

.odbc.ini

[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

Testing the ODBC DSN definitions

The access can be tested if the UnixODBC package are completly 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 loged; 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 <DSN name> <user> <password>, and the username and password are optional, but in some module drivers such FreeTDS are mandatory.

/bin/bash

% isql ejabberd-useronly username secretpassword.123
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>select * from users

See also

• How to create and use UnixODBC connection

• Connection