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:
-
Driver Managers (e.g. unixODBC, iODBC, Windows ODBC) which act as a go-between and can plug in vendors’ drivers
-
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
-
unixodbc a free opensource ODBC manager with a supplied SQL frontend (good for testing the database). See www.unixodbc.org
-
iodbc an ODBC manager from OpenLink, commercial but free to use. See www.iodbc.org
-
ifxodbc direct ODBC to Informix engines (using libraries from Informix CSDK )
-
pgodbc direct ODBC to PostgreSQL engines (free opensource). Not really needed now.
-
sapodbc direct ODBC with SAPDB (a free opensource Database Engine up till version 7.3)
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
|
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.
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
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.
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.