Using UPDATE

Once you have entries in a database, you'll eventually want to modify some of them. We'll now develop a program to alter a record in the database, given its ISBN number.

As we go through this next application, try to notice the many similarities between it and the previous program. This program uses much of the same code. I have

used a few different techniques in this program, however, to show you different ways of accomplishing the same thing.

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

Line 1 is just like the first lines of our other applications. It tells the system where to find Perl and turns on warnings with the -w.

Lines 2-6 are comments about the program.

Line 7 loads the strict pragma. This makes the programmer use better programming techniques so that common programming errors are avoided.

Line 8 loads the DBI module. This is the DataBase Interface that Perl uses to interact with databases.

09: my $conn->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 must abort the program by using die.

11: my $old_isbn; 12: my $book = Get_Data(); 13: Change_Record($book);

Line 11 declares a scalar variable named $old_isbn. This variable is used so that if the ISBN of the book changes, we can still remember the old value, as it's the value that we need to query for in the database.

Line 12 calls the Get_Data subroutine. This is a bit different from the function called in the previous example. In this example, the Get_Data subroutine returns a reference to the %book hash, which we store in the scalar variable named $book.

Line 13 calls the Change_Record subroutine, and in this example we pass the hash reference (stored in $book) to the subroutine so that it can access the data.

14: sub Get_Data{ 15:     my %book; 16:     $old_isbn        = Get_Input("Enter ISBN of Book to Modify");

Line 14 begins the Get_Data subroutine.

Line 15 declares a hash variable named %book. Since we declare the variable inside of the subroutine, and use my, this variable has only a scope inside of this subroutine. When you declare a variable with my, that variable has a scope that exists only within the innermost enclosing block. In our previous example, %book is globally scoped, as are the other variables we declared outside of any enclosing blocks.

Keeping your variables under control gets more important as your programs grow. When you declare all of your variables as global and don't pass anything to/from subroutines, it becomes very easy to start 'stepping on the toes' of the variables. By this, I mean that when everything is global and you are working with a variable named $foo, can you be absolutely sure that $foo contains the data you think it does? Not always, especially if $foo is used a lot in the program.

When, however, you declare as many variables as possible only in the scope where they are needed and pass references to/from them between subroutines, you can be much more certain that the data you think is in a variable actually is. Also, this can make programs easier to follow and maintain; when you look at the code, you can tell exactly where the variables/values come from.

Line 16 calls the Get_Input subroutine to print a prompt to the user. The value returned from the Get_Input subroutine is then stored in the $old_isbn variable. We do this step so to capture the ISBN number for the book that we are changing. The ISBN number uniquely identifies the book in the database. If the user is changing the ISBN of this book, we need to pass the old ISBN to the database because the new value is not yet known to the database.

17:     $book{isbn}      = Get_Input("Enter ISBN (even if not changed)"); 18:     $book{title}     = Get_Input("Enter Book Title"); 19:     $book{author}    = Get_Input("Enter Book Author"); 20:     $book{price}     = Get_Input("Enter Book Price"); 21:     $book{format}    = Get_Input("Enter Book Format"); 22:     $book{publisher} = Get_Input("Enter Book Publisher"); 23:     $book{pubdate}   = Get_Input("Enter Publish Date"); 24:     $book{notes}     = Get_Input("Enter Notes")  

Lines 17-24 use the Get_Input subroutine to prompt the user for the appropriate input and store the values returned into the %book hash.

25: return(\%book); 26: } 

Line 25 returns a reference to the %book hash. By returning this reference, we can store the reference in a scalar variable and easily pass it among subroutines as needed. When we need to access data, we simply de-reference it and fetch it.

Line 26 ends the Get_Data subroutine.

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

Line 27 begins the Get_Input subroutine.

Line 28 prints out 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 29 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 all of the lines in from standard input before returning the value.

Line 30 closes the Get_Input subroutine.

31: sub Change_Record{ 32:     my $book = shift;

Line 31 begins the Change_Record subroutine.

Line 32 uses the shift function to get the first (and only) value that was passed to the subroutine and stores it in the $book variable.

shift is used to take a value off of the front of an array. Since we don't pass shift an array to shift from, shift defaults to using the @_ array. The @_ array is where all data passed to a subroutine is stored; by using shift, we can easily read the values that have been passed in.

33: my @keys = keys   %$book; 34:     my @vals = values %$book; 35:     chomp (@vals);     

Lines 33-34 act just like Lines 29-32 of our previous example. The difference between these lines and the ones in the previous example is that $book is a reference to a hash, so we need to de-reference it to get the actual hash. To do this, we place a % in front of the $book. This can also be written as %{$book} for those of you who find that clearer.

Line 35 uses the chomp function to remove any trailing newlines from the values in the @vals array.

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

Line 36 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.

37: my $sql = "UPDATE library SET "; 38:     my $counter = 0; 39:     foreach my $key (@keys){ 40:         $sql .= "$key = ‘$vals[$counter]', "; 41:         $counter++; 42:     } 43:     $sql =~ s{, $}{ WHERE isbn = ‘$old_isbn'};    

Line 37 begins the code we use to construct the SQL statement needed to update the database. This line declares a my variable named $sql and stores the first part of the SQL statement in it.

Line 38 creates a variable named $counter and initializes it to 0. We use this as an index as we traverse through the array @vals.

Line 39 begins a foreach loop that traverses through the @keys array and stores the current value in the scalar variable named $key.

Line 40 appends (using the .= operator) the current values onto the $sql variable. If our current $key value were "title" and the current value of $vals[$counter] were "Perl Database Programming," this line would append title = ‘Perl Database Programming', onto the end of whatever value were currently in the $sql variable.

Note 

$sql .= "foo bar blah" is a shorthand way for writing $sql = $sql . "foo bar blah".

Line 41 increments the $counter variable.

Line 42 closes the foreach loop that begins on Line 39.

Line 43 finishes off the SQL statement by replacing the last comma and space (, ) with WHERE isbn = ‘value of old isbn'.

Tip 

I use a different syntax for this regular expression. Instead of using the /'s to delimit the regular expression, I use curly braces to enclose the parts-like this: {look for} {replace with}. This sometimes makes the regular expression easier to follow, in my opinion.

44:     my $query = $conn->do($sql)  45:         or die("\nError executing SQL statement! $DBI::errstr"); 46:    print "Record information updated in the database...\n"; 47:    return 1; 48: }

Line 44 uses a new method for interacting with the database. Instead of having to do $foo = $conn->prepare($sql) and then a $foo->execute, if you are just doing a SQL call and don't expect any data back, you can use the do method to save yourself the extra prepare step.

Line 45 is a continuation of line 44 and causes the program to die and display an error message if an error occurs in the execution of the SQL command.

Line 46 prints out a message to the user, indicating that the update is successful.

Line 47 returns a true value from the subroutine.

Line 48 ends the Change_Record subroutine.

That wraps up our application that modifies existing data. Most of the code for adding and modifying is the same-the main difference is the SQL statement.