return to first page linux journal archive
keywordscontents

Listing 1. dbi-demo.pl

#!/usr/bin/perl -w
# This program demonstrates how to use DBI by
# connecting to a database, inserting several 
# rows, and then selecting those rows. The 
# results are printed to the screen.
#
use strict;
use diagnostics;
use vars qw($query $dbh $sql $sth @row);
use DBI;
use CGI;
# Create an instance of CGI
$query = new CGI;
# Send MIME and HTML headers
print $query->header("text/html");
print $query->start_html(-title => "DBI demo");"
# ---------------------------------------
# Connect to the "test" MySQL database on localhost
$dbh = DBI->connect("DBI:mysql:test:localhost");
# Log and die if we cannot connect
&log_and_die($DBI::errstr) unless $dbh;
# Print a status message
print "<P><B>Now inserting...</B></P>\n";
# Insert several values into the database
foreach my $string ("foo","bar","baz")
{
    # Create our query
    $sql = "INSERT INTO test_insert (contents) 
VALUES (\"$string\") ";
    # Perform the query and get results
    my $successful_insert = $dbh->do($sql);
    # Give the user a status update
    if ($successful_insert)
    {
	print 
	"<P>Successfully inserted \"$string\"</P>\n" 
    }
    else
    {
	print 
	"<P>Error inserting: ", $dbh->errstr, "</P>\n";
    }
}
# ----------------------------------------
# Now that we have inserted some values, we will
# use SELECT to retrieve them.
# Print a status message
print "<P><B>Now retrieving...</B></P>\n";
# Create our query string
$sql = "SELECT id,contents FROM test_insert";
# Send the SQL, and make sure all is well
$sth = $dbh->prepare($sql);
&log_and_die($sth->errstr) unless $sth;
# Execute the query, and make sure all is well
$sth->execute || &log_and_die($sth->err);
# Loop through returned rows
while (@row = $sth->fetchrow)
{
    # Grab the columns from the row
    my $id = $row[0];
    my $contents = $row[1];
    # Print the ID and the contents
    print "<P>$id:\"$contents\"</P>\n";
}
# Finish with our statement
$sth->finish;
# Disconnect from the database
$dbh->disconnect;