Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

you could easily generate two handles with the DBI—one handle pointing to the one database and the

other handle pointing to the other database. This allows you to make very powerful programs that allow

data transfer. This type of an application would be very useful in a data warehouse.

Connecting with the DBI

Several pieces of information are needed to make a connection with the DBI. You need the driver name, the
database you are going to use, and the username and a password of the person who can connect to this
database. Nothing new here; you've seen these requirements before. The syntax for the connection
statement is as follows:

$dbh = DBI->connect(DBI:driver name:database=database name,

username, password);

The very first item is the variable that will hold the database handle. The current style is to name it $dbh

or database handle, although it can be whatever name you want it to be. It's just an ordinary Perl

variable. The item after the equal sign is the DBI object. Using the arrow syntax from the DBI object to

denote that you are calling a method of this object, you call the connect method with the following

parameters:

1. DBI:driver name:database=database name[em]The first half of this
   statement is the driver you'll be using to connect. For MySQL, it is mysql. The
   second part—separated by a colon—is the name of the database you'll be using.
   A proper argument here for a MySQL database would be
   DBI:mysql:database=Meet_A_Geek


2. The next argument is the username. This has to be a valid name that can be used
   to connect to the MySQL server.


3. The third argument is the password for the user mentioned in the second
   argument. If the user does not have a password (a very big security risk), this
   argument would be empty. You would still pass it as empty quotation marks ("").
   To connect to the Meet_A_Geek database, you would issue the following connect statement:
   $dbh = DBI->(DBI:mysql:database=Meet_A_Geek, "root", "tacobell");
   The database handle, commonly referred to as $dbh, has many methods associated with it. While they
   are not all covered here, the ones that are the most commonly used are listed. If you would like to learn
   more about the rest of the database handle methods, please refer to the man pages by typing in man
   DBI at the command line. The following are some of the database handle methods:


4. $dbh->do(SQL statement) This statement executes the SQL statement
   contained in the parentheses immediately. This is used if you are issuing
   commands that do not return a result set, such as an INSERT or UPDATE
   statement. You can also use DCL statements here as well, such as the CREATE
   and DROP commands.


5. $dbh->prepare(SQL Statement) This statement will generate a statement
   handle. A statement handle is a record set object. It has methods and properties
   that can be used to manipulate or describe the data it contains. The result set that
   is returned is the result of the query that is contained in the parentheses.


6. $dbh->disconnect This statement destroys your database handle and closes
   the database connection.
   The database handle also contains more methods, such as the table_info()and ping methods.
   Because this is a book about MySQL and not about the Perl DBI, these methods are beyond the scope
   of this book. Please refer to the man pages mentioned earlier for more information.

Writing Perl Script

Now you can create your first Perl script. The following is a simple script that will connect to the
Meet_A_Geek database and INSERT three rows of data:

#!/usr/bin/perl –w

use DBI;

$database = "Meet_A_Geek";

$driver = "DBI:mysql";