Connecting to the MySQL database with Perl

This part of the MySQL Perl tutorial will show, how to create a database connection to the database.

The first step is to connect to the database. We use the connect() DBI method to establish a connection. The disconnect() method is used to close the database connection.

$dbh = DBI->connect($dsn, $username, $password)
    or die $DBI::errstr;
$dbh = DBI->connect($dsn, $username, $password, \%attr)
    or die $DBI::errstr;

The connect() method establishes a database connection to the requested data source. It returns a database handle object if the connection succeeds. We use the disconnect() method to terminate the connection.

The $dsn is the data source name. It is a string that tells the Perl DBI module, what kind of driver it should load and the location of the database to which the connection is going to be created.

dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port

The above strings are examples of data source names in Perl DBI.

dbi:mysql:dbname=mydb

We are going to use this data source name. The dsn starts always with the dbi: substring. Then we have the driver name. In our case the driver name is mysql. The third part is the database name. We will work with mydb throughout this tutorial.

The $username and the $password are the user name and his password that are needed for authentication. The final parameter is a reference to hash, in which we can set attributes to alter the default settings of a connection. For example the RaiseError attribute can be used to force errors to raise exceptions rather than return error codes. The HandleError attribute can be used to provide a subroutine which is called in case of error. The AutoCommit attribute sets or unsets the autocommit mode.

The $DBI::errstr is a DBI dynamic attribute which returns the native database engine error message. In case the connection fails, this message is displayed and the script is aborted.

Version

In the first code example, we will get the version of the MySQL database.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare("SELECT VERSION()");
$sth->execute();

my $ver = $sth->fetch();

print @$ver;
print "\n";

$sth->finish();
$dbh->disconnect();

In the above Perl script we connect to the previously created mydb database. We execute an SQL statement which returns the version of the MySQL database.

use DBI;

We use the Perl DBI module to connect to the MySQL database.

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

Here we connect to the mydb database. The first parameter is the data source name. In the string we specify the database driver and the database name. The second parameter is the user name. The third parameter is the user password. The last parameter is the database options. We set the RaiseError option to 1. This will cause exceptions to be raised instead of returning error codes.

my $sth = $dbh->prepare("SELECT VERSION()");
$sth->execute();

The prepare() method prepares an SQL statement for later execution. The execute() method executes the SQL statement.

my $ver = $sth->fetch();

We fetch the data.

print @$ver;
print "\n";

We print the data that we have retrieved to the console.

$sth->finish();

Here we indicate that no more data will be fetched from this statement handle.

$dbh->disconnect();

We close the connection to the database.

$ ./version.pl
5.1.62-0ubuntu0.11.10.1

Executing the verion.pl script we get the version of the MySQL database.

Inserting data

We will create a Cars table and insert several rows to it.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",
    "34klq*",
    { RaiseError => 1}
) or die $DBI::errstr;

$dbh->do("DROP TABLE IF EXISTS Cars");
$dbh->do("CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, Price INT) ENGINE=InnoDB");
$dbh->do("INSERT INTO Cars VALUES(1,'Audi',52642)");
$dbh->do("INSERT INTO Cars VALUES(2,'Mercedes',57127)");
$dbh->do("INSERT INTO Cars VALUES(3,'Skoda',9000)");
$dbh->do("INSERT INTO Cars VALUES(4,'Volvo',29000)");
$dbh->do("INSERT INTO Cars VALUES(5,'Bentley',350000)");
$dbh->do("INSERT INTO Cars VALUES(6,'Citroen',21000)");
$dbh->do("INSERT INTO Cars VALUES(7,'Hummer',41400)");
$dbh->do("INSERT INTO Cars VALUES(8,'Volkswagen',21600)");

$dbh->disconnect();

The above script creates a Cars table and inserts 8 rows into the table.

$dbh->do("CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, Price INT) ENGINE=InnoDB");

The do() method executes the SQL statements. It combines two method calls, prepare() and execute() into one single call. The do() method is used for non-select statements.

$dbh->do("INSERT INTO Cars VALUES(1,'Audi',52642)");
$dbh->do("INSERT INTO Cars VALUES(2,'Mercedes',57127)");

These two lines insert two cars into the table. Note that by default, we are in the autocommit mode, where all changes to the table are immediately effective.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Price  |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.01 sec)

This is the data that we have written to the Cars table.

The last inserted row id

Sometimes, we need to determine the id of the last inserted row. In Perl DBI, we use the last_insert_id() method to find it.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

$dbh->do("DROP TABLE IF EXISTS Friends");
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT, Name TEXT)");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");
    
my $id = $dbh->last_insert_id("", "", "Friends", "");
print "The last Id of the inserted row is $id\n";

$dbh->disconnect();

We create a new Friends table. The Id is automatically incremented.

$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT, Name TEXT)");

This is the SQL statement to create a Friends table. The AUTO_INCREMENT attribute is used to generate a unique id for new rows.

$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");

These five SQL statements insert five rows into the Friends table.

my $id = $dbh->last_insert_id("", "", "Friends", "");

Using the last_insert_id() method, we get the last inserted row id.

$ ./last_rowid.pl
The last Id of the inserted row is 5

We see the output of the script.

Fetching data

In the last example of this chapter we fetch some data. More about data fetching will be discussed in the Queries chapter.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id=1" );  
$sth->execute();
      
my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";

my $fields = $sth->{NUM_OF_FIELDS};
print "We have selected $fields field(s)\n";

my $rows = $sth->rows();
print "We have selected $rows row(s)\n";

$sth->finish();
$dbh->disconnect();

In the example we fetch a row from the Cars table. We will also find out how many fields & rows we have selected.

my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id=1" );  
$sth->execute();

We prepare an SQL statement with the prepare() method. The SQL string is sent to the MySQL database engine for processing. Its syntax and validity is checked. The method returns a statement handle. Then the SQL statement is executed. The data is prepared to be sent to the client program.

my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";

The data is retrieved from the database with the fetchrow() method. The method returns one row from the table in form of a Perl list.

my $fields = $sth->{NUM_OF_FIELDS};

The NUM_OF_FIELDS is a statement handle attribute which gives us the number of returned fields. In our case we have three fields returned: Id, Name, and Price.

my $rows = $sth->rows();

We get the number of selected rows. We have retrieved only one row from the table. The rows() method returns the number of affected rows. It can be used for SELECT, UPDATE, and DELETE SQL statements.

$ ./fetchrow.pl
1 Audi 52642
We have selected 3 field(s)
We have selected 1 row(s)

Output of the fetchrow.pl script.

In this chapter of the MySQL Perl tutorial, we have shown how to establish a database connection to the MySQL database. We have explained scripts which do some basic work with a database.