############################################################
# 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 :)