use strict;
use DBI;

# edit these to match your server's settings
my $database="MyDatabaseName";
my $user="someuser";
my $password="somepass";
my $dbhost="MyHost";

my $dbh;
my $sth;

#############################################################
sub connect {
        $dbh=DBI->connect("DBI:mysql:database=$database;host=$dbhost", $user, $password)
                or &handle_error("Cannot open database $database. $DBI::errstr");
        return ($dbh)
}

sub disconnect {
        if(defined $sth) { my $rc=$sth->finish; }
        if(defined $dbh) { my $rc=$dbh->disconnect or &handle_error ("Error on disconnect. $DBI::errstr"); }
}
#############################################################
# you could edit this to write to a log or send you an email
#  or whatever you wanted
sub handle_error {
        my $error_string = "Server name: ".$ENV{SERVER_NAME}."\n";
        $error_string .= "Request: ".$ENV{REQUEST_URI}."\n";
        $error_string .= "Method: ".$ENV{REQUEST_METHOD}."\n";
        $error_string .= "Cookies: ".$ENV{HTTP_COOKIE}."\n";
        $error_string .= "Remote address: ".$ENV{REMOTE_ADDR}."\n";

        my $buf;
        if ($ENV{'REQUEST_METHOD'} eq 'POST') { read(STDIN,$buf,$ENV{'CONTENT_LENGTH'}); }
        else { $buf=$ENV{'QUERY_STRING'}; }

        $error_string .= "Variables: ".$buf."\n\n\n";
        $error_string .= "Error: \"@_\"";

        #now you have an error message
        #you could do whatever you wanted with

        # I wouldn't display all that info to your user though
        exit 0;
}
#############################################################
#just execute a sql qry .. no return .. fails safely
sub safe_do {
        my $sql=$_[0];
        $dbh->do($sql) or &handle_error ("SQL statement failed: $sql; ERROR DESCRIPTION: $DBI::errstr");
}

#############################################################
#just execute a sql qry and ignore all errors that are non-fatal
sub unsafe_do {
        my $sql=$_[0];
        return $dbh->do($sql);
}

#############################################################
#returns a single row of values
sub safe_select_row {
        my $sql=$_[0];
        my @row=$dbh->selectrow_array($sql);
        &handle_error ("SQL statement failed: $sql; ERROR DESCRIPTION: $DBI::errstr") if $DBI::errstr;
        return @row;
}
#############################################################
#retrurns a single row of values as a referrence
sub safe_select_row_ref {
        my $sql=$_[0];
        my $row=$dbh->selectrow_arrayref($sql);
        &handle_error ("SQL statement failed: $sql; ERROR DESCRIPTION: $DBI::errstr") if $DBI::errstr;
        return $row;
}

#############################################################
#returns a single row of values in a hash referrence
sub safe_select_row_hashref {
        my $sql=$_[0];
        my $sth=$dbh->prepare($sql)
                or &handle_error ("SQL statement failed: $sql; ERROR DESCRIPTION: $DBI::errstr");
        $sth->execute()
                or &handle_error ("SQL statement failed: $sql; ERROR DESCRIPTION: $DBI::errstr");
        my $row=$sth->fetchrow_hashref();
        #$sth->finish();
        return $row;
}

#############################################################
#returns a result
sub select_rows {
        my $sql=$_[0];
        my $sth=$dbh->prepare($sql)
                or &handle_error ("prepare failed: $sql; ERROR DESCRIPTION: $DBI::errstr");
        $sth->execute() or &handle_error ("execute failed: $sql; ERROR DESCRIPTION: $DBI::errstr");
        return $sth;
}

#############################################################
#returns all rows in an array reference
sub select_all_rows {
        my $sql=$_[0];
        my $sth=select_rows($sql);
        my $rows=$sth->fetchall_arrayref();
        return $rows;
}


#############################################################
#checks to see if a table exits
sub table_exists {
        my ($table)=@_;
        my @names=$dbh->tables();
        my $field;

        foreach $field (@names) {
                if($field eq $table) { return 1; }
        }
       
        return 0;
}

#############################################################
#exec sql statement, returns the number of rows affected
sub exec {
        my($sql)=@_;

        my $sth=$dbh->prepare($sql)
                or &handle_error ("Can't prepare statement '$sql': $DBI::errstr");

        my $rows_affected=$sth->execute
                or &handle_error ("Can't execute statement '$sql': $DBI::errstr");

        return $rows_affected;
}

#############################################################
#exec sql statement with values, returns the number of rows affected
sub execWithValues {
        my($sql,@values)=@_;

        my $sth=$dbh->prepare($sql)
                or &handle_error ("Can't prepare statement '$sql': $DBI::errstr");

        my $rows_affected=$sth->execute(@values)       
                or &handle_error ("Can't execute statement '$sql': $DBI::errstr");

        return $rows_affected;
}