Caché ODBC Client for Linux
A few years ago, the customer, a major medical center of federal importance, instructed us to develop software that serves information kiosks. Externally, 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 a schedule for receiving doctors, services and their cost, and so on.
For obvious reasons, the kiosk needed to develop a simplified user interface, which was decided to be implemented as a web application. Having a staff of experienced web programmers who confidently own php, they decided (for speed) to instruct them to write it by organizing a link to the database of our medical system. Three interaction options were considered:
This can be done in several ways, namely, using:
Mount the Caché distributions dvd for UNIX and go to the directory with the ODBC distributions:
If your distribution is not on DVD, but as a .tar.gz file (for example, cache-2009.1.2.602-lnxrhx64.tar.gz), unpack it somewhere. Further does not depend on the version of the distribution kit. Create (where you prefer) a folder to install the driver. We denote it by $ folder.
Find the driver for your platform in the distribution:
If you have x86_x64, select ODBC-lnxrhx64.tar.gz, if x86_x32, then ODBC-lnxrhx86.tar.gz. Copy the archive into your chosen folder $ folder and unpack 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 Driver, Host, Port (and possibly Password) definitions 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, we will only discuss the 8-bit version of the driver.
The result set returned by Caché ODBC, when using the 8-bit version of the driver, always has the encoding CP1251 (regardless of the type of installation Caché - 8-bit (localization RUW8) or Unicode (RUSW)). We were quite satisfied with this, since the appearance of multilingualism in the application data was not expected. It was necessary only 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 in Fedora Core 8, CentOS 5.x), localization is set as follows:
Checking for ru_RU.cp1251 is performed in the same way:
Further, in order for php to be able to access the DSN, it is necessary to register them in the ODBC manager. The following two are best known: iodbc and unixodbc. The first one is more versatile, since it allows you to use both versions of the Caché ODBC driver: 8 bits and Unicode. The second is more widely distributed and easier to install, but (in the case of Caché) only supports 8 bits. This did not constitute an additional limitation for us, therefore 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 type the driver path in this file twice ($ folder / bin / libcacheodbc.so). Copy odbc_template.ini to odbc.ini and list in this file the 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 preparation is present in odbc_template.ini), using the same parameters as in the section “Configuring Data Sources”. Also create a DSN aimed at the application area (say, it is called QMS), adding a new section to odbc.ini by copying the [samples] section. Name the section in the same way as the area is called, and change - with respect to Samples - just one parameter:
All DSNs can be described in a single odbc.ini file.
The values ​​used for Host, Namespace, Port, UID and Password must correspond to reality, namely, the Caché areas containing tables and / or stored procedures must be accessible by these requisites. The method of verification, of course, depends on the type of stored data. If your Caché% Service_Bindings service allows login without authentication, you can leave the UID and Password details empty (both of these fields are required). In some versions of ache, in the odbc_template.ini example, the Password is incorrect for the superuser _system, which is different from the current default when selecting 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 a login / password in a DSN, it is recommended to do so only during testing; during operation, it is more expedient to “sew” them into a php application and transfer them to the server when establishing a connection via ODBC.
Next, register the driver and all available DSNs:
After registering in the /etc/odbc.ini and /etc/odbcinst.ini files, the corresponding entries should appear.
')
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 (well, finally, start testing your development), do not forget to insert the current locale CP1251 closer to the top of your php code:
Finally, a few words about the overall solution architecture (browser in the kiosk - php application - Caché DB). In our case, it simplified the division of labor between web developers and applied Caché programmers and made life easier for web developers, while maintaining the most familiar and comfortable environment for them. At the same time, of course, we understood that direct interaction (browser on the client - application code in Caché) should be more efficient both in terms of performance and in terms of ease of configuration. This understanding has only strengthened over the years, and is reflected in the creation of a full-featured web-interface with our medical system.
However, ODBC access to Caché still occupies a certain “ecological niche” because:
For obvious reasons, the kiosk needed to develop a simplified user interface, which was decided to be implemented as a web application. Having a staff of experienced web programmers who confidently own php, they decided (for speed) to instruct them to write it by organizing a link to the database of our medical system. Three interaction options were considered:
- ODBC
- Jdbc
- web services.
Installing the 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 kit);
- Stand-alone installation of an ODBC client.
Mount the Caché distributions dvd for UNIX and go to the directory with the ODBC distributions:
sudo mount /dev/dvd /media If your distribution is not on DVD, but as a .tar.gz file (for example, cache-2009.1.2.602-lnxrhx64.tar.gz), unpack it somewhere. Further does not depend on the version of the distribution kit. Create (where you prefer) a folder to install the driver. We denote it by $ folder.
mkdir $folder Find the driver for your platform in the distribution:
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 into your chosen folder $ folder and unpack 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 Configure data sources
Next, go to mgr
cd $folder/mgr and edit the cacheodbc.ini file by changing the Driver, Host, Port (and possibly Password) definitions 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, we will only discuss the 8-bit version of the driver.
Working with Caché ODBC data sources (DSN) from apache2 / php5
Localization setting CP1251
The result set returned by Caché ODBC, when using the 8-bit version of the driver, always has the encoding CP1251 (regardless of the type of installation Caché - 8-bit (localization RUW8) or Unicode (RUSW)). We were quite satisfied with this, since the appearance of multilingualism in the application data was not expected. It was necessary only 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 in Fedora Core 8, CentOS 5.x), localization is set as follows:
sudo localedef --no-archive -c -f CP1251 -i ru_RU ru_RU.CP1251 Checking for ru_RU.cp1251 is performed in the same way:
locale -a | grep 1251 Further, in order for php to be able to access the DSN, it is necessary to register them in the ODBC manager. The following two are best known: iodbc and unixodbc. The first one is more versatile, since it allows you to use both versions of the Caché ODBC driver: 8 bits and Unicode. The second is more widely distributed and easier to install, but (in the case of Caché) only supports 8 bits. This did not constitute an additional limitation for us, therefore unixodbc was taken as the basis. Below is a description of its installation in Ubuntu 9.10.
Installing the 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 type the driver path in this file twice ($ folder / bin / libcacheodbc.so). Copy odbc_template.ini to odbc.ini and list in this file the 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 preparation is present in odbc_template.ini), using the same parameters as in the section “Configuring Data Sources”. Also create a DSN aimed at the application area (say, it is called QMS), adding a new section to odbc.ini by copying the [samples] section. Name the section in the same way as the area 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 values ​​used for Host, Namespace, Port, UID and Password must correspond to reality, namely, the Caché areas containing tables and / or stored procedures must be accessible by these requisites. The method of verification, of course, depends on the type of stored data. If your Caché% Service_Bindings service allows login without authentication, you can leave the UID and Password details empty (both of these fields are required). In some versions of ache, in the odbc_template.ini example, the Password is incorrect for the superuser _system, which is different from the current default when selecting 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 a login / password in a DSN, it is recommended to do so only during testing; during operation, it is more expedient to “sew” them into a php application and transfer them to the server when establishing a connection via ODBC.
Next, register the driver and all available DSNs:
sudo odbcinst -i -d -f odbcinst.ini sudo odbcinst -i -s -l -f odbc.ini After registering in the /etc/odbc.ini and /etc/odbcinst.ini files, the corresponding entries should appear.
')
Php testing
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 (well, finally, start testing your development), do not forget to insert the current locale CP1251 closer to the top of your php code:
echo setlocale(LC_ALL, 'ru_RU.CP1251', 'rus_RUS.CP1251', 'Russian_Russia.1251'); Conclusion
Finally, a few words about the overall solution architecture (browser in the kiosk - php application - Caché DB). In our case, it simplified the division of labor between web developers and applied Caché programmers and made life easier for web developers, while maintaining the most familiar and comfortable environment for them. At the same time, of course, we understood that direct interaction (browser on the client - application code in Caché) should be more efficient both in terms of performance and in terms of ease of configuration. This understanding has only strengthened over the years, and is reflected in the creation of a full-featured web-interface with our medical system.
However, ODBC access to Caché still occupies a certain “ecological niche” because:
- it is more productive 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 relationships" in cases where the development of a specialized external interface is carried out by a third-party team.
Source: https://habr.com/ru/post/156321/
All Articles