Configuring applications to use ODBC ==================================== Document Revision: 24.07.2000 This document provides some notes on how to configure/use a few ODBC aware applications (mostly specific to the Linux platform). This is mostly the result of testing ODBC aware applications with iODBC and the SQL/R ODBC driver on the Linux platform. 1. PHP ------ PHP is a script engine which is used to create dynamic web pages and supports a whole variety of database access options. It is usually used as an Apache module and most Linux distributions should include an Apache version with PHP. However the distributed versions are often restricted to MySQL support, ODBC support is not compiled in. To ease use of SQL/R ODBC for web applications we have created an Apache binary which includes a PHP version and ODBC support for the Linux and HÜ-UX platform (please refer to the apache directory). An example PHP application is also available in the php directory. 2. StarOffice ------------- StarOffice 5.1[a] and 5.2 version include ODBC support on the Linux platform. Please follow the notes below to use it with the SQL/R ODBC driver. - StarOffice 5.1[a] requires a glibc2.0 (aka libc6) based system. While you can install it using an alterntive glibc2.0 on a libc5 based system, StarOffice requires usage of the libc6 driver version. StarOffice looks for the libodbc.so library. The easiest solution is to either create a symbolic link to the libiodbc.so library. cd /usr/lib ln -s libiodbc.so.2 libodbc.so (When using StarOffice on a libc5 based system you should copy the libc6 based libiodbc.so binary to the starOffice lib directory as libodbc.so). To configure a database with StarOffice follow the directions below: - Create a new database by selecting the menu entriy File->New->Database. The Database Property Dialog becomes visible. - In the "General" page, enter the StarOffice specific name of the data base. - In the "Type" page, select the type ODBC and enter the data source name. If a section "ODBC Data Sources" is present in your odbc.ini file you can also use the "Browse" option to select an entry. - In the "ODBC" page, specify the user name and password as required. Otherwise "public" and an empty password are assumed. The following Problems were encountered during our testing: - Driver options specified in the ODBC properties might be ignored. The reason is that StarOffice passes those options to the driver enclosed in quotes and the SQL/R ODBC driver before version A.02.26 is unable to handle this. The workaround is to specify a dummy argument before and after arguments. For example: dummy1=0;Server=lxmike;Service=8003;dummy2=0 Can be used to specify the Server=lxmike and Service=8003 options. With dirver version A.02.26 this is no longer required. - Remote data sources: The SQL/R ODBC driver before version A.02.26 as used by StarOffice does not use additional driver arguments specified in the odbc.ini file, such as the "Server=" setting. In order to use a remote data source these settings can be specified in the Driver options. Please note the caveat with driver options mentioned above. With dirver version A.02.26 this is no longer required. - StarOffice 5.1[a] has a problem to display float values in a form. A value of -21474836.48 (that is -2^32) is output instead, independend of the value. This only applies to forms. This looks like a bug in StarOffice and is fixed in version 5.2. - When using a column name alias, StarOffice generates a syntax not supported by SQL/R. SQL/R currently requires column aliases and alternative column headers to be enclosed in quotes. The workaround is to specify a SQL statement rather than using the "AutoPilot" and enclose alternative column headers in single quotes. There is a toggle button on top of the query designer which needs to be pressed to allow specifying SQL statements not understood by the SQL parser built into StarOffice. - When displaying an ODBC error message, StarOffice sometimes complains about beeing unable to write to the database when in fact it encountered an error reading the database. StarOffice is not able to write to the database with the current SQL/R ODBC version. * StarOffice 5.2 requires a glibc2.1 based system. You need to install the glibc2.1 iODBC driver manager and specify the glibc2.1 version of the ODBC driver in your odbc.ini. SQL/R A.02.26 provides support for the glibc2.1. Previous versions do not work and cause StarOffice 5.2 to crash while loading the driver. In general, the notes for StarOffice 5.1 apply also for StarOffice 5.2. The following exceptions were found: - The problem with displaying float values in a form has been fixed. 2. ApplixWare ------------- Appliware versions 4.4 and above include ODBC support on the Linux platform. Please follow the notes below to use it with the SQL/R ODBC driver. We have tested AppliWare version 4.41. Applixware 4.41 includes the iODBC driver manager version 2.0.12 in the directory /opt/applix/axdata/axshlib/lib. You have the option of either continuing to use the version included with ApplixWare (libodbc.so) or to replace the libiodbc.so by a symbolic link to a more recent version. ApplixWare includes its own middleware, the axnet server, which provides connectivity to local or remote data sources. Please note that this is different from specifying a remote data source in the odbc.ini file. We found no problems using ApplixWare with the SQL/R ODBC driver and both, the iODBC 2.0.12 driver manager which comes with ApplixWare as well as the the newer iODBC 2.0.50. ApplixWare requires the section "ODBC Data Sources" to be present in your odbc.ini (or .odbc.ini) file to show the available data sources. 3. Trouble Shooting ------------------- In case a problem is encountered (or ou are asked by our support) a driver debug log can be created by setting the environment variables SQLR_LOG_FILE, SQLR_LOG_FLAGS and SQLR_DEBUG. SQLR_DEBUG - 0 or 1, default is 0. Equivalent to the "Debug=" option on Windows. If set to 1 ODBC error messages include a reference to the source code. This option should be used when asked by support. SQLR_LOG_FILE - path/file name to log file. Default is stderr. Equivalent to the "LogFile=" option in Windows. If set, log messages are appended to the specified file. SQLR_LOG_FLAGS - driver debug options. Default is no debug messages. Equivalent to the "LogMode=" option on Windows. The example below will instruct the SQL/R ODBC driver to write log messages to file /tmp/odbc.log (all level 1 messages). export SQLR_LOG_FILE=/tmp/odbc.log export SQLR_LOG_FLAGS="*1" Log levels 0 - error messages 1 - function calls and arguments 2 - debug 3 - verbose debug Log levels 2 and 3 are only meaningful to support.