Using DELETE

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.