Home » Tutorials » Connect to db |
In this tutorial you will learn how to connect to a database, and how to perform various standard database actions.
#!/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
# # 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.
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"; }
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;
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;