12.2.1 Database Drivers (DBDs)

The success of DBI is that it is only half of the story. The other half is a DBD, or a database driver. DBI provides the interface and framework for the drivers, but it's the database drivers that do the real work. Drivers implement the DBI methods for the private interface functions of the corresponding database engine.

Unless you're developing a sophisticated database application, you probably don't care about the drivers except that you want to install the correct one. Table 12.1 lists database servers, where you can find them, and the DBD driver designed for it. (The freeware or shareware database servers are available for download, and some of the commercial servers offer evaluation copies for download.)

12.2.2 Creating a Database

Before you can open a connection to a database with DBI, you must create the database. DBI isn't able to do this step for you, although your DBD might allow you to. For example, DBD:mSQL provides a msqladmin function. Your DBD might also support the func method, which is used to call private (and often non-portable) methods in the driver. You could use a one-liner like this to create the database from the command line:

perl -MDBI -e '$db_name = q[database_name_here]; \
     $result = DBD::mysql::dr->func($db_name, '_CreateDB');'

perl -MDBI -e '$db_name = q[database_name_here]; \
    $result = DBD::mysql::dr->func($db_name, '_DropDB');'

12.2.3 Database Handles and Statement Handles

DBI methods work on two different types of handles: database handles and statement handles. A database handle is like a filehandle: connect is a DBI class method that opens a connection to a database and returns a database handle object.

$db_handle = DBI->connect(dbi:mSQL:bookdb, undef, undef)
    || die("Connect error: $DBI::errstr");

Changes to the database are written to the database automatically if the AutoCommit attribute is turned on. If AutoCommit is off, then use the commit method when you're ready to write the changes to the database.

AutoCommit is only one of many attributes that can be set for both database and statement handles. For example, if $st_handle is a statement handle, then you can set $st_handle->{NULLABLE} to determine if the fields can contain null characters. Table 12.2 is a listing of all the attributes supported by database handles, statement handles, or both.

12.2.4 Placeholders database and statement handles

Many database drivers allow you to use question marks as placeholders in SQL statements, and then bind values to the placeholders before executing them. This enables you to prepare a single statement with placeholders and then reuse it for each row of the database. For example, the prepare statement might read:

$st_handle = $db_handle->prepare(q{
	insert into books (isbn, title) values (?, ?)
})  || die db_handle->errstr;

$st_handle->execute("1-56592-286-7", "Perl in a Nutshell") 
	|| die $db_handle->errstr;