Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Run SQL statements

by randomblue (Beadle)
on Nov 09, 2001 at 22:18 UTC ( #124426=snippet: print w/replies, xml ) Need Help??
Description: 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 :)
Replies are listed 'Best First'.
Re: Run SQL statements (what about dbish?)
by grinder (Bishop) on Nov 10, 2001 at 18:29 UTC

    Did you know that the DBI distribution comes bundled with dbish, a shellish interface to DBI? It can do all sorts of nifty things.

    For instance, it has a history mechanism...

    --
    g r i n d e r
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: snippet [id://124426]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (4)
As of 2022-08-16 11:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?