StarQuest Technical Documents

Using StarSQL with the Perl DBI Module

Last Update: 22 August 2008
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 a 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)

StarSQL is a 32-bit driver and should be used with the 32-bit versions of perl, DBI, and DBD::ODBC. Do not use StarSQL with the 64-bit versions of perl, DBI, and DBD::ODBC.

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

  1. 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 below code 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);

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

# perl test.pl

Configure the Apache Server to Use StarSQL

The following tasks must be performed in order to successfully use Perl, unixODBC, and StarSQL in conjunction with a Web server. The procedure outlined below was tested with the Apache HTTP Server running on Linux.

  1. Copy the .odbc.ini and .swodbc.ini files from the StarSQL user's home directory and place them in the home directory of the Apache user. Use "grep apache /etc/passwd" to determine the home directory of the Apache user (e.g., /var/www on Fedora; /var/lib/wwwrun on SUSE).
  2. Verify that the directories $STARSQL/lib and $STARSQL/odbc/lib are in the shared library path available to all users (including the Apache user). This can be accomplished using ldconfig:
    1. Create a text file /etc/ld.so.conf.d/starsql.conf that contains the following:

    /usr/share/starsql/lib
    /usr/share/starsql/odbc/lib

    1. Rebuild the ld.so cache by running the following command.

    # /sbin/ldconfig

  1. Enter the following command to restart Apache.

# /etc/init.d/apache restart

  1. If you are running StarSQL 5.3x or later, create a symbolic link to libICONV.so in /usr/lib by running the following command:

# ln -s $STARSQL/lib/libICONV.so /usr/lib

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 below code and paste it into a text editor.

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

# it is recommended that the application set the environment variables HOME and STARSQL
$ENV{HOME} = '/var/www';
$ENV{STARSQL} = '/usr/share/starsql';

# 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";

  1. 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.
  2. Save the file as webtest.pl.
  3. Copy or move the webtest.pl file to the cgi-bin directory (i.e., /var/www/cgi-bin or /srv/www/cgi-bin).
  4. Run the following command to make the file executable:

# chmod 755 webtest.pl

  1. 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).

If you encounter an error related to the database or the database queries, enable ODBC tracing by editing /usr/local/etc/odbcinst.ini. 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

DISCLAIMER

The information in technical documents comes without any warranty. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The information in technical documents may be gathered from various sources, including IBM, Microsoft, and other organizations.