What you have to know

Perl programming

Installing modules in Perl

Basic SQL

 

Introduction
The Perl DBI (DataBase Independent) module allows you to code your program to access a supported SQL database. As long as the SQL statements are standard, you can use the same code to access another supported SQL database with minor changes to the script.

The DBI module works with the DBD module for the SQL database. If you want to use say, a mySQL database, you will need to install the DBD::mySQL module. The DBD module provides the interface to the database and the DBI module. The figure below gives you an idea of how the DBI and DBD modules work. Although it just shows three database sources, you can use any database source as long as it has a DBD module.

When your program calls a DBI method with an SQL statement, the method is executed in the DBI module. The DBI module then goes to the DBD module and execute the corresponding method. The DBD module executes the SQL to whatever is requested. The result is returned to the DBI module which returns the result to the calling script.

Now for the Main Event
To use a database through the DBI module, you need to do the following steps:

• Connect to the database
• Prepare the SQL statement and return a statement handle
• Using the Statement handle, Execute the SQL statement
• If the SQL statement is a SELECT, use the Statement handle to get the SELECTed records
• Close the statement handle
• Close the connection

Template

use DBI;         # tell Perl
that you will use the DBI package
new $db = DBI->connect($dsn, $user, $password);  # DBI Connect
my $sth = $db->prepare($sql);  # Prepare the SQL statement
$sth->execute(@bindvalues);    # execute SQL statement

fetch statements if SQL statement is SELECT 

$sth->finish;    # close the statement handle
$db->disconnect; #close the database connection

Notes:

1. The $dsn variable will contain the database driver, databasename and host name. The format will depend on the database driver used.
2. The $user and $password variables are used if the database accessed is controlled based on defined users.
3. The prepare statement returns a statement handle. Use this statement handle for record specific methods, like fetching one or all records.
4. If your SQL statement will be used only once, you can combine the prepare and execute methods into a do statement. You normally use this on non-SELECT SQL statements. Example: $db->do($sql);

# This prepares and executes the SQL statement.

The @bindvalues array contains the data that are bound to the SQL statement. This will be explained later in this article.

Fetch methods are called from the statement handle returned from the prepare method. There are several Fetch methods that you can use. Each will depend on how you want to receive data from the database. If you want to fetch only a row, you can use the following methods:

• @row = $sth->fetchrow_array; #each column is returned as items in an array
• $ref = $sth->fetchrow_arrayref; #result is a reference to an array.
• $ref = $sth->fetchrow_hashref; #result is a reference to a hash. Column names are the keys of the array.
• $ref = $sth->fetchall_arrayref; #result is a reference to an array. The items in the array contain references to each row.

Sample Program
Let us say that you want to print all the contents of the Client .table in a mySQL database called ecomm using the userid concept and password of conceptpass in the localhost. Note that defining the userid and password to access the database is done on the database side, in this case using a mySQL administrator.

use DBI;
my $dsn = 'DBI:mysql:database=ecomm:host=localhost';
my $c = DBI->connect($dsn, 'concept', 'conceptpass');
if ($c) {
    my $sth = $c->prepare('SELECT * FROM Client');
    if ($sth->execute) {
        while (@row = $sth->fetchrow_array) {
            foreach (@row) {
                print "$_ -";
            }
        print "\n";
        }
    }
    $sth->finish;
}
$c->disconnect;

Binding Values
If you want to do several INSERTs to a table where only the values change, you can have your script doseveral INSERT statements. This approach is not efficient though because it has to prepare and execute the statement every time a do is requested.

A more efficient way is to prepare the statement, specifying that the values are to be changed everytime the SQL statement is executed. The values are then passed to the execute method. This way, you prepare the statement only once.

my $sth = $c->prepare('INSERT INTO CLIENT
	(NAME, ADDRESS) VALUES (?, ?)');
@bindvariables = ('Philip Yuson', 'Victoria BC');
$sth->execute(@bindvariables);

The example above shows how you pass the bind values to the execute method. If you have, say a comma-separated values file read from the STDIN that you want to insert into your table, you can write a code like this:

my $sth = $c->prepare('INSERT INTO CLIENT
	(NAME, ADDRESS) VALUES(?, ?)');
while (<>) {
	$sth->execute(split(',')); 
}

---

For more information on the DBI module:

Read your Perl DBI documentation.

Also read the DBD::driver documentation of your specific database driver for additional methods specific to your driver.