Programming for Oracle on Linux, Part I
By far, the most common RDBMS platforms for Linux-based systems are MySQL and PostgreSQL, due to their inclusion in most major Linux distributions. Quite a few shops still run legacy databases, however, using commercial database servers such as Sybase or Oracle. Even Linux Journal gave its 2001 Editors' Choice Award for Best Database to Oracle. As such, it is not uncommon for a programmer to be asked to put together an application to interact with these legacy databases.
Perl, the duct tape of the Internet, is well suited for use in CGI applications. Thanks to the Perl DBI module, available on CPAN, database interaction with Perl is quite simple.
To start, you need to install the Oracle client software with at least the development libraries. Installation of the Oracle client software is outside the scope of this article, but a plethora of information is available on-line about installing Oracle on Linux. Don't worry about the database server-specific instructions and kernel tuning; all we need is the development environment.
Once the Oracle client has been installed, it is time to install the Perl DBI and DBD::Oracle modules. Also, if you do not have it installed already, you need to install the Getopt::Long Perl module. The easiest way to install these modules is through CPAN. As root, run the following command:
[root@localhost root]# export ORACLE_HOME=/path/to/oracle [root@localhost root]# perl -MCPAN -e shell cpan shell -- CPAN exploration and modules installation (v1.61) ReadLine support enabled cpan> install DBI ... cpan> install DBD::Oracle ... cpan> install Getopt::Long
With our Perl modules and the Oracle client installed, we now can start writing our code.
For this program, we want to be able to query some data from a database containing some customer information. For purposes of the example, our customer_data table contains the username, first and last name, address, city, state, zip code, phone number, age and account status code. An example of the table definition is shown in Listing 1.
Listing 1. Sample Table Definition
Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL VARCHAR2(16) FIRST_NAME NOT NULL VARCHAR2(255) LAST_NAME NOT NULL VARCHAR2(255) ADDRESS_LINE1 NOT NULL VARCHAR2(255) ADDRESS_LINE2 VARCHAR2(255) CITY NOT NULL VARCHAR2(255) STATE NOT NULL VARCHAR2(2) ZIPCODE NOT NULL VARCHAR2(255) PHONE VARCHAR2(12) AGE NUMBER STATUS NUMBER
The Perl code for a simple utility either to query or add a new record to our customer_data table is shown in Listing 2.
Listing 2. Script to Query or Add a Record
#!/usr/bin/perl
#############################################################################
use strict;
use warnings;
use DBI;
use Getopt::Long;
#############################################################################
# Configuration section
#############################################################################
# Database type (oracle or mysql)
#my $db_type = "mysql";
my $db_type = "oracle";
# Database information for mysql db_type
my $mysql_host = "localhost";
my $mysql_user = "sqluser";
my $mysql_passwd = "sqlpass";
my $mysql_db = "testdb";
# Database variables for oracle db_type
my $ora_host = "localhost";
my $ora_user = "sqluser";
my $ora_passwd = "sqlpass";
my $ora_sid = "testdb";
#############################################################################
# Variables
#############################################################################
use vars qw ( $dbh $create_mode $query_mode $username $first_name $last_name $addr1 $addr2 $city $state $zipcode $phone $age $verbose $print_help );
#############################################################################
# Start of program execution
#############################################################################
# Check command line options
&check_options();
# Connect to the database
&database_connect();
# Perform the requested action
if ($query_mode) {
&query_table();
}
elsif ($create_mode) {
&add_record();
}
# Disconnect from the database
&database_disconnect();
# Exit
exit(0);
#############################################################################
#
# Subroutine: database_connect
# Description:
#
# Connect to the database
#
#############################################################################
sub database_connect {
if ($db_type eq "mysql") {
$dbh = DBI->connect("DBI:mysql:database=$mysql_db;host=$mysql_host",
$mysql_user, $mysql_passwd)
or &db_error("Error: Cannot connect to $mysql_db db on $mysql_host");
}
elsif ($db_type eq "oracle") {
$dbh = DBI->connect("DBI:Oracle:host=$ora_host;sid=$ora_sid",
$ora_user, $ora_passwd)
or &db_error("Error: Cannot connect to $ora_sid schema on $ora_host");
}
else {
print "Error: unsupported database type: $db_type\n";
exit(1);
}
}
#############################################################################
#
# Subroutine: database_disconnect
# Description:
#
# Disconnect from the database
#
#############################################################################
sub database_disconnect {
if ($dbh) {
$dbh->disconnect;
}
}
#############################################################################
#
# Subroutine: db_error
# Description:
#
# Print any database errors, if they occur.
#
#############################################################################
sub db_error {
my $error_string = $_[0];
my $sql_string = $_[1];
print STDERR "A database error has occurred:\n";
print STDERR " $error_string\n";
if ($sql_string) {
print STDERR " SQL: $sql_string\n";
}
}
#############################################################################
#
# Subroutine: add_record
# Description:
#
# Add a new user to the database
#
#############################################################################
sub add_record {
my $sth; my $error;
$sth = $dbh->prepare("INSERT into customer_data (username, first_name, last_name, address_line1, address_line2, city, state, zipcode, phone, age, status) VALUES (\'$username\', \'$first_name\', \'$last_name\', \'$addr1\', \'$addr2\', \'$city\', \'$state\', \'$zipcode\', \'$phone\', $age, 1)");
if (!$sth) {
$error = $dbh->errstr;
&db_error("Error: $error");
}
if (!$sth->execute) {
$error = $sth->errstr;
$sth->finish;
&db_error("Error: $error");
}
$sth->finish;
print "Added $username successfully to the database.\n";
}
#############################################################################
#
# Subroutine: query_table
# Description:
#
# Query the database, returning information for all users
#
#############################################################################
sub query_table {
my $sth; my $error;
$sth = $dbh->prepare("SELECT username, first_name, last_name, address_line1, address_line2, city, state, zipcode, phone, age FROM customer_data WHERE status=1");
if (!$sth) {
$error = $dbh->errstr;
&db_error("Error: $error");
}
if (!$sth->execute) {
$error = $sth->errstr;
$sth->finish;
&db_error("Error: $error");
}
my $rows = $sth->rows;
if ($rows eq 0) {
print "No users exist.\n";
return;
}
my $numFields = $sth->{'NUM_OF_FIELDS'};
while (my $ref = $sth->fetchrow_arrayref) {
for (my $i = 0 ; $i < $numFields; $i++) {
if ($i eq 0) {
print "Username: $$ref[$i]\n";
}
elsif ($i eq 1) {
print "Name: $$ref[$i] ";
}
elsif ($i eq 2) {
print "$$ref[$i]\n";
}
elsif ($i eq 3) {
print "Address: $$ref[$i]\n";
}
elsif ($i eq 4) {
print " $$ref[$i]\n";
}
elsif ($i eq 5) {
print "City: $$ref[$i]\n";
}
elsif ($i eq 6) {
print "State: $$ref[$i]\n";
}
elsif ($i eq 7) {
print "Zip Code: $$ref[$i]\n";
}
elsif ($i eq 8) {
print "Phone: $$ref[$i]\n";
}
elsif ($i eq 9) {
print "Age: $$ref[$i]\n";
}
}
print "\n";
}
$sth->finish;
}
#############################################################################
#
# Subroutine: check_options
# Description:
#
# Check the command line options and set the appropriate
# variables accordingly.
#
#############################################################################
sub check_options {
my $result;
$result = GetOptions ("verbose" => \$verbose,
"create" => \$create_mode,
"query" => \$query_mode,
"user=s" => \$username,
"first=s" => \$first_name,
"last=s" => \$last_name,
"addr1=s" => \$addr1,
"addr2=s" => \$addr2,
"city=s" => \$city,
"state=s" => \$state,
"zip=s" => \$zipcode,
"phone=s" => \$phone,
"age=i" => \$age,
"help" => \$print_help);
if (!$result) {
&print_usage();
exit(1);
}
if ($print_help) {
&print_usage();
exit(0);
}
if (!$query_mode && !$create_mode) {
print STDERR "Please select either create or query mode.\n";
&print_usage();
exit(1);
}
elsif ($create_mode) {
if (!$username||!$first_name||!$last_name||!$addr1||!$city||!$state||
!$zipcode||!$age) {
print STDERR "Missing required information. Any options not in square\n";
print STDERR " brackets are required for create mode.\n";
&print_usage();
exit(1);
}
if (!$addr2) {
$addr2 = "";
}
if (!$phone) {
$phone = "";
}
}
}
#############################################################################
#
# Subroutine: print_usage
# Description:
#
# Print usage information
#
#############################################################################
sub print_usage {
print "Usage: $0 --query\n";
print " $0 --create --user <user> --first <first name> \\\n";
print " --last <last name> --addr1 <address line 1> \\\n";
print " [--addr2 <address line 2>] --city <city> \\\n";
print " --state <state> --zip <zip code> [--phone <phone>] --age <age>\n";
}
The first thing we do, at line 6 of our program, is import the DBI database access module. This module allows us to use various DBI drivers, such as DBD::Oracle and DBD::mysql, to access a RDBMS with Perl.
Next, at line 7, we import the Getopt::Long module. Getopt::Long allows us to use the GetOptions() subroutine to parse GNU extended command line options, such as --verbose and --help.
At line 15, we set the type of database to which we are connecting. This is not related directly to the DBI routines, but it lets us specify to ourselves the type of database to which we are connecting, in the event that we need to talk to multiple RDBMS flavors.
With lines 18 through 27, we set the database connection related variables, both for Oracle and for MySQL. These are used by the database_connect() subroutine to create our database session.
Line 34 declares other various variables we will be using throughout the program.
Lines 42 through line 59 contain the main section of the program. At line 42, we execute the check_options() subroutine, declared later, to parse the command-line options passed to the program. At line 45, we execute the database_connect() subroutine, which connects us to our database of choice and creates our database handle, stored in $dbh.
Lines 48 through 53 determine whether we are using query mode to read customer data from the database or create mode to create a new customer data record in the database. If $query_mode is set, which is discussed later on in check_options, we then execute the query_table() subroutine. Otherwise, if $create_mode is set, we execute the add_record() subroutine to create a new record in customer data.
Finally, at line 56, we execute our database_disconnect() subroutine to disconnect from the database, and exit with a status of 0.
Our database_connect() subroutine starts at line 72. We check the contents of $db_type. For a MySQL database type, we use the DBD::mysql DBI driver at line 75 to connect to the database with a DBI->connect() call. If $db_type is set to Oracle, we use the DBD::Oracle DBI driver at line 80 to connect to the database with a similar but slightly different call to DBI->connect(). If the $db_type variable doesn't match any of those cases, an error is generated and we exit with an unsuccessful return code. You can use the DBI->available_drivers method to print a list of DBI database drivers installed on your system.
Each DBI driver defines its own syntax for the first argument to DBI->connect--the data source argument. For example, our program uses:
$dbh = DBI->connect("DBI:Oracle:host=$ora_host;sid=$ora_sid",
$ora_user, $ora_passwd);
Another method determines the hostname of the Oracle database server using tnsnames:
$dbh = DBI->connect("DBI:Oracle",
"$ora_user/$ora_passwd@$ora_sid", "");
For more information, consult the DBD::Oracle manual page.
Lines 99 through 103 declare the database_disconnect() subroutine, which simply checks for a valid database handle ($dbh) and executes the $dbh->disconnect method. if one exists.
Lines 114 through 123 define our db_error() subroutine, which is used throughout the database code to detect and report database errors.
Our next subroutine, add_record(), is defined from line 135 to line 151. This is the subroutine called when the program is called in --create mode. At line 138, we use the $dbh->prepare() method to create a statement handle ($sth) that contains our SQL INSERT statement. This statement inserts a new row into customer_data with all of the data given on the command line, which we obtain later in check_options(). At line 143, after the statement handle has been validated, we call $sth->execute() to execute the SQL statement we prepared earlier. Lastly, we close the statement with $sth->finish() and print a message saying that we have created the new row successfully.
Lines 163 to 220 contain our largest subroutine, the query_table() subroutine. query_table() is called to obtain the contents of the customer_data table when the program is called in query mode. The query_table() subroutine is quite similar to add_record() but contains more code to read the data that we attempt to read from the database. Lines 166 through 175 are similar to those in the add_record() subroutine, but here, we instead use a SQL SELECT statement to read the data from the customer_data table. Lines 176 through 180 check the number of rows returned by our statement. If 0 results are returned, we simply print a message stating that no data exists and return to the main program. Next, we check the number of columns returned, stored in $sth->{'NUM_OF_FIELDS'} and store that in our own variable, $numFields.
Next, we cycle through each of the rows returned from the database with a while() statement, starting at line 184. For each cycle, we execute $sth->fetchrow_arrayref() to fetch an array reference containing our data and store it in a local variable, $ref. Starting at line 185, we cycle through each column returned in the row. Lines 186 through 215 determine which piece of data is given to us and prints it to standard output. Finally, at line 219 we call $sth->finish to complete our statement.
Our last major subroutine, check_options(), starts at line 234. check_options() wraps the Getopt::Long module's GetOptions() subroutine to parse the options passed to the program on the command line. Lines 236 through 249 contain the call to GetOptions, with a list of options to accept and variables to store the values of those options. At line 251, if GetOptions returns an error, we print our usage message with print_usage(), defined later, and exit unsuccessfully. At line 256, if the $print_help variable is defined with a --help option being passed on the command line, we print the usage message with print_usage() and exit successfully. Lines 261 through 281 determine whether --query or --create mode was passed on the command line. If --create mode was passed, it validates that all necessary information is given.
Finally, lines 295 through 302 define the print_usage() subroutine used by check_options() to print the usage message.
Interaction with a legacy Oracle database can be done simply in only a few hundred lines of code, using Perl, DBI and DBD::Oracle. With additional DBI database drivers, it is possible to use the same code to interact with many different database servers running multiple RDBMS flavors.
