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.
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 insted 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.
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.
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.
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.