StarQuest Technical Documents

Using StarSQL with the Perl DBI Module

Last Update: 05 February 2009
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ013

Abstract

Perl, or Practical Extraction and Reporting Language, is an open source programming language. It is often used to create CGI programs and other web scripts, and is particularly useful for Web forms. StarSQL can be used with Perl, DBI (database interface module for Perl), and the DBD::ODBC module to access DB2 databases.

This document explains how to set up and use StarSQL and unixODBC with Perl on UNIX platforms. The same concepts should apply to using Perl on Windows. This document also provides instructions on how to create and run a CGI application written in Perl on an Apache HTTP Server.

Run all UNIX commands as root user.

Prerequisites

• Perl (tested with Perl 5.8.x, although other versions may work)

Most UNIX distributions include Perl, and you can run the following command to verify that it is installed:

# perl -v

If Perl is not installed, obtain it from either the UNIX distribution media, your UNIX vendor, or the Comprehensive Perl Archive Network, http://www.cpan.org (browse the binary distributions). You may also download ActivePerl from http://www.activestate.com.

• DBI module (DBI 1.48 tested although other versions may work)
• DBD::ODBC module (tested with DBD::ODBC 1.13, although other versions may work)

Some UNIX distributions supply DBI and DBD::ODBC. However, in many cases you will need to build and install DBI and DBD::ODBC from source, which may be downloaded from http://www.cpan.org. To determine whether DBI and DBD::ODBC are installed, run the following commands:

# perl -e 'use DBI;'
# perl -e 'use DBD::ODBC;'
# perl -MDBD::ODBC -e 'print %DBD::ODBC::VERSION;'

If DBI and DBD::ODBC are not installed, follow the procedures below to build both modules.

• Apache HTTP Server (tested with Apache 2, although other versions may work)

The 32-bit version of StarSQL should only be used with the 32-bit versions of perl, DBI, and DBD::ODBC. Use StarSQL (64-bit) with the 64-bit versions of perl, DBI, and DBD::ODBC.

HP-UX Considerations

HP supplies two different binaries for perl in /opt/perl_32/bin: perl-static and perl-dynamic. The default (symbolic link from /usr/bin/perl) is perl-static, which has SHLIB_PATH  disabled. For best results, we recommend using perl-dynamic instead of perl-static.

Solution

This solution explains how to set up and use StarSQL and unixODBC with Perl on UNIX platforms. The steps that you need to perform, which are explained in detail in the sections that follow, are:

1. Build the DBI module.
2. Build the DBD::ODBC module.
3. Verify that both the DBI and DBD::ODBC modules are ready for use.
4. Create and execute a stand-alone application written in Perl.
   
5. Configure the Apache HTTP Server to use StarSQL.
6. Create a CGI web application written in Perl and run it on the web server.
   

Build the DBI Module

1. To build the DBI module, run the following commands.

# perl Makefile.PL
# make
# make test
# make install

Build the DBD::ODBC Module

1. Set the DBI_DSN, DBI_USER, and DBI_PASS environment variables to values that the DBD::ODBC tests can use to verify the database connection during the build process. Run the following commands, replacing mydsn, myuser, and mypassword with the appropriate values for the ODBC data source, DB2 userID, and DB2 password, respectively.

   (using csh)

   # setenv DBI_DSN dbi:ODBC:mydsn
   # setenv DBI_USER myuser
   # setenv DBI_PASS mypassword

2. You must indicate where the ODBC Driver Manager is installed before you can build the DBD::ODBC module. We recommend that you use the unixODBC driver manager, which is supplied with all StarSQL for UNIX distributions. Run the following command to configure Perl with the location of the unixODBC driver manager.

# perl Makefile.PL -o $STARSQL/odbc

3. Run the following commands to build the DBD::ODBC module:

# make
# make test
# make install

Verify that the DBI and DBD::ODBC Modules are Ready for Use

1. Verify that the DBI and DBD::ODBC modules are ready for use by running the following commands:

# perl -e 'use DBI;'
# perl -e 'use DBD::ODBC;'
# perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'

If the modules were built successfully, the output from the last command will be the version of the DBD::ODBC module, such as shown in the following example:

# perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;' 1.13#

Create and Execute a Standalone Application Written in Perl

The following short program will fetch the contents of a table and display the result set.

1. Copy the code provided below and paste it into a text editor.

use strict;
use DBI;

my $dbh = DBI->connect('dbi:ODBC:mydsn', 'myuser', 'mypassword',
                {AutoCommit => 0});

my $sql = qq/select * from MYTABLE/;  # the query to execute
my $sth = $dbh->prepare($sql);        # prepare the query
$sth->execute();                      # execute the query
my @row;
while (@row = $sth->fetchrow_array) { # retrieve one row
    print join(", ", @row), "\n";
}

$dbh->disconnect if ($dbh);

2. Modify the code that you pasted into the text editor, replacing mydsn, myuser, and mypassword in the connection string to values appropriate for your environment. In addition, change the table name from MYTABLE to a name that is valid for a table in your database.
3. Save the file as test.pl.
4. Enter the following command to execute the application:

# perl test.pl

Configure the Apache Server to Use StarSQL

The procedure outlined below was tested with the Apache2 HTTP Server running on Linux.

The apache environment must be configured with the location of the unixODBC Driver Manager to use with StarSQL, whether it be an existing, supported unixODBC installation or the one included with StarSQL.

1. Log on as root user.
2. Locate the apache configuration file (e.g., /usr/lib/apache2/build/envvars on AIX, /etc/sysconfig/apache2 on SUSE, /etc/apache2/envvars on Ubuntu). Refer to the apache2 documentation for your platform if necessary.
3. Set the library path environment variable (LD_LIBRARY_PATH, LIBPATH, etc.) in the apache configuration file. For example:

   32 bit: export LD_LIBRARY_PATH=/usr/share/starsql/odbc/lib

   64 bit: export LD_LIBRARY_PATH=/usr/share/starsql64/odbc/lib

4. Restart the apache server (e.g., /etc/init.d/apache2 restart).

Create a CGI Web Application Written in Perl and Run It on the Web Server

The following short program will fetch the contents of a table and display the data in the first two columns.

1. Copy the code provided below and paste it into a text editor.

#!/usr/bin/perl -w
use DBI;

# it is recommended that the application set the environment variable HOME
$ENV{HOME} = '/var/www';

# declare the content-type for the web server to use

print "content-type: text/html\n\n";
print '<html><head><title>Basic CGI</title><head><body>';

my $dbh = DBI->connect('dbi:ODBC:mydsn', 'myuser', 'mypassword',
                {AutoCommit => 0});

my $sql = qq/select * from MYTABLE/;   

# the query to execute
my $sth = $dbh->prepare($sql);
$sth->execute;
print '<table border="1">';

# table headings are SQL column names

print "<tr><th>$sth->{NAME}->[0]</th><th>$sth->{NAME}->[1]</th></tr>";
while (my @row = $sth->fetchrow_array) {
    print "<tr><td>$row[0]</td><td>$row[1]</td></tr>\n";
}
print " </table>\n";
print " </body></html>\n";

# a disconnect statement is required

$dbh->disconnect;

2. Modify the code that you pasted into the text editor, replacing mydsn, myuser, and mypassword in the connection string to values appropriate for your environment. In addition, change the table name from MYTABLE to a name that is a valid table in your database.
3. Save the file as webtest.pl.
4. Copy or move the webtest.pl file to the cgi-bin directory (i.e., /var/www/cgi-bin or /srv/www/cgi-bin).
5. Run the following command to make the file executable:

# chmod 755 webtest.pl

6. Run the application by pointing a Web browser to the URL: http://mywwwserver/cgi-bin/webtest.pl.

Troubleshooting

If the Web application cannot locate Perl, use the command " which perl" to determine the installed location of Perl and modify the first line of the sample Web application (webtest.pl) if it is a location other than /usr/bin.

For errors related to the Web server, examine the Web server error log (i.e., /var/log/httpd/error_log).

To assist with troubleshooting problems with Perl applications running on IIS, run the application in debug mode. Rename the perl file to nph-<appname>.pl and run the application. "nph" stands for "no-parsed-header" script, and it is a directive to run in debug mode which will produce more useful error messages. 

If you encounter an error related to the database or the database queries, enable ODBC tracing by editing /usr/local/etc/odbcinst.ini (on UNIX/Linux) or use the Windows ODBC tracing tool (in the ODBC Data Source Administrator). After reproducing the problem, examine the ODBC trace log.

References

http://dbi.perl.org
DBI home page

http://www.cpan.org
CPAN where you can download DBI and DBD::ODBC

http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html
Perl DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection

http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your database

http://www.easysoft.com/developer/languages/perl/tutorial_data_web.html
Perl DBI - Put Your Data On The Web

http://www.devshed.com/c/b/Perl/
Developer Shed

http://www.software.ibm.com/data/db2/perl
(Note that we recommend using the DBD::ODBC interface rather than DBD::DB2; the latter may work with StarSQL, but has not been tested.)

You can get help with Perl DBI from the dbi-users-subscribe@perl.org mailing list and help on dbi-users-help@perl.org