Program Example

The following small program example adds a table to an existing database. I show only one example because all we are really doing is executing a quick SQL statement that we won't use repeatedly.

01: #!/usr/bin/perl -w 02: # add_table.pl 03: # Chapter 3 04: # Listing 1

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

Lines 2-4 are simply comments about this program.

05: use strict; 06: use DBI;

Line 5 loads the strict module. This module is used to help the programmer. It forces the programmer to declare variables and can catch common errors that can be hard to debug.

Line 6 loads the DBI module so that we can access a database.

07: my $dbh = DBI->connect("DBI:mysql:widgets",      "bookuser","testpass")  08:     or die("Cannot connect: $DBI::errstr");

Line 7 declares a scalar variable named $dbh and connects us to the database. The DBI->connect method is used to connect to the database; we pass the database driver and name (DBI:mysql:widgets), username (bookuser), and password (testpass) so that we can connect. The connect method returns a handle to the database if all goes well. This handle is stored in the $dbh variable.

Line 8 is a continuation of line 7. If there is a problem connecting to the database, this line gets executed. This line causes the program to die and to display an error message to the user.

09: my $sql = qq{CREATE TABLE products ( 10:               Pid   INT NOT NULL, 11:               Item  VARCHAR(255), 12:               Descr VARCHAR(255), 13:               Price REAL, 14:               Vid   INT NOT NULL)};

Lines 9-14 are the SQL statement to add a table to the database. On line 9, we declare a scalar variable named $sql, which we set to the string that makes up the SQL statement. Notice that for the qq function we use curly brackets {} instead of parentheses () because we need to include parentheses inside of the quoted string.

This is the same SQL statement we use above when we create the products table. This is just a way to create a table by using Perl instead of the database command line.

15: my $return = $dbh->do($sql);

Line 15 calls the do method on the $dbh handle and passes the SQL statement, via $sql, to the method. The do method saves time when you are not expecting data to be returned because it takes the place of both the prepare and execute methods. The value the do method returns is stored in the new scalar variable named $return.

16: if($return) { 17:     print "Table addition successful!\n"; 18: } 19: else { 20:     print "\n\nERROR! $DBI::errstr\n"; 21: }

Lines 16-21 check to see what has happened. Line 16 uses the if function to see if $return contains anything. If the SQL statement is successful, a value will be stored in $return. It really doesn't matter what value as long as a value is there.

Line 17 prints the success message if the SQL table addition is successful.

Line 18 ends the if block.

Line 19 begins the else block. This is the block of code that the program enters if there is no value in $return. If an error occurs, $return contains undef, which is not a value that evaluates to true, so the preceding if block does not execute.

Line 20 prints the error message as well as the error text that gets set in the $DBI::errstr variable.

Line 21 closes the if..else block and is also the last line of this program.