UnixODBC.pm Installation - Contents - Overview - Installing UnixODBC.pm - Testing UnixODBC.pm - Installing and configuring the bridge server - Installing the GUI data manager, tkdm - Installing the Web data manager - Client scripts - Troubleshooting - Copyright - Overview UnixODBC.pm provides Perl programs with a subset of the X/Open ODBC Application Programming Interface implemented by the unixODBC libraries from http://www.easysoft.com/. UnixODBC.pm also provides a network bridge API that implements ODBC function calls on networked host systems using TCP/IP and Remote Procedure Calls, as well as simple bridge client and server scripts. Refer to the section, "Installing and configuring the bridge server," below. There are also sample text-mode clients (refer to the section, "Client scripts"), and two Graphical User Interface data manager applications that can perform ODBC queries on networked systems: one that provides a Web Browser interface and communicates with the UnixODBC bridge API using Apache and CGI scripts and another, tkdm, that uses Perl/Tk. Refer to the sections, "Installing the GUI data manager, tkdm," and, "Installing the Web data manager," below, and the file, "INSTALL" in the tkdm subdirectory. Be sure also to consult dbms/README for information about operating system and DBMS server compatibility and DBMS server installation notes. - Installing UnixODBC.pm UnixODBC.pm links against the unixODBC run time libraries, from http://www.easysoft.com, so they need to be installed first. This version of UnixODBC.pm works with version 2.2.0 and later of the libraries. UnixODBC.pm also requires the unixODBC C-language include files from unixODBC to compile UnixODBC.pm. They should be installed in /usr/local/include when you install unixODBC. If they aren't, you can find them in the include/ subdirectory of the unixODBC source code package. To use the bridge server (to use ODBC to query DBMS servers on networked systems), you'll need to have installed on each host the same versions of Perl, Net::Daemon, PlRPC, and the modules that they require. Tkdm requires Perl/Tk, but. UnixODBC.pm and the non-network text-mode programs work without the bridge server. This version of UnixODBC requires the following Perl libraries: - Net-Daemon-0.35 - 0.37 - PlRPC-0.2015 - 0.2017 - Tk800.021 - 0.23 Versions of Perl prior to 5.6 might also need Sys::Syslog.pm and Storable.pm. IMPORTANT - Each host system must use the same versions of ALL the Perl software that UnixODBC uses - Perl, UnixODBC.pm, UnixODBC::BridgeServer.pm, Net-Daemon and PlRPC packages, Sys::Syslog.pm, and Storable.pm - on each networked system in order for the Bridge clients and servers to communicate with each other. To build and install the modules, use the following commands: $ cd UnixODBC- $ perl Makefile.PL $ su # make install If this is the first time installing UnixODBC.pm on the system, the installation gives the warning that UnixODBC.pm is not installed. If you receive warning messages that other library modules are not installed, you should install them before installing UnixODBC. To run the test script dbms/test.pl, you need to have installed the data in the dbms subdirectory. Refer to, "Testing UnixODBC.pm," below. The ODBC driver needs to be configured for your DBMS and a data source using either odbcinst or ODBCConfig. Standard Disclaimer - UnixODBC.pm and the client and server scripts were written with Perl installed in /usr/local/bin. If Perl scripts produce an error message like: sh: ./client: no such file or directory It means the script can't find the Perl interpreter. Either change the path on the first line of the script to the location of the perl binary (the output of, "which perl"), or create a symlink /usr/local/bin/perl to the actual perl program. - Testing UnixODBC.pm UnixODBC.pm is known to work with MySQL and PostgreSQL on Linux and Solaris systems. Running, "make test," makes sure that the library modules load correctly. The dbms/test.pl script excercises the unixODBC API more thoroughly. For the script to complete, it is necessary to have installed the test data in the dbms/ subdirectory, and to have configured a DSN for the test data. Refer to the file dbms/README for notes about MySQL and PostgreSQL DBMS's. Running test.pl is likely to result in at least a few errors. The script is designed to test the subset of ODBC API functions that UnixODBC.pm implements as exhaustively as possible, and as many different DBMS drivers as possible (currently the MyODBC and Easysoft PostgreSQL drivers). - Installing and configuring the bridge server. IMPORTANT (Again) - The versions of Perl, Net::Daemon, Storable, and PlRPC MUST be the same on all hosts. Net::Daemon, in particular, checks for version information when authenticating, and PlRPC will generate protocol errors when communicating with different versions. Storable also relies having the same version of itself and Perl on local and remote systems. UnixODBC and the supporting libraries listed above are not thread-safe (yet), so you may encounter thread exceptions on some systems if Perl 5.8.0 has ithreads configured. Net::Daemon errors are commonly recorded in the syslog. Although you can configure the log messages, check the syslog first. Refer to the, "Troubleshooting," section, below. The bridge server runs as user nobody and uses port 9999. To change the configuration, edit /usr/local/etc/odbcbridge.conf after installation. The RPC server settings are in the RPC::PlServer(3) man page. Example server and client scripts are in the bridge/ directory. For further examples of writing Perl RPC clients and servers, refer to the man pages for RPC::PlServer and RPC::PlClient, and the ProxyServer.pm module in the Perl DBI bundle. Install UnixODBC.pm. If the installation proceeded correctly, everything should be installed in the proper directories, and you should be able to start and stop the bridge server daemon by typing: # /usr/local/etc/init.d/unixodbc start # /usr/local/etc/init.d/unixodbc stop Manual Installation - Create the directories /usr/local/etc, /usr/local/var, and /usr/local/sbin if they don't exist already, Then, $ su # mkdir /usr/local/var/odbcbridge # chown nobody /usr/local/var/odbcbridge # cp bridge/odbcbridge.conf /usr/local/etc # cp bridge/odbclogins /usr/local/etc # chmod 0600 /usr/local/etc/odbclogins # chown nobody /usr/local/etc/odbclogins # mkdir /usr/local/etc/init.d # cp bridge/unixodbc /usr/local/etc/init.d # cp bridge/odbcbridge /usr/local/sbin # chmod +x /usr/local/sbin/odbcbridge If everything is installed correctly, you should be able to start and stop the server by typing: # /usr/local/etc/init.d/unixodbc start # /usr/local/etc/init.d/unixodbc stop To start the server when the system starts, install the unixodbc script in the same directory as the other local startup scripts, refer to the system administration documents for information about how to start daemons during system initialization. - Installing the GUI data manager, tkdm The GUI data manager, tkdm is installed when UnixODBC.pm is installed. Refer to the man page ("man tkdm") and the tkdm/README file for instructions about configuring tkdm. - Installing the Web data manager The Web data manager works with the Web servers: Apache 1.3 and 2.0, and the Web browsers: Mozilla 1.0, Netscape 4.75, and Internet Explorer 3.03. The document describes a configuration that uses /usr/local/apache/htdocs as the Apache DocumentRoot directory and /usr/local/apache/cgi-bin as the CGI script directory. Make the necessary adjustments for the system's Apache configuration. APACHE CONFIGURATION - Httpd.conf must be configured to load mod_perl, mod_env, and mod_ssi, and use server-side includes. On some systems, LD_LIBRARY_PATH in the server's environment must be able to locate all of the libraries that the DBMS server, ODBC, and Perl require. The data manager is configured to be installed in the, "datamanager," subdirectory of, "htdocs," or the directory named in the httpd.conf DocumentRoot directive. If installing in a different subdirectory, modify the directives shown below for httpd.conf accordingly, and change the value of $folder in the CGI script, datamanager.cgi. If the Apache server does not have server-side includes enabled, add the following lines to httpd.conf: Options FollowSymLinks Includes AddType text/html .shtml AddHandler server-parsed .shtml To add the path for all of the libraries to the server's environment, add a line like this to httpd.conf: SetEnv LD_LIBRARY_PATH /usr/lib:/usr/local/lib:/usr/local/mysql/lib/mysql The names of the directories will vary depending on the system's Apache and DBMS server configuration. Then restart the Apache: # /usr/local/apache/bin/apachectl restart Then install the files with these steps. $ su # mkdir /usr/local/apache/htdocs/datamanager # cp datamanager/*.html /usr/local/apache/htdocs/datamanager # cp datamanager/*.shtml /usr/local/apache/htdocs/datamanager # cp datamanager/*.gif /usr/local/apache/icons # cp datamanger/odbclogins /usr/local/etc # cp datamanager/*.cgi /usr/local/apache/cgi-bin # cd /usr/local/apache/cgi-bin # chmod +x tables.cgi datamanager.cgi odbclogin.cgi Edit the path to the perl interpreter on the first line of tables,cgi, datamanager.cgi, odbclogin.cgi to the path of the actual perl interpreter. The path should be either /usr/local/bin/perl, /usr/bin/perl, or the output of the shell command, "which perl." # cd /usr/local/etc # chmod 0600 odbclogins # chown nobody odbclogins # Use the UID/GID of httpd.conf's, # chgrp nobody odbclogins # "User," and, "Group," directives. Edit each line of the file /usr/local/etc/odbclogins for the login of each system that has a bridge server installed. The format of each line is: host::user::password The user names and passwords need not be the same as those used to log in to the DSNs. They simply need to allow the clients (either the CGI scripts or the command-line scripts) to log in to the bridge server on the remote system, so they need to be actual users with passwords, with their own entries in /etc/passwd. IMPORTANT - /usr/local/etc/odbclogins contains login data for remote systems. It is important to change the permissions and ownership so that it can be read by the httpd server processes ONLY (i.e., the CGI scripts). Apache servers often run as user nobody, group nobody, but the ownership of /usr/local/etc/odbclogins to the values in the User and Group http.conf directives. Also, do a, "chmod 0600 /usr/local/etc/odbclogins," to remove, "group," and, "other," permissions from the file. If this isn't secure enough, you can also create a separate UID (www, for example) and make sure to change the User directive value in httpd.conf. Standard Disclaimer (Again) - The CGI scripts are written to find Perl as /usr/local/bin/perl. If that isn't the configuration on the system, then either edit the first line of the scripts to reflect the actual location of the perl binary (the output of, "which perl") or make /usr/local/bin/perl symlink to the interpreter. The ODBC logo is modified from the XPM in the unixODBC-2.2.2 Data Manager source tree. CGI script errors are recorded in the Apache error_log, even if they don't appear in the browser window. If the scripts seem to work right, but the data manager can't connect to data sources, look for the bridge error messages in the syslog, and consult the section, "Troubleshooting," below. - Client scripts The eg/ directory contains example ODBC clients for locally hosted ODBC servers. The scripts that log into a data source require the DSN, DBMS login name and DBMS password as command line arguments. The client scripts in the bridge/ subdirectory, for networked ODBC servers, additionally require the name or IP address of the host system. Documentation for the client scripts are in the man pages. The command line option, "--help," prints a brief help message. # alltypes --help Clients for local host ODBC queries. - alltypes List the SQL data types for a DSN. - apifuncs ODBC API functions that a driver implements. - colattributes Column attributes for a title. - connectinfo Connection attributes. - datasources DSN's on the local system. - driverinfo Drivers on the local system. Clients for remote DSN queries are in the, "bridge," subdirectory. sampleclient and sampleclient2 - Testing ODBC function calls using the peer to peer bridge. - remotedsn List the DSN's of a remote system. - remotetables List the tables of a remote DSN. - Troubleshooting - Perl error messages similar to, : not found indicate that the script cannot locate the Perl interpreter. Most of the scripts in UnixODBC.om (including CGI scripts) assume that the Perl binary is /usr/local/bin/perl. If it isn't, you can make a symlink from the actual Perl interpreter, or edit the first line in the scripts to the path of the Perl binary. - If a program seems to take forever to start, it generally indicates that the Bridge server can't make a connection to another machine on a network, and usually indicates that a network system is not accessible. - "Failed to make first connection...," to a DSN generally means that either the remote bridge server is not running, the client was not able to log in, or the perl and library versions of the systems are different. Check the installation and configuration of the bridge server (above and, "man odbcbrigde"), and make sure that you can make a connection to the remote DSN with a text-mode client like remotedsn ("man remotedsn") or remotetables ("man remotetables"). If the data manager can connect to local data sources but not remote data sources, the problem is likely in the RPC network layer. Ensure that the versions of Perl, Net::Daemon, Storable, and RPC::PlServer are the same on local and remote systems. Try connecting with one of the text mode clients (remotedsn, remotetables) to help diagnose the problem. The servers at all levels (DBMS server, HTTP server, bridge server) perform caching on the local and remote machines. It might be necessary to restart both systems, especially if you change the configuration of one or more systems, or upgrade the software. If you started the bridge server in the background, remember to re-start it if you change the system configuration. - If you receive, "Unexpected EOF while waiting for server reply," errors while logging into a DSN, try restarting the bridge server from a terminal session. - "Out of memory during large request," and "Maximum message size exceeded," errors occur when the size of a result set is greater than 64 kb. This is because the bridge server uses integers as packet indexes. Try to narrow the SQL query to produce a smaller result set. - Error and/or status messages do not show up in the system logs. The clients, especially the Web data manager, can record activity by the UnixODBC bridge server, the ODBC Driver Manager, and the HTTP server in up to three (that's right, three) different system logs. 1. UnixODBC bridge servers and clients use the syslog, "daemon," facility to record activity. If daemon logging is not enabled (i.e., there is no, "daemon.log," file), typically add a line like this to /etc/syslogd.conf, for example (on Linux systems): daemon.* /var/log/daemon.log Refer to the man pages for syslogd, syslogd.conf, and/or syslog.conf depending on the Unix version. If the 'debug' option in /usr/local/etc/odbcbridge.conf is non-zero the ODBC server generates additional status and error messages. Remember to restart the syslog daemon after making configuration changes. 2. The functions dm_log_open() and dm_log_close() in UnixODBC.pm and UnixODBC::BridgeServer.pm begin and end the logging of the ODBC run-time library. 3. The HTTP server records HTTP and CGI script errors server's error log. - Authorship and Copyright UnixODBC.pm is written by Robert Kiesling, rkies@cpan.org. Copyright © 2002-2004 by Robert Kiesling and licensed using the same terms as Perl. Refer to the file, "Artistic," for the details. $Id: README,v 1.12 2004/02/08 02:02:54 kiesling Exp $