Networking, ODBC, & Perl

In this article, I first describe a Perl API for the open-source unixODBC libraries (http://www.easysoft.com/) and then a peer-to-peer API that lets ODBC clients access the data sources of other systems on a TCP/IP network. The source code that implements these APIs is available electronically; see "Resource Center," page 5.

Perl's XSUB API

The primary problem in providing a Perl interface to the ODBC Driver Manager libraries is that the ODBC function calls rely heavily on the ability to modify multiple parameters in one function call. As long as the client language is C/C++, this isn't much of a problem: A calling program simply passes the addresses of the variables to the ODBC function, as in the call to the ODBC SQLGetDiagRec function in Example 1.

Perl's memory management, however, does not let scripts allocate variables at will, nor does it let users easily specify buffer lengths or whether data is initialized/uninitialized by default. Further, attempts by the ODBC Driver Manager libraries to modify Perl variables without the cooperation of Perl's memory management can cause segmentation faults.

But Perl's XSUB API lets you allocate variables in C. Macros that attach those variables to Perl symbol-table variables (SVs) are accessible in Perl scripts. The Perl safemalloc() function allocates variables in C, which the XSUB interface can then use in function calls to the ODBC runtime libraries. The XSUB sv_setiv and sv_setpv macros let XSUB assign C-language variables to Perl integer and pointer variables, respectively. The macro ST(x) can refer to function parameters passed on the stack from a Perl script to an XSUB.

Listing One is an XSUB that allocates variables in C that the ODBC runtime library can access, then assigns those variables to Perl SVs after the function call. Perl's memory management takes care of freeing the space allocated to unused variables when there are no more references to them.

The main difference between the C API for ODBC and the one for Perl is that the Perl API lets the XSUB worry about multiple parameter references. Perl function calls do not need to use references to variables—they simply specify the variables themselves as function parameters (see Example 2).

The Perl client in Listing Two lets the ODBC Driver Manager check whether the libraries support ODBC Level 2 or ODBC Level 3 functions. The ODBC level of support by the Driver Manager in the environment is independent of the level of support provided by drivers for individual DBMS servers.

A Perl DBMS Client

The basic steps for querying a data source and returning the results using Perl are similar to those of ODBC clients written in other programming languages.

1. Create an environment handle.

2. Set the ODBC level.

3. Create a connection handle.

4. Connect to the data source.

5. Prepare and execute a query.

6. Retrieve the results.

7. Close the connection and deallocate the handles.

SQLTables (available electronically; see "Resource Center," page 5) is a Level 3 ODBC client that returns the names of the tables of a database. The ODBC function SQLTables queries the names of the tables, even for DBMSs that do not support the "show tables" query.

Finding the ODBC Libraries

Because the ODBC Driver Manager needs to find libraries for DBMS drivers and servers, you need to ensure that the calling program can determine which directories contain the libraries.

With Linux, you can list all of the directories in /etc/ld.so.conf, then run ldconfig, and the dynamic loader searches those directories regardless of what library search path is provided in the environment.

With UNIX systems in general, the LD_LIBRARY_PATH environment variable provides the search path in the environment. However, you need to take care that the search path is correct in the environment where each client, server, or proxy executes. In some cases, the program might not inherit the environment variables of a login shell if you run the server as a system daemon at startup.

One way to provide the library search path for Perl scripts is to set the path in Perl's %ENV hash:

$ENV{'LD_LIBRARY_PATH'} =

'/usr/local/lib:/usr/local/mysql/lib:/usr/lib';

Shell scripts that start ODBC clients as system daemons, as with any other daemons, probably need to set LD_LIBRARY_PATH in the script. Listing Three shows how to set the library search path in the shell script that starts an ODBC peer-to-peer bridge server. If the data is being returned through a web server, it is also necessary (in many cases) to provide the library search path there.

Handling Signals

Perl scripts are sensitive to signals from the operating system, many of which cause scripts to terminate unexpectedly. However, signal handlers are also useful when shutting down daemons, among other conditions.

When scripts terminate unexpectedly (for instance, when PostgreSQL servers reopen interprocess pipes), Perl clients can use a signal handler like this:

$SIG{PIPE} = sub{print "SIGPIPE: ". $!."\n"};

to catch the SIGPIPE errors sent by the operating system and print error messages, instead of terminating. When Perl scripts need to perform some cleanup work before terminating, they can define a subroutine to handle SIGTERM's. Example 3, for instance, is a SIGTERM handler used by a bridge server. It removes the .PID file before the daemon terminates, and the subroutine can easily perform other shutdown tasks as well. Some versions of UNIX, however, do not return from application-defined signal handlers. The application might also need to do the work that the operating system's handler would perform otherwise.

Bridges Using RPCs

Writing peer-to-peer clients and servers in Perl is simplified by the Perl Remote Procedure Call (RPC) library (http://www.cpan.org/). Once installed with its supporting libraries, RPC library modules provide objects and methods for writing network clients and server daemons that are the basis for the UnixODBC.pm peer-to-peer API.

Because the UnixODBC.pm module XSUBs already handle the ODBC runtime library's convention of modifying multiple variables in one function call, the bridge API can take advantage of Perl's ability to return multiple values in a list. Instead of using the C-language-style ODBC function call syntax (as in the call to SQLGetDiagRec), peer-to-peer clients call the functions as methods of a client object; see Example 4.

A Peer-to-Peer Bridge Server

The Perl ODBC peer-to-peer server is a subclass of the Perl library's RPC::PlServer. The bridge server code is implemented as the UnixODBC::BridgeServer.pm library module. Example 5 is the constructor for the BridgeServer objects. Because the BridgeServer class uses the standard RPC library facilities of Perl, it is mainly a wrapper for the RPC::PlServer superclass. Then, a Perl script can create a server daemon with a single call to its superclass function main(), as in:

&UnixODBC::BridgeServer::main;

A Peer-to-Peer Bridge Client

Listing Four is a client that fetches the names of DSNs from a remote host. The API for peer-to-peer function calls is implemented as a separate subclass of the Perl RPC server library, BridgeAPI, which defines the methods that a peer-to-peer client may call.

The hostname, user, and password arguments to the RPC::PlClient new() method provide login information for the remote server, and the username and password need not—and probably should not—be the same as the used by the sql_connect method to open a remote data source.

Logging and Error-Handling Functions

Logging for the server is handled by Perl's Net::Daemon libraries, and lets the server daemon use the system's syslog facility to record events or errors, or to log events to an application-defined log file. In normal use, the only message that the server writes to the syslog is its startup message, as in this log entry:

Jul 16 07:34:35 aardvark

UnixODBC::BridgeServer[318]: Server starting

Net::Daemon provides a way for methods to write notices, debugging messages, and error messages to the syslog. This logging is independent of the ODBC Driver Manager's logging, initiated by calling the dm_log_open function with the name of the application and the name of the log file. In the case of peer-to-peer clients, dm_log_open writes the log file on the server system, and the client needs to have write permissions in the remote directory.

Example 6 shows how to write an entry in the server log from the client. Methods defined by the client-server libraries, like Log, must be called by instances of class RPC::PlClient; while the ODBC API functions are called by its subclass, RPC::PLClient::Object::BridgeAPI.

mod_perl and CGI

The Web Data Manager in Figure 1, which has the ability to query remote DSNs, shows how the UnixODBC.pm module and the peer-to-peer bridge API can be used to easily build flexible, networked data-management applications (using Apache, in this case).

There is not much additional configuration that needs to be done to configure Apache. The server requires that mod_env and mod_ssi, which are part of the standard configuration, and mod_perl, which most implementors build into Apache, be installed.

The Data Manager uses server-side Includes to build the web pages in each frame and enable server parsing of HTML pages; standard Apache configurations require only that you include the directives in Example 7 in the httpd.conf file. The server-side Include directives shown here are only effective in the /usr/local/apache/htdocs/datamanager directory, where the HTML pages of the Data Manager reside.

Apache's mod_env

You also need to configure Apache with the library search path in the server environment, because it is the program that is executing the CGI scripts that make ODBC function calls. SetEnv LD_LIBRARY_PATH /lib:/usr/lib:/usr/local/lib:/usr/local/mysql/lib/mysql illustrates how to use the Apache SetEnv directive to set LD_LIBRARY_PATH for the CGI scripts.

In this example, the directories contain the libraries for MySQL as well as unixODBC. You'll need to adjust the search path to look in the directories of the system's DBMS server libraries and the unixODBC runtime libraries.

Displaying Log and Error Messages

CGI script errors are normally recorded in Apache's error_log file. To make them appear in the generated HTML as well, use the CGI::Carp library module with the FatalsToBrowser option, as in:

use CGI;

use CGI::Carp qw(fatalsToBrowser);

Most nonfatal error messages that a CGI script generates are viewable on the browser when they are printed to STDOUT. It's better to format the error messages as HTML. In the case of peer-to-peer client errors that occur when a bridge client can't connect to a server, you need to use Perl's error records to print the error messages that the CGI client generates. Example 8 shows the peer-client initialization and error-message generation when used in a CGI script.

When printing error results from ODBC calls, the UnixODBC::BridgeAPI's sql_get_diag_rec method can generate HTML output with the sequence of function calls from the CGI script to the DBMS. The subroutine to print ODBC errors, and an example use of the sql_get_diag_rec method, is in Example 9. In this case, the error messages are enclosed in <pre> tags to make them more readable in the browser.

A Web Data Manager

The complete datasources.cgi script (also available electronically) retrieves the data sources from each ODBC peer and generates the dynamic HTML page of DSNs and hosts. Another CGI script, tables.cgi, creates a form (where users enter query information), then prints the results of the query. The Data Manager is included in the UnixODBC.pm library package.

When logging-in to remote hosts, the application does not perform any special authentication. Peer-to-peer passwords are stored in plain text in the CGI scripts, and security considerations may require that you implement encrypted passwords or use the passwords in /etc/passwd if it's practical to maintain a consistent set of network user names and passwords for each ODBC bridge server.

The SQL query form is relatively standard, and the Data Manager simply formats the query output as HTML table rows beneath the form when retrieving a result set. The Data Manager doesn't do any further formatting of the result set, because it doesn't know in advance the number of rows or columns that a result set could include. The tables.cgi script is flexible enough, however, that it could include enhancements such as opening a new browser window for the result set, formatting the results based on data type, or downloading binary objects.

Conclusion

The two Perl APIs for unixODBC I've examined here let you use ODBC functions both on local hosts and on peer-to-peer network clients and servers. The APIs let ODBC clients and servers use the capabilities of networked UNIX systems to access data independently of the host operating system and DBMS server type, and they provide essential building blocks for complex, reliable, and platform-independent data-management applications.

DDJ

Listing One

SQLRETURN 
SQLGetDiagRec (handle_type,handle,rec_number,sqlstate,native,
               message_text,buffer_length,text_length_ptr)
        SQLSMALLINT handle_type;
        SQLHANDLE   handle
        SQLSMALLINT rec_number;
        char *sqlstate;
        SQLINTEGER native;
        char *message_text;
        SQLSMALLINT buffer_length;
        SQLSMALLINT text_length_ptr;

    PREINIT:
    SQLCHAR *st = (SQLCHAR*) safemalloc (buffer_length);
    SQLCHAR *text = (SQLCHAR*) safemalloc (buffer_length);
    SQLINTEGER *nat = (SQLINTEGER*) safemalloc (sizeof(int));
    SQLSMALLINT *len = (SQLSMALLINT*) safemalloc (sizeof(int));

        CODE:
        RETVAL = SQLGetDiagRec ( handle_type, handle, rec_number, st,
                                 nat, text, buffer_length, len );
        sv_setpv (ST(3), st);
    sv_setiv (ST(4), *nat);
        sv_setpv (ST(5), text);
        sv_setiv (ST(7), *len);
        OUTPUT:
                RETVAL

Back to Article

Listing Two

#! /usr/bin/perl
use UnixODBC qw(:all);
my $evh;                                   # Environment Handle
my $r;                                     # Return Value
my ($rerr, $state, $native, $text, $len);  # GetDiagRec
my ($desc, $desc_len, $attr, $attr_len);   # SQLDrivers

$r = SQLAllocEnv ($evh);
if (! defined $evh) {
    print "Could not allocate environment handle: $r\n";
    exit 1;
}

# Try a Level 3 function.
$r = SQLSetEnvAttr($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC3, 0);

# If not successful, try a Level 2 function.
if ($r != $SQL_SUCCESS) {
    $r = SQLDrivers ( $evh, $SQL_FETCH_FIRST, $desc, 255, $desc_len,
              $attr, 255, $attr_len );
    if (($r != $SQL_SUCCESS) || ($r != $SQL_SUCCESS_WITH_INFO)) {
    print "$r";
    exit 1;
    }
    print "ODBC Level 2 supported\n";
    exit 1;
}
# Try some other Level 3 functions.
$r = SQLFreeHandle ($SQL_HANDLE_ENV, $evh);
if ($r == $SQL_SUCCESS) {
    print "ODBC Level 3 supported.\n";
} else {
    $rerr = SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, 
                           $state, $native, $text, 255, $len);
    print "$text\n";
    exit 1;
}

Back to Article

Listing Three

#! /bin/sh
# 
# Edit LD_LIBRARY_PATH with all the directories of the libraries 
# that ODBC needs to find.
#
LD_LIBRARY_PATH="/usr/local/lib:/usr/local/mysql/lib:/usr/lib:/lib" 
export LD_LIBRARY_PATH
case "$1" in
  start)
    echo "Starting ODBC bridge... "
    /usr/local/sbin/server &
    ;;
  stop)
    echo "Stopping ODBC bridge... "
        kill `cat /usr/local/var/odbcbridge/odbcbridge.pid`
    ;;
  *)
    echo "Usage: odbcserver {start|stop}" >&2
    exit 1
    ;;
esac
exit 0

Back to Article

Listing Four

#! /usr/local/bin/perl
require 5.004;
use strict;

require RPC::PlClient;
use UnixODBC qw (:all);
use UnixODBC::BridgeServer;

my $client = 
    eval { RPC::PlClient->new('peeraddr' => 'remotehost', 'peerport' => 9999,
              'application' => 'RPC::PlServer',
              'version' => $UnixODBC::VERSION, 
                          'user' => 'kiesling', 'password' => 'password') }
    or print "Failed to make first connection: $@\n";
my $c = $client -> ClientObject ('BridgeAPI', 'new');
my $evh;  
my ($r, $sqlstate, $native, $text, $textlen);
my ($dsn, $dsnlength, $driver, $driverlength);
$evh =  $c -> sql_alloc_handle ($SQL_HANDLE_ENV, $SQL_NULL_HANDLE);
$r = $c -> 
    sql_set_env_attr ($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);
($r, $sqlstate, $native, $text, $textlen) = 
    $c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);
($r, $dsn, $dsnlength, $driver, $driverlength) = 
    $c -> sql_data_sources ($evh, $SQL_FETCH_FIRST, 255, 255);
print "$dsn, $driver\n";
while (1) {
($r, $dsn, $dsnlength, $driver, $driverlength) = 
    $c -> sql_data_sources ($evh, $SQL_FETCH_NEXT, 255, 255);
    last unless $r == $SQL_SUCCESS;
    print "$dsn, $driver\n";
}
$r = $c -> sql_free_handle ($SQL_HANDLE_ENV, $evh);
($r, $sqlstate, $native, $text, $textlen) = 
    $c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);

Back to Article