Tweet

Connect to a database

In this tutorial you will learn how to connect to a database, and how to perform various standard database actions.

The DBI module

    #!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;

All database connections in perl can be handled using the DBI module. Simply 'use' the module at the top of your script. You will also need to have the appropriate driver installed for the database you intend to use. For example, if you were using a MySQL database, you would need DBD::Mysql installed, or if you were using an Oracle database, you would need DBD::Oracle installed.

For the purposes of this tutorial we will assume a MySQL database.

The table used was created as follows:

    create table people_i_know (
    name varchar(40),
    age int,
    pet varchar(40));

And contained the following data:

    name       age     pet
    ========== ======= ====
    Carolyn    25      null
    Steve      23      cat
    Melissa    24      dog
    Ritchie    24      rabbit

Creating a database connection

    #
    # Replace the following values with those suitable for
    # your own environment
    #
    my $driver = "mysql";
    my $database = "test";
    my $user = "test"; 
    my $password = "test";

    my $dbh = DBI->connect(
        "DBI:$driver:$database",
        $user, $password,
    ) or die $DBI::errstr;

The code above creates a database handle called $dbh, which connects you to a mysql database called test, connecting as user 'test' using password 'test'.

You can optionally (and we recommend that you do) pass in a hash of options in your connect statement, for example:

    my $dbh = DBI->connect(
        "DBI:$driver:$database",
        $user, $password,
        {
            RaiseError => 1,
            PrintError => 1,
            AutoCommit => 0,
        }
    ) or die $DBI::errstr;

There are many more options. See:

    perldoc DBI

for a complete list.

If there was an error connecting to the databse, $DBI::errstr will contain the error message. How useful these error messages are depends on the driver used.

Selecting from the database

You first need to prepare the select statement. The handy thing about the prepare statement is that it creates a statement handle ($sth). This means you can execute the sql again later without having to redefine it.

    my $sth = $dbh->prepare("
        SELECT name, age, pet
          FROM people_i_know
         WHERE age > ?
    ") or die $dbh->errstr;

The question mark is a placeholder and is not necessary. You could also replace it with a hard-coded value (for example: 50). However, if you do not, it means you can execute the statement many times with different values:

    $sth->execute(70) or die $dbh->errstr;
    # more code here ...
    $sth->execute(50) or die $dbh->errstr;

There are many different ways you can retrieve data from a statement handle. The most common are quite simple and their use is shown below:

    my @row_array = $sth->fetchrow_array;
    my $array_ref = $sth->fetchrow_arrayref;
    my $hash_ref = $sth->fetchrow_hashref;

The first, fetchrow_array, will return each row in turn as an array. An example using data returned from the select above could be:

    while (my @row_array = $sth->fetchrow_array) {
        print $row_array[0], " is ", $row_array[1], " years old, and has a " , 
              $row_array[2], "\n";
    }

The second example is similar but returns an array reference rather than an array:

    while (my $array_ref = $sth->fetchrow_arrayref) {
        print $array_ref->[0], " is ", $array_ref->[1], 
              " years old, and has a " , $array_ref->[2], "\n";
    }

The third example, fetchrow_hashref, is often the most readable:

    while (my $hash_ref = $sth->fetchrow_hashref) {
        print $hash_ref->{name}, " is ", $hash_ref->{age}, 
              " years old, and has a " , $hash_ref->{pet}, "\n";
    }

Inserting into/Updating the database

In some ways much simpler that selecting:

    $dbh->do("
        INSERT INTO test 
                    (name, age, pet)
             VALUES ('Becky', 23, 'cat')
    ") or die $dbh->errstr;

Update statements can be done in the same way:

    $dbh->do("
        UPDATE test 
           SET age = 24
         WHERE name = 'Becky'
    ") or die $dbh->errstr;

However, like select statements, if you were to do many similar inserts or updates you could use a prepare and execute pattern:

    $sth = $dbh->prepare("
        INSERT INTO test 
                    (name, age, pet)
             VALUES (?, ?, ?)
    ") or die $dbh->errstr;

    $sth->execute('Becky', 23, 'cat') or die $dbh->errstr;

A full working example

Now here is a complete working example that connects to a database, selects some data and then disconnects.

    #!/usr/bin/perl
    # A working database example 
    use strict;
    use warnings;
    use DBI;

    my $driver = "mysql";   # Database driver type
    my $database = "test";  # Database name
    my $user = "";          # Database user name
    my $password = "";      # Database user password

    #
    # Connect to database
    #
    my $dbh = DBI->connect(
        "DBI:$driver:$database",
        $user, $password,
        {
            RaiseError => 1,
            PrintError => 1,
        }
    ) or die $DBI::errstr;

    #
    # Fetch data from the database
    # Get people over the age of 23
    #
    my $sth = $dbh->prepare("
        SELECT name, age, pet
          FROM people_i_know
         WHERE age > ?
    ") or die $dbh->errstr;

    $sth->execute(23) or die $dbh->errstr;

    #
    # Output the results
    #
    print "People over the age of 23:\n";
    while (my $results = $sth->fetchrow_hashref) {
        print $results->{name} . " is " . $results->{age};
        print " and has a " . $results->{pet} if ($results->{pet});
        print "\n";
    }

    #
    # Disconnect from database
    #
    $dbh->disconnect;

    exit 0;
[Top]