Using the MySQL DBD

Any Perl script that uses DBI with the MySQL DBD module should begin with the following line:

 use Mysql; 

Mysql

Note the unusual capitalization of Mysql that is used in the Perl API. The name of this extension is case sensitive.

Connecting to MySQL

You establish a connection to a MySQL database by calling the connect() method on the master Mysql object. The following statement opens a new connection to the database mysql10 on a local MySQL server and assigns a database handle named $dbh.

 $dbh = Mysql->connect("localhost", "mysql10", "username" "password"); 

Mysql->connect() should have exactly four arguments:

• hostThe hostname of the MySQL server. This can be an IP address, domain name, or hostname. Use undef when the database server is localhost.

• dbnameThe database name to connect to.

• userThe username for database authentication.

• passwordThe password corresponding to the username passed in argument 3.

The return value when a successful connection has been made is a database handle resource. If an error arises when connecting to the database, the properties errno and errstr on the master Mysql object will contain the MySQL error number of message text, respectively.

You can use a statement such as the following to perform a database connection with error trapping:

 $dbh = Mysql->connect("localhost", "mysql10", "username", "password")    or die ("Error " . Mysql->errno . "  " . Mysql->errstr); 

Executing a Query

When you have a database handle resource that is connected to a MySQL server, you can execute a query using the query() method on the database handle. Its argument should be a single SQL statement; the terminating semicolon is not required. The following statement is an example:

 $sth = $dbh->query("SELECT * FROM customers"); 

The return value is a statement handle, which was assigned to $sth in this example. The statement handle is used to find information about the query and to retrieve data rows.

If an error occurs while executing the query, you can access the errno and errstr properties on the database handle object to retrieve the MySQL error number and message text, respectively.

You can use the following statement to execute a query and display the associated error message if there is a problem:

 $sth = $dbh->query("SELECT * FROM products")    or die("Error " . $dbh->errno . "  " . $dbh->errstr); 

Finding Information About a Query

If the query you executed was a SELECT statement, you can find how many data rows it returned by checking the num_rows property on the statement handle. The following statement is an example:

 print $sth->num_rows . " rows(s) were returned"; 

The num_fields property similarly contains the number of columns in the data set. The affected_rows property contains the number of rows affected by an UPDATE, INSERT, or DELETE statement.

Fetching Queried Data

When the fetchrow method is called on a statement handle, a row of data from the result of the query is returned into an array structure. No arguments are required.

The first time you call fetchrow(), the first row from the data set is returned; subsequent calls return the next row until there is no data left to be fetched.

The array elements are numbered from zero in the order they appear in the SELECT statement. For example, suppose that your script executes the query SELECT code, price FROM products ORDER BY price and then uses $sth->fetchrow to return a row of data into @data. The value in $data[0] will be the code for the first product returned, and $data[1] will be its price.

To reference data by its column name or alias, use the fetchrow_hash method. Using the previous example query and fetchrow_hash, you would be able to reference the values selected as $data->{code} and $data->{price}.

Listing 25.1 puts everything together into a script that connects to MySQL, executes a query, and fetches and displays the resulting data.

Listing 25.1. Performing a Query Using the Perl DBI

 #!/usr/bin/perl use Mysql; $dbh = Mysql->connect("localhost", "mysql10", "user", "pass")   or die ("Error " . Mysql->errno . " - " . Mysql->errstr); $sql = "SELECT * FROM customers ORDER BY name"; $sth = $dbh->query($sql)   or die("Error " . $dbh->errno . " - " . $dbh->errstr); while (@row = $sth->fetchrow) {   for($i=0; $i<$sth->numfields; $i++) {    print $row[$i] . "\t";   }    print "\n"; } 

Running this script produces the output shown here: the contents of the customers table in tabulated format.

 $ perl listing25.1.pl MUSGRP Musicians of America PRESINC Presidents Incorporated SCICORP Science Corporation 

Tidying Up

Database connections opened in your script are automatically closed and resources are deallocated when the script ends. There is no specific function to explicitly disconnect from a database in the Perl DBI.

However, to free up any resources allocated in a running script, you can simply use undef to free the handle.