Perl And MySQL; Using DBI; Connections

Tim Bunce, the lead developer of DBI.pm, asserts ‘The DBI is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used. [..] The DBI is a layer of ‘glue’ between an application and one or more database driver modules. It is the driver modules which do most of the real work. The DBI provides a standard interface and framework for the drivers to operate within.’ (http://search.cpan.org/~timb/DBI-1.38/DBI.pm)

However, DBI attempts to make it simple for programmers by creating almost seamless integration with drivers. It’s only when databases have different specifications that one actually has to take note of which database they’re using. The most notable differences are between transactional databases, and specific functions that may be linked to specific queries, such as fetching the index of an automatically incrementing database. This tutorial will cover the basics of databases, assuming that you will have access to a server with Perl, MySQL, and DBI.

Opening The Connection

use DBI;
$dsn = ‘dbi:mysql:dbname=NameOfDatabase’;
$user = ‘mysqlusername’;
$password = ‘mysqlpassword’;
$dbh = DBI->connect($dsn, $user, $password,
{ RaiseError => 1, AutoCommit => 0 });

What exactly this code mean? Obviously, you’re connecting to the database, but what are these variables standing for?

Opening The Connection [Page 2]

Well, we’ll begin with the basic connect statement:

$dbh = DBI->connect($dsn, $user, $password,
{ RaiseError => 1, AutoCommit => 0 });

This line establishes a database connection ($dbh, which stands for database handle), from which we’ll able to query the database in the future. Each parameter that the connect method of the DBI module (which is actually what OO programmers call a constructor) takes is important. The connect method takes a data source ($dsn), MySQL username ($user), MySQL password ($password), and a hash reference with additional parameters, such as whether to throw errors when the driver has errors, or whether to automatically commit data for transactional databases.

The Data Source

$dsn = ‘dbi:mysql:dbname=NameOfDatabase’;

$dsn must contain ‘dbi:driver_name:‘, from there the input is database specific. With MySQL ‘dbname=NameOfDatabase’ should be all that’s necessary. At other times, you may need to specify the host you wish to connect to, if you’re not trying to connect locally. In MySQL, as well as most databases, you’ll have to specify which database you’re connecting to in $dsn, because MySQL access is specified on a per database level, and sometimes even on a per table level (in GRANT tables.) Assuming you do have access, you still need a username and password to connect.

Usernames And Passwords

  $user     = 'mysqlusername';
  $password = 'mysqlpassword';

Although MySQL generally attributes access on a per database level, users are created for all of MySQL. Therefore, you are entering a MySQL username and password. MySQL then proceeds to connect you to the specific datasource you requested, and grant you the access privileges your username and password afford you.

Final Hashref

Assuming you’ll be able to connect to the database, you can then modify some attributes in this final hashref. According to Tim Bunce, the hashref can be used ‘alter the default settings of PrintError, RaiseError, AutoCommit, and other attributes,’ even your mysql username and password (taking precedence over the previously specified ones.)

I’m Connected, Now What?

With DBI, you can retrieve, insert, and delete data from the database. Look out for future articles on how to do each of these. If you can’t wait until more articles come out, check out the more technical DBI documentation (http://search.cpan.org/~timb/DBI-1.38/DBI.pm), perlmonk’s (http://www.perlmonks.com), and try google-ing, too! Good luck, and we hope to see you when the next article comes out!

Author: Gyan Kapur