Using ADD

Our first example deals with adding data to the database. The user interface for this program is simply the command line. The program allows the user to enter one item into the database. Although the program is functional, it is not very user-friendly. We will be creating user interfaces with more features as we progress in this book.

01: #!/usr/bin/perl -w 02: # 03: # program 5-1 04: # Chapter 5 05: # Listing 1 06: # 07: use strict; 08: use DBI;

Line 1 tells the system where to find Perl and turns on warnings with the -w flag.

Lines 2-6 are comments that provide information about the program. You may notice that inside the application I use very few comments. The only reason for this is because I am providing detailed comments about the code here. Normally, I use generous comments in my code so that if anyone needs to maintain the program, or just wants to see what it does, he or she can easily do so by looking at the comments. Comments are good; use them generously.

Line 7 turns on the strict pragma. This forces the programmer to use better programming practices, such as declaring variables before they are used. Using the strict pragma helps the programmer avoid many common mistakes, like inadvertently reusing a variable name.

Line 8 loads the DBI module. The DBI module is required for programs that connect to a database. The module is well written and has become the de-facto standard in Perl database programming.

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: my ($sql, %book, @keys); 12: Get_Data(); 13: Execute_Transaction(); 

Line 11 declares some variables that we'll use in this program.

Line 12 calls the Get_Data subroutine. This subroutine gathers the book information from the user.

Line 13 calls the Execute_Transaction subroutine. This is the subroutine that writes data to the database.

14: sub Get_Data{ 15:     $book{‘isbn'}      = Get_Input("Enter ISBN #"); 16:     $book{‘title'}     = Get_Input("Enter Book Title"); 17:     $book{‘author'}    = Get_Input("Enter Book Author"); 18:     $book{‘price'}     = Get_Input("Enter Book Price"); 19:     $book{‘format'}    = Get_Input("Enter Book Format"); 20:     $book{‘publisher'} = Get_Input("Enter Book Publisher"); 21:     $book{‘pubdate'}   = Get_Input("Enter Publish Date"); 22:     $book{‘notes'}     = Get_Input("Enter Notes"); 23:     return 1; 24: }

Line 14 begins the Get_Data subroutine.

Lines 15-22 populate the %book hash. The data, which was returned from each call to Get_Input, is stored in the %book hash at the corresponding hash key.

Line 23 returns 1 from the subroutine. Returning a true value, 1 in this case, is a common Perl programming practice. Returning a true value makes it easy for a programmer to test whether or not the subroutine completed successfully.

Line 24 closes the Get_Data subroutine.

25: sub Get_Input { 26:     print $_[0], ":\n"; 27:     return scalar <STDIN>; 28: }

Line 25 begins the Get_Input subroutine.

Line 26 prints the first item passed to this subroutine then a colon and a newline. The text that gets printed is the field name so that the user knows what field they are entering data for.

Line 27 returns the value that was entered by the user. The handle <STDIN> that you see on this line is the standard input handle. It works just like a file handle, except it reads the input from the keyboard instead of a file.

The scalar function that is on this line prevents <STDIN> from reading in all of the lines from standard input before returning the value.

Line 28 closes the Get_Input subroutine.

29: sub Execute_Transaction{ 30:     @keys = keys   %book; 31:     @vals = values %book; 32:     chomp(@vals);

Line 29 begins the Execute_Transaction subroutine. This is the subroutine that reads %book, creates the SQL statement and adds the data to the database.

Line 30 copies the keys from the %book hash into the @keys array.

Line 31 copies the values from the %book hash into the @vals array.

Line 32 uses the chomp function to remove the trailing newline character off of the values in the @vals array. The newlines arise when the user enters the data and hits the Enter key-the Enter key is actually recorded along with the data that the user enters. chomp is very useful; if you pass it an array, as we do here, it will process the entire array for you!

33:     @vals = map{$conn->quote($_)} @vals;

Line 33 uses the map function to call the quote function on all of the items in the @vals array. map performs the function(s) inside of the curly bars, {}, on each member of the array that is passed to the map function. So, you are easily able to transform every item in an array with map.

34:     $sql = "INSERT INTO library (" 35:            . join(", ", @keys) 36:            . ") VALUES (" 37:            . join(", ", @vals) 38:            . ")";

This code block can be a little tricky, so please make sure you understand it before moving on.

Lines 34-38 are actually a single Perl statement; notice that the semicolon that ends the statement is on Line 38.

Line 34 simply stores a string of text into the scalar named $sql.

Line 35 begins with a dot. The dot is called the concatenation operator and is used to join strings together. The join function is used to take all of the values in an array and join them with the value passed to it-here we pass ", " so each value is joined by what is inside the quotes.

This line joins each value in the @keys array by using a comma and a space.

Line 36 adds text to the $sql string.

Line 37 does nearly the same thing line 35 does except it acts on the values instead of the keys.

Line 38 finishes off this SQL command string by adding a trailing ‘).

Our finished SQL statement will look something like this:

INSERT INTO library (field1, field2, field3) VALUES (‘item 1', ‘item 2', ‘item 3'); 39:   my $query = $conn->prepare($sql); 40:   $query->execute or die("\nError executing SQL statement! $DBI::errstr");

Line 39 uses the DBI connection object's prepare method to get the SQL statement ready to run. This returns a new object representing the query we're about to perform, which will then actually update the database.

Line 40 calls the execute method of the $query handle, which actually runs the SQL in the database. If the database returns any errors, the program will die and give us the returned error.

41:     print "Record added to database.\n"; 42:     return 1; 43: }

Line 41 simply uses the print function to print a message so that the user knows that the record has been added.

Line 42 returns a true value from this subroutine.

Line 43 closes the Execute_Transaction subroutine.

There we have our first application that allows us to modify data in the database. This application adds only one record at a time, but we will soon be writing applications that offer much more functionality than this.