Using SQL From a Perl Script: The Basics
#!/usr/bin/perl -w
###### Demonstration of Major Relevant SQL Commands From A Perl Script ######
use DBI;
$db = DBI->connect("DBI:CSV:f_dir=.") || die "Cannot connect: $DBI::errstr\n";
if (!-e "people")
{
$create = $db->prepare("CREATE TABLE people (
first varchar(32) not null,
last varchar(32) not null,
phone char(4),
ssnum char(11) not null)");
$create->execute() || die "Cannot create the person file!\n";
}
# Let's populate the file with records!
while ((print "Enter record: "), ($record = <STDIN>) !~ /^\s*quit\s*$/i)
{
$record =~ s/^\s*//;
($first, $last, $phone, $ssnum, $extra) = split(/\s+/, $record);
if ($extra)
{
print "Too many parameters on command line! Insertion rejected!\n";
next;
}
if ($first !~ /^[a-z]+$/i)
{
print "Bad first name. Record insertion rejected!\n";
next;
}
else
{
$first = uc($first);
}
if ($last !~ /^[a-z]+$/i)
{
print "Bad last name. Record insertion rejected!\n";
next;
}
else
{
$last = uc($last);
}
if ($phone !~ /^\d{4}$/)
{
print "Bad extension. Record insertion rejected!\n";
next;
}
if ($ssnum !~ /^\d{9}$/)
{
print "Bad Social Security Number. Record insertion rejected!\n";
next;
}
# Prevent duplicate insertion.
$IsThere = $db->prepare("SELECT * FROM people WHERE ssnum = '$ssnum'");
$IsThere->execute() || die "Check for duplicate prohibited!\n";
$rows = $IsThere->fetchall_arrayref();
if (@$rows)
{
print "Someone with that Social Security Number is in the database!\n";
next;
}
# Person not already in the DB -- insert.
$RowInsert = $db->prepare("INSERT INTO people (first, last, phone, ssnum)
VALUES ('$first', '$last', '$phone', '$ssnum')");
$RowInsert->execute() || die "Cannot insert row into database!\n";
}
# Let's fetch rows and partial rows with various SELECT statements
# First, list all the rows.
$GetRows = $db->prepare("SELECT * from people");
$GetRows->execute() || die "Cannot fetch all the rows!\n";
while (($first, $last, $phone, $ssnum) = $GetRows->fetchrow_array)
{
print "$first, $last, $phone, $ssnum\n";
}
print "\n\n";
# If your rows have too many fields then the above may not be as good as...
$GetRows = $db->prepare("SELECT * from people");
$GetRows->execute() || die "Cannot fetch all the rows!\n";
$ref = $GetRows->fetchall_arrayref();
foreach $rowref (@$ref)
{
print "@$rowref\n";
}
print "\n\n";
$Get3 = $db->prepare("SELECT first, last FROM people WHERE ssnum LIKE '3%'");
$Get3->execute() || die "Cannot fetch all the rows!\n";
$ref = $Get3->fetchall_arrayref();
foreach $rowref (@$ref)
{
print "@$rowref\n";
}
print "\n\n";
# The IN operator did not work when I checked it on Unix and Windows.
#$GetLast = $db->prepare("SELECT * FROM people WHERE last IN ('BUSH','PERRY')");
#$GetLast->execute() || die "Cannot fetch all the rows!\n";
#$ref = $GetLast->fetchall_arrayref();
#foreach $rowref (@$ref)
#{
# print "@$rowref\n";
#}
$Get = $db->prepare("SELECT first, last FROM people WHERE ssnum LIKE '3%'
AND last LIKE 'B%'");
$Get->execute() || die "Cannot fetch all the rows!\n";
$ref = $Get->fetchall_arrayref();
foreach $rowref (@$ref)
{
print "@$rowref\n";
}
print "\n\n";
$update = $db->prepare("UPDATE people SET last = 'JONES', first = 'ED'
WHERE ssnum LIKE '5%'");
$update->execute() || die "Could not update file!\n";
$GetRows = $db->prepare("SELECT * from people");
$GetRows->execute() || die "Cannot fetch all the rows!\n";
while (($first, $last, $phone, $ssnum) = $GetRows->fetchrow_array)
{
print "$first, $last, $phone, $ssnum\n";
}
print "\n\n";
$delete = $db->prepare("DELETE FROM people where ssnum LIKE '4%'");
$delete->execute() || die "Could not perform deletions!\n";
$GetRows = $db->prepare("SELECT * from people");
$GetRows->execute() || die "Cannot fetch all the rows!\n";
while (($first, $last, $phone, $ssnum) = $GetRows->fetchrow_array)
{
print "$first, $last, $phone, $ssnum\n";
}
print "\n\n";
######################### General Database Notes ############################
#1. All commercial databases allow JOINS where you can get fields from
# multiple databases such as:
#
#
# SELECT foo.name, foo.address, bar.ssnum
# FROM foo, bar
# WHERE foo.ssnum = bar.ssnum
#
#2. All commercial databases (including MySQL!!) allow transaction control.
# This means that you can decide to commit any changes made to the database
# or roll the state of the database back to its original condition as you
# please. "Programming the Perl DBI" has ALL the details!
#
#3. Before you enter data into a database you should make sure you:
#
# a) Make all string data have a consistent case.
# b) Make sure you do not have duplicate entries.
# c) Learn how to normalize a database. Any good DB book can teach you how.
######################## Execution Results of Script ########################
Enter record: quit # Did all the inserts in a previous session.
JOHN, PERRY, 8931, 333333333 # Fetch using fetchrow_array
JOHN, DOE, 6543, 123456789
BILL, CLINTON, 5678, 111111111
DAVID, DANIELS, 4656, 222222222
GEORGE, BUSH, 7654, 345654123
WILLIE, WONKA, 9090, 987654321
ISAAC, NEWTON, 7865, 444444444
WILEY, COYOTE, 6565, 555555555
JOHN, MARCHANT, 8989, 666666666
JOHN, DOWLAND, 7878, 777777777
ORSTEN, ARTIS, 6767, 212121212
AL, GORE, 9095, 434343434
JOHN PERRY 8931 333333333 # Fetch using fetchall_arrayref
JOHN DOE 6543 123456789
BILL CLINTON 5678 111111111
DAVID DANIELS 4656 222222222
GEORGE BUSH 7654 345654123
WILLIE WONKA 9090 987654321
ISAAC NEWTON 7865 444444444
WILEY COYOTE 6565 555555555
JOHN MARCHANT 8989 666666666
JOHN DOWLAND 7878 777777777
ORSTEN ARTIS 6767 212121212
AL GORE 9095 434343434
JOHN PERRY # ssnum LIKE '3%'
GEORGE BUSH
GEORGE BUSH # ssnum LIKE '3%' AND last LIKE 'B%'
JOHN, PERRY, 8931, 333333333
JOHN, DOE, 6543, 123456789
BILL, CLINTON, 5678, 111111111
DAVID, DANIELS, 4656, 222222222
GEORGE, BUSH, 7654, 345654123
WILLIE, WONKA, 9090, 987654321
ISAAC, NEWTON, 7865, 444444444
ED, JONES, 6565, 555555555 # This record UPDATEd
JOHN, MARCHANT, 8989, 666666666
JOHN, DOWLAND, 7878, 777777777
ORSTEN, ARTIS, 6767, 212121212
AL, GORE, 9095, 434343434
JOHN, PERRY, 8931, 333333333 # Notice that the SSnums starting with '4' gone!
JOHN, DOE, 6543, 123456789
BILL, CLINTON, 5678, 111111111
DAVID, DANIELS, 4656, 222222222
GEORGE, BUSH, 7654, 345654123
WILLIE, WONKA, 9090, 987654321
ED, JONES, 6565, 555555555
JOHN, MARCHANT, 8989, 666666666
JOHN, DOWLAND, 7878, 777777777
ORSTEN, ARTIS, 6767, 212121212