GAMBAS BOOK 3.19.5

The special ODBC driver 'odbc-postgresql' for the DBMS PostgreSQL can be installed via the application management. Alternatively, enter the following line in a terminal:

$ sudo apt-get install odbc-postgresql

This will show you which ODBC drivers have already been installed:

$ find /usr/lib -name '*odbc*.so'
/usr/lib/gambas3/gb.db.odbc.so                    →	  Gambas-ODBC-Driver
/usr/lib/x86_64-linux-gnu/odbc/libodbctxtS.so
/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so       →  PostgreSQL-ODBC-Driver
/usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so    →  PostgreSQL-ODBC-Driver
...
/usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so       →  PostgreSQL-ODBC-Driver
...

The special ODBC drivers are always configured in the system-wide file with the path /etc/odbcinst.ini.

After installing the ODBC driver 'odbc-postgresql' for the DBMS PostgreSQL, an automatically generated configuration file /etc/odbcinst.ini with the following content already exists:

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

The names of the ODBC drivers are in the square brackets and are required when configuring the data source name (DataSourceName).

A data source name (DSN) is configured in the system-wide file /etc/odbcinst.ini or locally in the file /etc/odbc.ini.

In our case, we configure the data source name in the .odbc.ini file in the home directory of the logged-in system user with local reference. Alternatively, you can use the file /etc/odbc.ini for a system-wide data source name. You can freely define the name. However, it may only appear once in the configuration file. In the following configuration, 'dsn_pg_postgres' is used for the DBMS PostgreSQL and the database 'postgres'.

You can reference the ODBC driver `PostgreSQL Unicode` from the file /etc/odbcinst.ini in the hidden configuration file ~/.odbc.ini as follows:

[dsn_pg_postgres]
Description 	= PostgreSQL UTF8 DB `postgres`
Driver		= PostgreSQL Unicode
ServerName	= localhost
Port		= 5432
User		= postgres
Password	= *PASSWORT*
Database	= postgres
ReadOnly	= No

• The password *PASSWORD* can be omitted and must be requested later by the odbc-enabled application.
• The file ~/.odbc.ini allows passwords, but bear in mind that this can also be read by any logged-in user for the system-wide DSN configuration file /etc/odbcinst.ini!
• Note: An IP interface is available. This allows local and remote access via the network, which is an important idea of ODBC. The port differs from DBMS to DBMS and is standardised.

You can use the odbcinst console programme to inspect the ODBC drivers. The options -q and -d list the names of all (special) ODBC drivers that have registered with unixODBC so far:

$ odbcinst -d -q
[PostgreSQL ANSI]
[PostgreSQL Unicode]

The odbcinst -q -s command, on the other hand, lists all ODBC data source names (DSN) that you have defined in the local configuration file ~/.odbc.ini, for example:

hans@mint20:~$ odbcinst -q -s
[dsn_pg_postgres]

Before the ODBC configuration can be tested for the first time, you should check the following prerequisites:

• The PostgreSQL database server is started.
• There is a DB user who has the necessary rights to read and write access to the database `contacts` and the DB table `contacts`.
• The inspection of the required ODBC drivers and the ODBC data source names (DataSourceName (DSN)) was successful.
• You can use an odbc-capable programme for the test.

A good choice for a simple odbc-capable programme is the console programme `isql`, which was installed automatically when UnixODBC was installed.

The syntax: isql <DSN> <DB_User> [<DB_User_Password> ] is simple, whereby the password is optional and depends on the DSN definition.

For the test, the (system) database `postgres` was entered in the configuration file ~/.odbc.ini, but this is not used. Instead, only the version of PostgreSQL is queried. Knowing the version is of secondary importance here, because the test was only about successfully connecting to a PostgreSQL database via the obdc-capable client programme isql:

hans@mint20:~$ isql -m47 dsn_pg_postgres postgres
+---------------------------------------+
| Connected!                            |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
+---------------------------------------+
SQL> SELECT version();
+------------------------------------------------+
| version                                        |
+------------------------------------------------+
| PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) |
+------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
hans@mint20:~$ 

Conclusion: The test was successful!