Develop Perl applications with Apache Derby

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Introductory

Moira Casey (caseymo@us.ibm.com), Quality Assurance, IBM

07 Oct 2004

Learn to use Perl, Open Database Connectivity (ODBC), and the Perl Database Interface (DBI) to access data in an Apache Derby database. This article covers the installation of Perl, the ODBC driver, the Perl DBI, and Perl DBD-ODBC modules on Windows®, and includes a sample Perl script that demonstrates connecting to, inserting data into, and selecting data from a Derby database.

Overview

To access databases in Perl, the Perl database interface (DBI), an open source module for Perl, is required. DBI supports Perl's integration of third-party databases, such as IBM® Cloudscape™ or Derby. DBI defines methods, variables and conventions for database access. To access a specific database, a Perl Database Driver (DBD) is required in addition to the DBI. Numerous DBDs are available for a variety of databases and access to each database requires using the DBI and the database specific DBD. A different way to access a database using Perl is to use the DBI and the DBD-ODBC module which uses ODBC instead of the native DBD. Presently there is no DBD specific to Derby available, therefore access to a Derby database requires using the DBD-ODBC module . Derby gets it's ODBC support through the IBM DB2® Run-time Client.

IBM Cloudscape is the commercial release of the Apache Derby open source database.

Perl is a versatile, powerful, scripting language which is frequently used in Web development. Accessing a Derby database from Perl is relatively easy given the support for databases in Perl. Using ODBC along with the Perl DBI and DBD-ODBC modules allows access to a Derby database from Perl.

Throughout this article you build upon each step of installing and configuring the components involved in accessing a Derby database from Perl. First install and configure the IBM DB2 ODBC driver to connect to and create an ODBC data source for a Derby database. Next, learn how to install Perl, the Perl DBI and the Perl DBD-ODBC module on Windows. At this point all the steps will be in place to create a Perl script which accesses a Derby database. The article concludes with a sample script that shows this.

Back to top

Software requirements

The software described in this section is available for download at no cost.

Install one of the database options listed below:

1. Cloudscape 10.0 (this includes the IBM DB2 Java Database Connectivity (JDBC) Universal Driver)
2. Apache Derby and the IBM DB2 JDBC Universal Driver

Additionally, install all software listed below:

• Perl The instructions in this technical article were tested with ActivePerl version 5.8.4.810.
• IBM DB2 ODBC Using ODBC with Cloudscape is currently a beta configuration.
• The Perl DBI module. The instructions in this technical article were tested with the 1.4.3 Perl DBI module.
• The Perl DBD-ODBC module. The instructions in this technical article were tested with the 1.10 Perl DBD-ODBC module.

Back to top

Configure the IBM DB2 ODBC client

To use the IBM DB2 ODBC driver with a Derby database, the IBM DB2 client software needs to know where the Derby database is and what its name is, and it also needs to know that it is an ODBC data source. This section shows how to provide all this information by cataloging the node.

The instructions in this section assume the following:

• The Derby Network Server runs on the default port of 1527.
• You created a Derby database named DB1.

First, identify the host and port that the remote database is running on. In this case, the Derby database is on the local machine ("localhost") and the Network Server is listening on the default port of 1527. Next provide a name for this node; for example purposes, call it CNS.

Open a db2cmd window and catalog the CNS node to access localhost on port 1527, as shown below:

db2 catalog tcpip node CNS remote localhost server 1527

Next, provide the name of the database to access on the CNS node, as shown below:

db2 catalog db DB1 at node CNS authentication server 

Finally, catalog the database as an ODBC data source. You can do this using the Windows ODBC Data Source Administrator, or by using the CLP command shown below:

db2 catalog system odbc data source DB1

To check if the previous steps worked correctly to create the ODBC data source for the DB1 database issue this command:

db2 list system odbc data sources

If the steps did work correctly you will see confirmation of the DB1 Data source name existing of type IBM DB2 ODBC DRIVER.

The commands shown above will fail if the node or database is already catalogued. If you experience any failures, the DB2 UDB commands below show how to uncatalog them and go back to the very beginning:

db2 uncatalog node CNS
db2 uncatalog db DB1

For a complete description of the IBM DB2 UDB catalog command, see the IBM DB2 Universal Database Command Reference.

Verify ODBC works with Derby

A quick way to determine if ODBC can connect to the Derby Data Source you just set up, DB1, use the ODBC Data Sources tool in Windows. To access this tool in Windows XP go to Start --> Control Panel --> Administrative Tools --> Data Sources (ODBC). This will bring up the ODBC Data Source Administrator. Select the 'System DSN' tab and if you cataloged the DB1 Data Source correctly you'll see it in this list.

Highlight the DB1 under the 'Name' column and click the 'Configure...' button on the left. You'll see a window appear similar to the image below.

With the Cloudscape Network Server running on the localhost at port 1527 fill out the 'User ID' and 'Password' values, then click the Connect button to test the connection to the Cloudscape Data Source, 'DB1'. If the connection is successful a pop-up box will appear informing you it was.

Back to top

Install Perl

Go to http://www.perl.org/. Select 'Get Perl', and then under the 'Download Perl Distributions' section pick 'ActivePerl for Windows,Solaris, Linux'. Click the Download link on the next page, register and then download the zip file to a temporary directory.

Unzip the ActivePerl-5.8.4.810-MSWin32-x86.zip file into a directory. Run the installer.bat file once you have unzipped it. Do not install the 5.8.x version of ActivePerl on a system where 5.6 is already installed. There is additional information about installing ActiveState Perl in the RELEASE.txt document where you unzipped ActivePerl 5.8.

Make sure you have the Perl 5.8 version at the beginning of the PATH environment variable. Otherwise, you may pick up an older Perl version, such as the version of Perl included in the MKS package.

The configuration for Perl is now complete. Now execute a simple script from the command line to test the set up.

Back to top

Test Perl from the command line

The code in Listing 1 is a simple Perl script to test your Perl environment.

# hello-world.pl

use strict;

print "Where would we be without Hello World?";

Copy the contents of Listing 1 into a file called hello-world.pl and save it to any directory. Make sure perl.exe is in your path before you execute the script. Here is the command to run the hello-world.pl file and the output from running the script.

C:\temp>perl hello-world.pl
Where would we be without Hello World?

Back to top

Install the DBI module

The Perl DBI Module is the database interface for Perl. It is required for all database connectivity to third party databases from Perl. To download, install, and configure the module use the Perl Package Manager (ppm) included with the ActiveState perl distribution you just installed. The ppm needs to access the internet to download and install the packages. If you have firewall software installed you may need to set some environment variables to use ppm correctly. There is a note following the install directions on these variables if they are required to get ppm to install the DBI module.

Using the ppm to install the DBI

• Open a command window and test you are using the correct version of perl by entering perl -version. The perl release should say 5.8.4 if you just installed the version mentioned above. Release 5.6 or higher is required to get everything in this article to work.
• Next enter ppm at the prompt to bring up the Perl package manager included with ActiveState Perl.
• To install the DBI module just enter install DBI at the ppm prompt.
• That's it! If you do have problems, see the FAQ about installing the DBI on Windows at the dbi.perl.org site.

Here is partial output of the successful ppm session which installed the Perl DBI.

ppm> install DBI
====================
Install 'DBI' version 1.43 in ActivePerl 5.8.4.810.
====================
Downloaded 513616 bytes.
Extracting 69/69: blib/arch/auto/DBI/Driver_xst.h
Installing C:\tools\perl\site\lib\auto\DBI\dbd_xsh.h
Installing C:\tools\perl\site\lib\auto\DBI\DBI.bs
Installing C:\tools\perl\site\lib\auto\DBI\DBI.dll
...
...
...
Installing C:\tools\perl\bin\dbiprof.bat
Installing C:\tools\perl\bin\dbiproxy
Installing C:\tools\perl\bin\dbiproxy.bat
Successfully installed DBI version 1.43 in ActivePerl 5.8.4.810.

You may need to set these environment variables if you are running firewall software and the ppm can not download the Perl Modules.

Table 1. Environment Settings if PPM can not download the DBI Module

Back to top

Install the DBD-ODBC driver

The next step in fitting all of the pieces together is to get the DBD-ODBC driver to use with the DBI module. Since you have support for ODBC in our Derby database through the DB2 Run-time Client, use the DBD-ODBC driver to talk to the DBI module you just installed. The Perl Package Manager, ppm, will be used again to install the DBD-ODBC Version 1.1.0.

• Open another command prompt and type ppm.
• Now enter install DBD-ODBC

Here is the sample output from running this command.

ppm> install DBD-ODBC
====================
Install 'DBD-ODBC' version 1.10 in ActivePerl 5.8.4.810.
====================
Downloaded 76744 bytes.
Extracting 11/11: blib/arch/auto/DBD/ODBC/ODBC.lib
Installing C:\tools\perl\site\lib\auto\DBD\ODBC\ODBC.bs
Installing C:\tools\perl\site\lib\auto\DBD\ODBC\ODBC.dll
Installing C:\tools\perl\site\lib\auto\DBD\ODBC\ODBC.exp
Installing C:\tools\perl\site\lib\auto\DBD\ODBC\ODBC.lib
Installing C:\tools\perl\html\site\lib\DBD\ODBC.html
Installing C:\tools\perl\html\site\lib\DBD\ODBC\Changes.html
Files found in blib\arch: installing files in blib\lib into architecture depende
nt library tree
Installing C:\tools\perl\site\lib\DBD\ODBC.pm
Installing C:\tools\perl\site\lib\DBD\ODBC\Changes.pm
Successfully installed DBD-ODBC version 1.10 in ActivePerl 5.8.4.810.

Back to top

DBI architecture and syntax

The DBI architecture relies on the DBI programming interface to forward the DBI calls to the Database Driver, in this case the DBD-ODBC. One of the main goals of the DBI is to make the syntax for accessing databases be database independent. The DBI, when used with the appropriate driver, can be used to access multiple databases (even simultaneously if you wish!), using the same standard syntax.

Some of the common DBI conventions and syntax are listed in the table below.

Table 2. DBI conventions and syntax

Back to top

Test the connection to the Derby Network Server

Before running our Perl script to connect to a Derby database, make sure the connection to the Derby Network Server using the DB1 database and ij, the Derby SQL scripting tool, is successful. If ij doesn't work, your Perl script isn't likely to work either. The java command for starting ij on the Network Server is shown below, but for readability, it is split across several lines:

C:\>java -Dij.driver=com.ibm.db2.jcc.DB2Driver 
      -Dij.protocol=jdbc:derby:net://localhost:1527/ 
      -Dij.user=APP -Dij.password=APP  org.apache.derby.tools.ij
ij version 10.0 (C) Copyright IBM Corp. 1997, 2004.
ij> connect 'DB1';

If the ij command fails, check:

• Is your CLASSPATH correctly set?
• Did you start the Network Server?

If the ij test succeeded, you are ready to run the perl script which connects to the Derby database.

Back to top

Perl, DBI, DBD-ODBC, ODBC and Derby!

This section uses all the components you have configured and built on. The sample perl script in this section uses the DBI to make calls to the DBD-ODBC perl driver, and in turn this driver will use the DB2 ODBC driver to contact the Derby Network Server which connects to the Derby database, DB1.

Listing 2, is shown below which contains a sample Perl script that creates a table, inserts and fetches rows from it, and then drops the table and disconnects from the database. The database, named DB1, was defined earlier in Window's ODBC Data Source manager.

Copy the contents of this Listing to a file called perlExample.pl.

#perlExample.pl

use DBI;
use strict;

my $data_source = "DBI:ODBC:DB1";
my $user = "dbadmin"; my $password = "testpw";

# Create a database connection handle. Autocommit is On.
# $DBI::errstr - returns the native database engine error 
# message from the last DBI method called
my $dbh = DBI->connect($data_source, $user, $password, {AutoCommit =>1})
		or die "Can't connect to $data_source: $DBI::errstr";

# prepare a single CREATE SQL statement
my $sql = qq( CREATE TABLE STAFF(name VARCHAR(10),job VARCHAR(10)));

# execute the CREATE statement. The do() method can be used for 
# non repeated non-SELECT statement.
$dbh->do($sql);

# prepare a single INSERT SQL statement containing literal values. Inserts 2 rows.
my $sql = qq(INSERT INTO STAFF( name, job)
VALUES ( 'Pearce', 'Clerk'), ('Hachey', 'Mgr'));

# execute the INSERT statement
$dbh->do($sql);

# prepare an INSERT SQL statement that contains parameter markers. Inserts one row.
# This is an example of a SQL statement being prepared once. The bind values 
# for each row are provided to the execute method each time it is called. 
# The statement does not need to be re-prepared for each row. The application 
# typically runs faster. 
my $sth = $dbh->prepare(q{INSERT INTO STAFF (name, job) VALUES ('Smith', ?)})
|| die $dbh->errstr;

# bind the parameter value 
my $value = "President";
$sth->bind_param(1, $value);

# execute the INSERT statement
my $rc = $sth->execute
or die "Can't execute statement: $DBI::errstr";

# prepare a SQL SELECT statement
my $sth = $dbh->prepare( q{SELECT name, job FROM STAFF})
or die "Can't prepare statement: $DBI::errstr";

# execute the SELECT statement
my $rc = $sth->execute
or die "Can't execute statement: $DBI::errstr";

# Use Statement Handle Attributes, NUM_OF_FIELDS, NAME.
# NUM_OF_FIELDS  (This attribute is the number of fields (columns) in 
# the data the prepared statement will return.)
# Statements that don't return rows of data, like DELETE and CREATE 
# set the NUM_OF_FIELDS attribute to 0.
# NAME  (This attribute returns a reference to an array of field names 
# for each column.)

my $name = "";
my $job = "";
print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
print "Field names: @{ $sth->{NAME} }\n";

# The fetchrow_array utility method fetches the next row of data and 
# returns it as a list containing the field values.
	while (($name, $job) = $sth->fetchrow_array) {
		print "$name | $job\n";
}

# prepare a SQL DROP statement
my $sth = $dbh->prepare( q{
DROP TABLE STAFF;
}) or die "Can't prepare statement: $DBI::errstr";

# execute DROP statement
my $rc = $sth->execute
or die "Can't execute statement: $DBI::errstr";

# teminate the connection
$dbh->disconnect;

Now open a command window and with perl.exe in your path enter perl perlExample.pl.

If all goes well you should see output like this:

C:\perl_test>perl perlExample.pl
Query will return 2 fields.

Field names: NAME JOB
Pearce | Clerk
Hachey | Mgr
Smith | President

If your script did not work correctly check the following:

• Is the Derby Network Server started?
• Did you install all of the prerequisite software?
  • Derby or Cloudscape
  • DB2 Universal JDBC driver (required for Derby only)
  • DB2 ODBC support
  • Perl
  • Perl DBI module
  • Perl DBD-ODBC driver

Back to top

Summary

This article showed you how to install and configure Perl, described how to catalog a Derby node for ODBC using the IBM DB2 ODBC driver, described how to install the Perl DBI and DBD-ODBC modules and demonstrated how to perform basic database operations with Perl.

Resources

• Learning Perl, 2nd Edition, Chapter 1 (O'Reilly, 1997) is a great introduction to Perl.
  
  
• An excellent introductory article on the Perl DBI, can be find at perl.com, A Short Guide to DBI, Perl's Database Inteface Module.
  
  
• Share your questions and views on this article with the author and other readers in the IBM Cloudscape discussion forum.
  
  
• To learn more about IBM Cloudscape, visit the developerWorks Cloudscape zone. You'll find technical documentation, how-to articles, education, downloads, product information, and more.
  

About the author

		Moira Casey is an Advisory Engineer in DB2. For the past six years she has been a test engineer for DB2, and more recently, Cloudscape.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top