Posted on by tigoe

Accessing SQL in Perl

This is a perl script that uses DBI to open an SQL database, read all the lines of a table, and print them out in an HTML page. This script assumes you’ve got a table in your database called people_table, and that you’ve got at least two fields, one called firstname and one called lastname.
Everything I know about accessing SQP through perl (which isn’t much) I learned from Chris Sung’s notes, in about half an hour. I copied much of this script from him too.

Technorati Tags: ,



Here’s the script:

#!/usr/bin/perl

# Get the directory of this script:

if    ($0=~m#^(.*)\\#){ $execDir = "$1"; }  # Win/DOS
elsif ($0=~m#^(.*)/# ){ $execDir = "$1"; }  # Unix
else  {`pwd` =~ /(.*)/; $execDir = "$1"; }  # Unix

#     get the login (if you get the login like this,
#    make sure that only you can read and write to the file (chmod 0600 on the file):
require "$execDir/info.pl";
use DBI();

#    Set basic parameters:
$database = $user;
$data_source = "dbi:mysql:$database";
$username = $user;
$password = $passwd;

#    Open the database:
&connectSQL;
#    Print the top of the HTML page:
&print_header;
#    Get everything from the database:
&get_everything;
#    Print the bottom of the HTML page:
&print_footer;
#    disconnect from the database:
&disconnectSQL;

#    End the script:
exit(0);


#########################################################
#    Subroutines
#########################################################


#########################################################
# Connect to the database.
#########################################################
# This only needs to be done once per script
sub connectSQL {
     $dbh = DBI->connect($data_source, $username, $password,
        {'RaiseError' => 1, 'PrintError' => 1});;
    
}
 
 
#########################################################
# Disconnect from the database.
#########################################################
# Always the last thing you do before exiting your script

sub disconnectSQL {
    $dbh->disconnect();
}

#########################################################
# Get everything from the given table:
#########################################################


sub get_everything {
    # Retrieve all rows from the table and put in normal array:
    $SqlStatement = "SELECT * FROM people_table ORDER BY firstname";
    $sth = $dbh->prepare($SqlStatement);
    $sth->execute();

    while (@row_array = $sth->fetchrow_array())
    {    $firstname = $row_array[0];
        $lastname = $row_array[1];
        print "$firstname    $lastname <br>\n";
    }
    $sth->finish();
}

#########################################################
# Print the beginning of an HTML document
#########################################################

sub print_header {
#Print the header of the HTML file:    
print "Content-type: text/html\n\n";
print qq^
<html>

<head>
    <title>Database Results</title>
</head>
<body>
<h1>database results</h1>
^
}

#########################################################
# Print the end of an HTML document
#########################################################

sub print_footer {
print qq^
</body>

</html>
^
}