Chapter 3: Set up Up Chapter 3: Set up Section 3.2: Databases 

3.1 ODBC

ODBC (Open Database Connectivity) is an X/Open and ANSI standard CLI (Call Level Interface) for communicating with database backends through a common library called a Driver Manager which in turn uses another library (called a driver) appropriate to the backend desired. All ODBC libraries implement common functions (an API or Application Programming Interface) with the details of the functions tailored to the particular backend,
ODBC comes in two broad categories:
  1. Driver Managers (e.g. unixODBC, iODBC, Windows ODBC) which act as a go-between and can plug in vendors’ drivers
  2. Direct (e.g. Informix, PostgreSQL, SAPDB, SQLite) which link directly to the vendors’ drivers
Aubit4GL can handle embedded SQL with a library of ODBC (Open Database Connectivity) functions intended for passing to an implementation of ODBC. You need to install the ODBC application as well as the database vendor’s odbc library files. (These latter may or may not come with the ODBC application).
On Unix/Linux platforms the ODBC options supported are

3.1.1 ODBC config files

ODBC configuration is held in files: /etc/odbcinst.ini (driver info) and /etc/odbc.ini (datasources). Each user may have his own configuration in ~/.odbc.ini (where ~ means the user’s home directory). Applications often supply nice GUI applications to simplify editing these files. Unfortunately implementation of ODBC is so inconsistent between database suppliers, that these GUIs are useless. Use vi and edit the files by hand. Then observe the notes for each vendor and copy or link the files appropriately.

3.1.1.1 Sample odbcinst.ini

The file odbcinst.ini holds a list of ODBC drivers. An example:
[Informix]
Driver=/opt/informix/lib/cli/libifcli.so
Setup=/opt/informix/lib/cli/libifcli.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=03.00
FileUsage=0
SQLLevel=1
smProcessPerConnect=Y
 
[PostgreSQL]
Driver=/usr/lib/libodbcpsql.so
Setup=/usr/lbi/libodbcpsqlS.so
FileUsage=1
Threading=2
 
[SAPDB]
Driver=/opt/sapdb/interfaces/odbc/lib/libsqlod.so
Setup=/usr/lib/libsapdbS.so
FileUsage=1
CPTimeout=
CPReuse=
The Informix drivers will not tolerate whitespace (blanks or tabs) in the above file.

3.1.1.2 ODBC Datasources

Access to ODBC databases is configured in odbc.ini files which contain all the information required by the vendor’s drivers to allow a connection. For example:
[infstores]
Description=Informixstores demo database
Driver=/opt/informix/lib/libifcli.so
Database=stores7
LogonID=fred
pwd=zxcv132
ServerName=elvis
CLIENT_LOCALE=en_us.8859-1
TRANSLATIONDLL=/opt/informix/lib/esql/igo4a304.so
[pgstores]
Description=Postgres stores demo database
Driver=PostgreSQL
Trace=Yes
Tracefile=sql.log
Database=pgstores
Servername=localhost
UserName=
Password=
Port=5432
Protocol=6.4
ReadOnly=No
RowVersioning=No
ShowSystemTables=No
ShowOidColumn=No
FakeOidIndex=No
ConSettings=
[SAPstores]
Description=SAP stores demo database
Driver=SAPDB
ServerNode=elvis
ServerDB=stores

In principle, the Server property should be the name from the odbcinst.ini list of drivers, but the Informix driver needs the full path to the driver library file.
The Informix driver will not find the /etc/odbc.ini file unless you point to it with the environment variable: ODBCINI
export ODBCINI=/etc/odbc.ini
Note that the different vendors use different keywords for naming the same things, and they have different sets of properties.


3.1.1.3 Informix ODBC Drivers

Informix give a choice of 4 ODBC drivers. They are installed in $INFORMIXDIR/lib/cli (usually /opt/informix/lib/cli on Linux systems). There appear to be 7 files but 3 of them are links to other files. Informix does not use separate files for setup; each library file contains both driver and driver setup functions.
Static Dynamic
Threaded libthcli.a libthcli.so or oclit09b.so
Unthreaded libcli.a or libifcli.a libifcli.so or iclis09b.so
3.1.1.3.1 Informix Driver Manager
Informix supplies a driver manager replacement (DMR) file with 2 links:
libifdmr.so
idmrs09a.so

3.1.1.4 PostgreSQL Drivers

PostgreSQL ODBC drivers are installed by default in /usr/lib
Static Dynamic
driver libodbcpsql.a libodbcpsqlso
driver setup libodbcpsqlS.a libodbcpsqlS.so
Note that there is a separate file Postgres driver setup.

3.1.1.5 SAPDB Drivers

SAPDB drivers are installed by default in /opt/sapdb/interfaces/odbc/lib/
Static Dynamic
Driver libsqlod.a libsqlod.so
For SAPDB, use driver setup file from unixODBC: /usr/lib/libsapdbS.so
SAPDB will not find its odbc.ini file unless it is in /usr/spool/sql/ini (which it will have created at install time). You must either copy or link /etc/odbc.ini to that directory:
cd /usr/spool/sql/ini
ln -s /etc/odbc.ini .
On Linux systems /usr/spool with be a symbolic link to /var/spool

3.1.1.6 ODBC Warning

There are different versions of ODBC (2.5, 3.0, 3.5) - each with its own peculiarities. There are also big differences between what is required and what is optional - not all drivers implement the full ODBC functionality.

3.1.1.7 Native

Aubit 4GL can process DATABASE statements directly if it has a native interface to the database engine. To achieve this, we need the database vendor’s ESQL/C compiler (Embedded SQL in C) available when we compile the Aubit4GL compilers.
Embedded SQL/C is an ANSI SQL standard for allowing you to embed SQL statements into C source files. The SQL statements are enclosed within EXEC SQL ... END SQL tags. Traditionally the ESQL/C file has a .ec suffix. A vendor supplied pre-compiler then replaces the SQL statements with appropriate calls to functions in the vendor’s libraries. The result of the compile is a C code .c file which can be compiled and linked to make executables, modules, or .so or .a library files.
At install time, the Aubit 4GL configure program looks for vendors ESQLC files and builds an interface to each of the vendor databases detected.
Backend ESQL compiler Suffix
Informix /opt/informix/bin/esql .ec
PostgreSQL /usr/bin/ecpg .pgc
SAPDB /opt/sapdb/interfaces/precompiler/bin/cpc .cpc
SQLite??? Help here please!
Aubit4GL Native Connections
SQLTYPE RDBMS Compiler Comment
esql Informix esqlc
esqlPG PostgreSQL ecpg
pg PostgreSQL ecpg obsoleted by pg8
esqlSAP SAPDB cpc
esqlQ Querix esqlQ
The environment variable A4GL_SQLTYPE determines which connection is used when program (or 4glc compiler) is run.
 Chapter 3: Set up Up Chapter 3: Set up Section 3.2: Databases