Useful shortcut for simple database applications.
Connects to the database and executes a given SQL statement, substituting given parameters for placeholders.
If the statement is a SELECT query, returns an array ref of rows.
Set up to work with Oracle, but will probably work with any DBI-compatible database by changing the connection string.
############################################################
# exec_sql
#
# Executes an SQL statement with the given parameters in
# place of ?'s
#
# Syntax:
# $aref = &exec_sql( $statement, \@parameters, \$db_error);
#
# Pre: $statement is a valid SQL statement (may be with
# placeholders)
# \@parameters is a reference to an array of
# parameters to put in place of placeholders
# \$db_error is a reference to an error string
#
# Post: If $statement was a SELECT sql statement,
# exec_sql will return a reference to an array
# containing the rows of query results as arrays.
# If an error occurs, it will be stored in $db_error.
############################################################
use DBI;
use strict;
sub exec_sql {
# Get parameters
my $statement = shift; # SQL statement to be executed on database
my $param_ref = shift; # Variables to be substituted inside the
+ script
my $error_ref = shift; # Reference to an error string
# Set up variables
my $result; # Result from the database query that will
+ be
# returned to the user
my $dbh; # Database connection object
+
my $sth; # SQL statement handle object
$$error_ref = ""; # Reset error string
# Set environmental variables required for Oracle
# and connection parameters (replace with your own)
$ENV{ORACLE_HOME} = q{/opt/oracle/product/8.1.6};
$DB_SID = 'mysid';
$DB_USER = 'scott';
$DB_PASSWORD = 'tiger';
# Connect to database
# Replace with your own connection parameters
if (! ( $dbh = DBI->connect
("dbi:Oracle:$DB_SID", $DB_USER, $DB_PASSWORD) ) ) {
# Error connecting to database
$$error_ref = "Cannot connect to database: "
. $DBI::errstr;
} else {
# Enable DBI to read up to 5000 characters from
# Long fields
$dbh->{LongReadLen} = 5000;
if ( !($sth = $dbh->prepare($statement)) ) {
# Error preparing SQL statement
$$error_ref
= "Prepare of SQL statement failed: "
. $dbh->errstr;
} else {
if ( ! $sth->execute( @$param_ref ) ) {
# Error executing SQL statement
$$error_ref =
"Executing of SQL statement failed: "
. $dbh->errstr;
} else {
# Everything worked, most likely
$result = $sth->fetchall_arrayref;
$sth->finish;
} #if
} #if
$dbh->commit or $$error_ref =
"Committing changes failed: " . $dbh->errstr;
$dbh->disconnect;
} #if
return $result;
} # exec_sql
# Examples of usage: "retrieve" and "add" functions for a
# server description database
sub get_description {
my $machine = shift;
my $error_ref = shift;
my $description;
$$error_ref = ""; # Reset error string
# Set up SQL statement
my $sql_statement = q{SELECT description
FROM unix_machines WHERE dns_name = ?};
my @parameters = ( $machine );
# Run the SQL statement with the given parameters
my $result =
&exec_sql( $sql_statement, \@parameters, $error_ref );
# Since the dns_name is defined as unique, then if the
# entry exists, it will be the first value in the first
# row of results.
$description = $result->[0][0];
if ( (! $$error_ref) && (! defined($ip)) ) {
$$error_ref = "No entry for $machine";
}
return $description;
} #get_description
sub add_entry {
my $machine = shift;
my $description = shift;
my $error_ref = shift;
$$error_ref = ""; # Reset error string
# Set up SQL statement
my $sql_statement = q{INSERT INTO
unix_machines( dns_name, description )
VALUES( ?, ? )};
my @parameters
= ($machine, $description);
# Execute SQL statement
&exec_sql( $sql_statement, \@parameters, $error_ref);
} # add_entry
# Had to retrofit my 80-character-line code to fit into
# snippets, so watch for errors that may caused by extra
# newlines :)