Help With the DBI Syntax

Before using MySQL with Perl, make sure you have the DBI module as well as DBD::mysql installed. The DBI access syntax is a bit arcane, so I'll provide a full example here:

#!/usr/local/bin/perl

use DBI;

# connect to the food database
$dsn = "DBI:mysql:database=food;host=hostname;port=3306";
$db = DBI->connect($dsn, 'username', 'pass');

# add another row to the table
$db->do("INSERT INTO fruit VALUES('mango', 'orange', 100)");

# prepare a query, then execute it
$query = $db->prepare('SELECT * FROM fruit');
$query->execute();

# loop through the results
while (@results = $query->fetchrow_array()) {
  $name = $results[0];
  $color = $results[1];
  $quantity = $results[2];
  print "We have $quantity $color $name", "s\n";

Output from the above program:

We have 10 red apples
We have 15 yellow bananas
We have 50 green grapes
We have 100 orange mangos

DBI has an extensive set of methods, and they take some getting used to. Once you have them in hand, however, Perl's natural simplicity and modularity make it a database access system, which is as fast and flexible as (and a lot more elegant than) PL/SQL or Transact-SQL.

MySQL is not an industrial-strength solution, but for moderately sized applications and Web sites, its robustness and flexibility are hard to beat. It meshes beautifully with other open-source software products such as Linux, Apache, and Perl, and it's remarkably easy to set up and administer.

Steve Renaker is a San Francisco-based programmer, free-lance writer, and frequent contributor to DevX.com. He can be reached at steve@renaker.com.

Back to the First Page