30 second guide to Perl DBI

The Perl DBI module provides a uniform API to access relational databases. Thanks to Perl’s data type model using it is considerably easier than, say JDBC in the Java world. In common with most database access APIs, the code is split into two bits, the generic infrastructure is in the DBI module, while backends for each database are in the various DBD modules:

• DBD::Pg – PostgreSQL
• DBD::Oracle – Oracle
• DBD::Informix – Informix
• and many more…

The first task in any program is to connect to the server. This is done with the DBI->connect method. To simplify error handling, its good practice to turn on ‘RaiseError’ option, and turn off ‘PrintError’ and ‘AutoCommit’. Then by wrapping the entire unit of work in an eval we get safe transaction commit/rollback without the need to check method return status on each DBI call.

  use DBI;

  my $db;

  eval {
    $db = DBI->connect("DBI:Pg:dbname=mydb;host=myhost",
                       $username, $password, {
			  RaiseError => 1,
			  PrintError => 0,
			  AutoCommit => 0
                       });

      ...do some work with the db...
  };
  if ($@) {
     if ($db) {
       $db->rollback;
     }
     die $@;
  }
  $db->commit;
  $db->disconnect;

The next task is issue statements to the DB. Following common practice DBI allows placeholders to be used in SQL, which are substituted with real values at execution time. If the underlying DB doesn’t support placeholders, DBI will emulate them. For maximum performance its advisable to use prepared statement handles, and again DBI will emulate this feature if the underlying driver does not support it.

    my $sth1 = $db->prepare_cached("INSERT INTO foo (bar) values (?)");
    $sth1->execute($bar);

    my $sth2 = $db->prepare_cached("UPDATE foo SET bar = ? WHERE wizz = ?");
    $sth2->execute($bar, $wizz);

    my $sth3 = $db->prepare_cached("DELETE foo WHERE bar LIKE ?");
    $sth3->execute($bar);

The final common task is retrieving data from the DB. There are a number of ways to get data back, but the simplest to code is to bind variables to each return parameter and then call ‘fetchrow’.

    my $sth4 = $db->prepare_cached("SELECT bar, wizz FROM foo where bar > ?");
    $sth4->execute(20);

    my ($bar, $wizz);
    $sth4->bind_columns(\$bar, \$wizz);
    while ($sth4>fetchrow) {
        print "Got $bar $wizz\n";
    }

Putting this together a complete example looks like

  use DBI;

  my $db;

  eval {
    $db = DBI->connect("DBI:Pg:dbname=mydb;host=myhost",
                       $username, $password, {
			  RaiseError => 1,
			  PrintError => 0,
			  AutoCommit => 0
                       });

    my $sth1 = $db->prepare_cached("INSERT INTO foo (bar) values (?)");
    $sth1->execute($bar);

    my $sth2 = $db->prepare_cached("UPDATE foo SET bar = ? WHERE wizz = ?");
    $sth2->execute($bar, $wizz);

    my $sth3 = $db->prepare_cached("DELETE foo WHERE bar LIKE ?");
    $sth3->execute($bar);

    my $sth4 = $db->prepare_cached("SELECT bar, wizz FROM foo where bar > ?");
    $sth4->execute(20);

    my ($bar, $wizz);
    $sth4->bind_columns(\$bar, \$wizz);
    while ($sth4->fetchrow) {
        print "Got $bar $wizz\n";
    }
  };
  if ($@) {
     if ($db) {
       $db->rollback;
     }
     die $@;
  }
  $db->commit;
  $db->disconnect;