Now that we can add and modify data, let's create something that allows a user to delete data from the database table. Our next application does just that; it allows the user to remove a record.
01: #!/usr/bin/perl -w 02: # 03: # program 5-3 04: # Chapter 5 05: # Listing 3 06: # 07: use strict; 08: use DBI;
Line 1 should be very familiar by now. It tells the system where to find Perl and turns on warnings.
Lines 2-6 are simply comments about the program.
Line 7 calls the strict pragma to force better programming style.
Line 8 loads the DBI module so that this program can talk to the database.
09: my $conn = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr");
Lines 9-10 form a single Perl statement that tries to create an object of class DBI that represents a connection to the database. If the connection cannot be made, our call to DBI->connect will fail, and we will abort the program by using die.
11: Delete_Record(Get_ISBN());
Line 11 calls the Delete_Record subroutine and takes the value returned by the call to Get_ISBN as it's input.
12: sub Get_ISBN{ 13: print "Delete ISBN #:\n"; 14: $isbn = <STDIN>; 15: chomp($isbn); 16: return($isbn); 17: }
Line 12 begins the Get_ISBN subroutine.
Line 13 prints out a prompt for the user to enter the ISBN of the book to delete.
Line 14 reads STDIN (the keyboard) and stores the value entered into the $isbn variable.
Line 15 uses the chomp function to remove any trailing newline from the $isbn variable.
Line 16 returns $isbn from the subroutine.
Line 17 ends the Get_ISBN subroutine.
18: sub Delete_Record{
Line 18 begins the Delete_Record subroutine.
19: my $result = $conn->do("DELETE FROM library WHERE isbn = ‘$isbn'") 20: or die("\nError executing SQL statement! $DBI::errstr");
Line 19 uses yet another method for calling an SQL statement. Here, we again use the do method, but instead of creating a variable called $sql and then storing the SQL statement string in it, we simply put the SQL string directly inside do! This is another way to save a bit of space without sacrificing clarity.
Also on this line, we declare a variable named $result and store the result of the $conn->do call. If the call is successful, 1 will be returned. If the call is not successful, 0 will be returned.
Line 20 is a continuation of Line 19. This line tells the program to die if there is a problem executing the SQL statement.
21: if($result){ 22: print "Record deleted from database.\n"; 23: } 24: else { 25: print "Record NOT DELETED! $DBI::errstr "; 26: } 27: return 1; 28: }
Line 21 checks the $result variable to see if it contains a true value. If so, the code inside its block is executed.
Line 22 is the code that gets executed if $result contained a true value.
Line 23 ends the first part of the if..else block that begins on Line 21.
Line 24 is the else part of this if..else block. If $result does not contain a value (is false), the code inside of this block is executed.
Line 25 is the code that gets executed if $result is false (contained no value).
Line 26 closes the if..else block.
Line 27 returns a true value from the subroutine.
Line 28 closes the Delete_Record subroutine.
That is it for the program that deletes a record. Deleting is much easier than inserting a record or modifying an existing record because you do not have to gather all of the information about the record and then create a somewhat complex SQL statement.