October 30, 2012 at 17:52Linux 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:
This can be done in several ways, namely, using:
Mount the dvd with the Caché distributions for UNIX s and go to the directory with the ODBC distributions:
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.
Find the driver for your platform in the distribution kit:
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.
Next, go to mgr
and edit the cacheodbc.ini file by changing the definitions of Driver, Host, Port (and possibly Password) in the data source description sections:
To check the driver and Samples data source, run:
A small remark is appropriate here. After installation, the Caché ODBC for Linux driver appears in two guises: libcacheodbc.so (8-bit) and libcacheodbciw.so (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.
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:
in the list of localizations will appear:
On RedHat-like distributions (tested on Fedora Core 8, CentOS 5.x), localization is set like this:
Checking for ru_RU.cp1251 is done similarly:
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.
The technique is borrowed from ru.php.net/manual/en/function.odbc-connect.php . Namely, install:
following packages:
then restart apache:
Remembering that Caché ODBC was installed in $ folder, run:
Copy odbcinst_template.ini to odbcinst.ini and write the driver path ($ folder / bin / libcacheodbc.so) 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:
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:
After registration, the corresponding entries should appear in the /etc/odbc.ini and /etc/odbcinst.ini files.
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:
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:
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.
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.
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
./ODBCinstall
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:
[Samples]
Driver = $folder/bin/libcacheodbc.so
Description = Cache ODBC driver
Host = IP-адрес или DNS-имя сервера Caché
Namespace = SAMPLES
UID = _SYSTEM
Password = SYS (если Caché установлена с минимальной безопасностью, то скорее всего, этот пароль подойдёт)
Port = TCP-порт суперсервера Caché (обычно 1972)
To check the driver and Samples data source, run:
cd $folder/dev/odbc/samples/select
./select.sh # простейший select из таблицы Samples.Person
A small remark is appropriate here. After installation, the Caché ODBC for Linux driver appears in two guises: libcacheodbc.so (8-bit) and libcacheodbciw.so (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:
ru_RU.cp1251
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 ru.php.net/manual/en/function.odbc-connect.php . Namely, install:
sudo apt-get install <имя-пакета>
following packages:
apache2
apache2-mpm-prefork
apache2-utils
apache2.2-common
libapache2-mod-php5
odbcinst1debian1
php5
php5-cli
php5-common
php5-odbc
unixodbc
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 / libcacheodbc.so) 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:
[QMS]
...
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');
Conclusion
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.