Linux Caché ODBC Client

  • Tutorial
A few years ago, the customer, a large medical center of federal importance, instructed us to develop software that serves information kiosks. Outwardly, the kiosk resembles a payment terminal (only without a bill acceptor), its main function, as the name implies, is to provide patients with various information, such as the schedule of doctors' appointments, services and their cost, and so on.
For obvious reasons, the kiosk needed to develop a simplified user interface, which it was decided to implement as a web application. Having a staff of experienced web-programmers who are confident in php, we decided (for speed) to entrust them with writing it by organizing a connection with the database of our medical system. We considered 3 options for interaction:
  • ODBC
  • Jdbc
  • web services.
Web programmers preferred ODBC as the easiest option from their point of view, and the alpha version of the kiosk quickly saw the light of day. However, it soon became clear (surprise!) That the php code should not work under Windows, as it was with the developer, but under Linux, despite the fact that in those years our medical system was operated by the customer on the Windows 2008 platform. To make friends all members of the triad (Linux - Caché ODBC driver - php5) took some effort. I recorded the sequence of actions in the form of a sketch for this article, which I bring to your attention.

Install ODBC Driver

This can be done in several ways, namely, using:
  • custom installation of Caché for Linux by selecting ODBC Client as an option;
  • client installation (cinstall_client script, available in the DVD version of the distribution package);
  • Stand-alone installation of an ODBC client.
The last option will be described below as the most practical, since it can be performed both from the .tar.gz distribution, and from its full DVD version, as well as from the distribution of the Caché ODBC driver distributed as a separate file. Tested in Ubuntu 9.10 and Fedora 8 with the ODBC drivers Caché 2008.1 and 2009.1.
Mount the dvd with the Caché distributions for UNIX s and go to the directory with the ODBC distributions:
sudo mount /dev/dvd /media

If your distribution is not on DVD, but in the form of a .tar.gz file (for example, cache-2009.1.2.602-lnxrhx64.tar.gz), unzip it somewhere. Further does not depend on the distribution variant. Create (where you are comfortable) a folder for installing the driver. Denote it by $ folder.
mkdir $folder

Find the driver for your platform in the distribution kit:
cd /media/dist/ODBC

If you have x86_x64, select ODBC-lnxrhx64.tar.gz, if x86_x32, then ODBC-lnxrhx86.tar.gz. Copy the archive to the $ folder you selected and unzip it in it, then run the installer. Installation will take place in the same folder, there will be no questions.
cd $folder
tar -xvf ODBC-lnxrhx86.tar.gz

Configuring data sources

Next, go to mgr
cd $folder/mgr

and edit the cacheodbc.ini file by changing the definitions of Driver, Host, Port (and possibly Password) in the data source description sections:
Driver = $folder/bin/
Description = Cache ODBC driver
Host = IP-адрес или DNS-имя сервера Caché
Namespace = SAMPLES
Password = SYS (если Caché установлена с минимальной безопасностью, то скорее всего, этот пароль подойдёт)
Port = TCP-порт суперсервера Caché (обычно 1972)

To check the driver and Samples data source, run:
cd $folder/dev/odbc/samples/select
./ # простейший select из таблицы Samples.Person

A small remark is appropriate here. After installation, the Caché ODBC for Linux driver appears in two guises: (8-bit) and (Unicode). In our conditions, both the data source (Caché installation) and the client (php application) were 8-bit (CP1251 encoding), therefore, in the following we will only talk about the 8-bit version of the driver.

Working with Caché ODBC data sources (DSNs) from apache2 / php5

Setting localization CP1251

When using the 8-bit version of the driver, the result set returned by Caché ODBC is always encoded CP1251 (regardless of the type of installation Caché is 8-bit (localization RUW8) or Unicode (RUSW)). This suited us quite well, since the appearance of multilingualism in application data was not expected. It was only necessary to install the appropriate localization in Linux. For example, in Ubuntu 9.10 this is done like this:
sudo locale-gen ru_RU.CP1251
locale -a | grep 1251

in the list of localizations will appear:

On RedHat-like distributions (tested on Fedora Core 8, CentOS 5.x), localization is set like this:
sudo localedef --no-archive -c -f CP1251 -i ru_RU ru_RU.CP1251

Checking for ru_RU.cp1251 is done similarly:
locale -a | grep 1251

Further, so that you can access DSN from php, you need to register them in the ODBC manager. The best known are the following two: iodbc and unixodbc. The first is more versatile, as it allows you to use both versions of the Caché ODBC driver: 8 bits and Unicode. The second is more widespread and easier to install, but (in the case of Caché) only 8 bits are supported. This was not an additional restriction for us, so unixodbc was taken as the basis. Below is a description of its installation in Ubuntu 9.10.

Installing ODBC Unixodbc Manager (on Ubuntu 9.10)

The technique is borrowed from . Namely, install:
sudo apt-get install <имя-пакета>

following packages:

then restart apache:
sudo /etc/init.d/apache2 restart

Register driver and DSN Caché ODBC in unixodbc

Remembering that Caché ODBC was installed in $ folder, run:
cd $folder/dev/odbc/redist/unixodbc

Copy odbcinst_template.ini to odbcinst.ini and write the driver path ($ folder / bin / in this file twice. Copy odbc_template.ini in odbc.ini and write in this file IP, Port, Namespace, UID and Password corresponding to the data source. Repeat for each DSN, if there are several. For testing, it makes sense to create a Samples source (its procurement is present in odbc_template.ini) using the same parameters as in the "Configuring data sources" section. Also create a DSN that targets the application area (let's say it's called QMS) by adding a new section to odbc.ini by copying the [samples] section. Name the section as the region is called, and change - with respect to Samples - just one parameter:
Namespace = QMS

All DSNs can be described in a single odbc.ini file.
The used values ​​of Host, Namespace, Port, UID and Password must correspond to reality, namely, Caché areas containing tables and / or stored procedures should be accessible by these details. The verification method, of course, depends on the type of data stored. If the% Service_Bindings service allows login without authentication in your Caché, you can leave the UID and Password details empty (both of these fields are required). In some versions of Cache, the odbc_template.ini example sets an incorrect Password value for the superuser _system, which is different from the default one when choosing the minimum security in Caché (hint: the password is case-sensitive). In addition, the Caché administrator could change the password. And finally, it is not safe to save the username / password in the DSN, so it is recommended to do this only during testing;
Next, register the driver and all available DSNs:
sudo odbcinst -i -d -f odbcinst.ini
sudo odbcinst -i -s -l -f odbc.ini

After registration, the corresponding entries should appear in the /etc/odbc.ini and /etc/odbcinst.ini files.

Testing in php

If you did everything correctly and described the SAMPLES data source, the $ folder / dev / odbc / samples / php / sample.php example installed with the Caché ODBC driver will work. If you want to try something Russian (and finally start testing your development), do not forget to insert the current localization CP1251 closer to the beginning of your php code:
echo setlocale(LC_ALL, 'ru_RU.CP1251', 'rus_RUS.CP1251', 'Russian_Russia.1251');


Finally, a few words regarding the general architecture of the solution (browser on the kiosk - php application - Caché database). In our case, it simplified the division of labor between web developers and Caché application programmers and made life easier for web developers, while preserving the most familiar and comfortable environment for them. At the same time, we, of course, understood that direct interaction (browser on the client - application code in Caché) should be more effective both in terms of performance and in terms of ease of configuration. This understanding only intensified over the years, and was reflected in the creation of a fully functional web-interface with our medical system.
Nevertheless, ODBC access to Caché still occupies a certain “ecological niche”, because:
  • It has a higher performance than many other access methods (for example, SOAP);
  • it allows you to access tables directly, without intermediate stored procedures;
  • this is a good way to “formalize relations” in cases where the development of a specialized external interface is carried out by a third-party team.

Also popular now: