MySQL Perl tutorial

Course- MySQL >

MySQL & Perl DBI

In the first chapter of the MySQL Perl tutorial, we will introduce the Perl DBI module and the MySQL database. We will provide some definitions and show how to install the necessary elements.

Prerequisites

To work with this tutorial, we must have Perl language, MySQL database, Perl DBI and DBD::MySQL modules installed. The DBI is the standard Perl database interface. Each database has its driver. In our case, DBD::mysql is the driver for the MySQL database.

$ sudo perl -MCPAN -e shellcpan> install DBIcpan[2]> install DBD::mysql

The above commands show, how to install Perl DBI and DBD::mysql modules.

MySQL database

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one part of the very popular LAMP platform consisting of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Window,s or Mac OS. Wikipedia and YouTube use MySQL. These sites manage millions of queries each day. MySQL comes in two versions: MySQL server system and MySQL embedded system.

The MySQL comes with the mysql command line utility. It can be used to issue SQL commands against a database. Now we are going to use the mysql command line tool to create a new database.

$ sudo apt-get install mysql-server

This command installs the MySQL server and various other packages. While installing the package, we are prompted to enter a password for the MySQL root account. For installing MySQL from sources, have a look at MySQL installation page.

$ service mysql statusmysql start/running, process 1238

We check if the MySQL server is running. If not, we need to start the server.

$ sudo service mysql start

The above command is a common way to start MySQL if we have installed the MySQL database from packages.

$ sudo -b /usr/local/mysql/bin/mysqld_safe

The above command starts MySQL server using the MySQL server startup script. The way how we start a MySQL server might be different. It depends whether we have installed MySQL from sources or from packages and also on the Linux distro. For further information consult MySQL first steps or your Linux distro information.

Next, we are going to create a new database user and a new database. We use the mysql client.

$ mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 30Server version: 5.0.67-0ubuntu6 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema | | mysql | +--------------------+2 rows in set (0.00 sec)

We use the mysql monitor client application to connect to the server. We connect to the database using the root account. We show all available databases with the SHOW DATABASES statement.

mysql> CREATE DATABASE mydb;Query OK, 1 row affected (0.02 sec)

We create a new mydb database. We will use this database throughout the tutorial.

mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';Query OK, 0 rows affected (0.00 sec) mysql> USE mydb;Database changed mysql> GRANT ALL ON mydb.* to user12@localhost;Query OK, 0 rows affected (0.00 sec) mysql> quit;Bye

We create a new database user. We grant all privileges to this user for all tables of the mydb database.

Perl DBI

The Perl DBI (Database Interface) is a database access module for the Perl programming language. It defines a set of methods, variables and conventions that provide a standard database interface. The DBI is also responsible for the dynamic loading of drivers, error checking and handling, providing default implementations for methods, and many other non-database specific duties. The DBI dispatches method calls to the appropriate database driver. The DBD (Database Driver) is a Perl module which translates the DBI methods for a specific database engine. The database drivers are supplied by database vendors.

#!/usr/bin/perl use strict;use DBI; my @ary = DBI->available_drivers();print join("\n", @ary), "\n";

The code example lists all available drivers on our system.

use DBI;

We import the DBI module for our script.

my @ary = DBI->available_drivers();

The available_drivers() class method gets all the current available drivers on our system.

print join("\n", @ary), "\n";

This line prints the drivers to the console, each on a separate line.

$ ./available_drivers.pl DBMExamplePFileGoferProxySQLiteSpongemysql

Example output.

Common DBI methods

The following table lists some common DBI methods.

Method name	Description
available_drivers()	Returns a list of all available drivers
connect()	Establishes a connection to the requested data source
disconnect()	Disconnects from the database server
prepare()	Prepares an SQL statement for execution
execute()	Executes the prepared statement
do()	Prepares and executes an SQL statement
bind_param()	Associates a value with a placeholder in a prepared statement
bind_col()	Binds a Perl variable to an output field of a SELECT statement
begin_work()	Starts a new transaction
commit()	Writes the most recent series of uncommitted database changes to the database
rollback()	Undoes the most recent series of uncommitted database changes
quote()	Quotes a string literal for use as a literal value in an SQL statement
dump_results()	Fetches all the rows and prints them
fetchrow_array()	Fetches the next row as an array of fields
fetchrow_arrayref()	Fetches the next row as a reference array of fields
fetchrow_hashref()	Fetches the next row as a reference to a hashtable
fetchall_arrayref()	Fetches all data as an array of arrays
finish()	Finishes a statement and lets the system free resources
rows()	Returns the number of rows affected
column_info()	Provides information about columns
table_info()	Provides information about tables
primary_key_info()	Provides information about primary keys in tables
foreign_key_info()	Provides information about foreign keys in tables

Conventions

Perl programmers usually use the following variable names when working with Perl DBI. In this tutorial we will adhere to these conventions too.

Variable name	Description
$dbh	Database handle object
$sth	Statement handle object
$drh	Driver handle object (rarely seen or used in applications)
$h	Any of the handle types above ($dbh, $sth, or $drh)
$rc	General Return Code (boolean: true=ok, false=error)
$rv	General Return Value (typically an integer)
@ary	List of values returned from the database, typically a row of data
$rows	Number of rows processed (if available, else -1)
$fh	A filehandle
undef	NULL values are represented by undefined values in Perl
\%attr	Reference to a hash of attribute values passed to methods

This chapter of the MySQL Perl tutorial was an introduction to the Perl DBI module and the MySQL database.

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_namedbi:DriverName:database_name@hostname:portdbi: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.

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.pl5.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.plThe 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.pl1 Audi 52642We 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.

Perl Error handling in MySQL

In this chapter we will show, how we can handle errors.

Method name	Description
$h->err()	Returns the native database engine error code from the last driver method called.
$h->errstr()	Returns the native database engine error message from the last DBI method called.
$h->state()	Returns a state code in the standard SQLSTATE five character format.

The above three methods deal with error messages.

DBI dynamic attribute	Description
$DBI::err	Equivalent to $h->err()
$DBI::errstr	Equivalent to $h->errstr()
$DBI::state	Equivalent to $h->state()

The second table gives a list of DBI dynamic attributes, which are related to error handling. These attributes have a short lifespan. They should be used immediately after the method that might cause an error.

Default error handling

By default, the errors are returned by Perl DBI methods.

#!/usr/bin/perl use strict;use DBI; my $dsn = "dbi:mysql:dbname=mydb";my $user = "user12";my $password = "34klq*"; my $dbh = DBI->connect($dsn, $user, $password) or die "Can't connect to database: $DBI::errstr"; my $sth = $dbh->prepare( q{ SELECT Id, Name, Price FROM Cars } ) or die "Can't prepare statement: $DBI::errstr"; my $rc = $sth->execute() or die "Can't execute statement: $DBI::errstr"; while (my($id, $name, $price) = $sth->fetchrow()) { print "$id $name $price\n";} # check for problems which may have terminated the fetch earlywarn $DBI::errstr if $DBI::err; $sth->finish();$dbh->disconnect();

In the first script we deal with the default behaviour of returning error codes.

my $dbh = DBI->connect($dsn, $user, $password) or die "Can't connect to database: $DBI::errstr";

We call the connect() method to create a database connection. If the attempt fails, the method returns undef and sets both $DBI::err and $DBI::errstr attributes. The die() method prints the error message in case of a failure and terminates the script.

my $sth = $dbh->prepare( q{ SELECT Id, Name, Price FROM Cars } ) or die "Can't prepare statement: $DBI::errstr";

We call the prepare() statement. If the method fails, the die() method prints an error message and terminates the script.

my $rc = $sth->execute() or die "Can't execute statement: $DBI::errstr";

Again. We call the execute() method and check for errors. The method returns undef if it fails.

warn $DBI::errstr if $DBI::err;

We check for problems which may have terminated the fetch method early.

Raising exceptions

Checking for errors each time we call a DBI method may be tedious. We could easily forget to do so if we had a larger script. The preferred way of dealing with possible errors is to raise exceptions. To raise exceptions, we set the RaiseError attribute to true.

#!/usr/bin/perl use strict;use DBI; my $dsn = "dbi:mysql:dbname=mydb";my $user = "user12";my $password = "34klq*";my %attr = ( RaiseError => 1 ); my $dbh = DBI->connect($dsn, $user, $password, \%attr) or die "Can't connect to database: $DBI::errstr"; my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5"); $sth->execute(); while (my($id, $name, $price) = $sth->fetchrow()) { print "$id $name $price\n";} $sth->finish();$dbh->disconnect();

In the connection attributes, we set the RaiseError attribute to 1. When an error occurs, the exceptions are raised rather than error codes returned.

my %attr = ( RaiseError => 1 );

We set the RaiseError attribute to 1.

my $dbh = DBI->connect($dsn, $user, $password, \%attr) or die "Can't connect to database: $DBI::errstr";

The connect() method is the only method that we check for the return code.

my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5"); $sth->execute();

The prepare() and execute() methods do not check for the return error codes. If they fail, an exception is thrown and the Perl DBI will call the die() method and print the error message.

Error subroutines

With the HandleError connection handle attribute, we can set a reference to a subroutine, which is called when an error is detected. The subroutine is called with three parameters: the error message string that RaiseError and "PrintError" would use, the DBI handle being used, and the first value being returned by the method that failed (typically undef).

If the subroutine returns a false value then the RaiseError or PrintError attributes are checked and acted upon as normal.

#!/usr/bin/perl use strict;use DBI; my $dsn = "dbi:mysql:dbname=mydb";my $user = "user12";my $password = "34klq*";my %attr = ( RaiseError => 1, AutoCommit => 0, HandleError => \&handle_error ); my $dbh = DBI->connect($dsn, $user, $password, \%attr) or die "Can't connect to database: $DBI::errstr"; $dbh->do("UPDATE Cars SET Price=52000 WHERE Id=1");$dbh->do("UPDATE Car SET Price=22000 WHERE Id=8"); $dbh->commit(); $dbh->disconnect(); sub handle_error { $dbh->rollback(); my $error = shift; print "An error occurred in the script\n"; print "Message: $error\n"; return 1;}

Our own subroutine will handle the error.

my %attr = ( RaiseError => 1, AutoCommit => 0, HandleError => \&handle_error );

The HandleError attribute provides a reference to a handle_error() subroutine that is called, when an error is detected. The AutoCommit is turned off, which means that we work with transactions.

$dbh->do("UPDATE Car SET Price=22000 WHERE Id=8");

There is an error in the SQL statement. There is no Car table.

sub handle_error { $dbh->rollback(); my $error = shift; print "An error occurred in the script\n"; print "Message: $error\n"; return 1;}

This is the handle_error() subroutine. We print the error message. And return 1. If we returned 0 instead, additional error messages would appear. Returning 1 error messages associated with the RaiseError attribute are supressed.

$ ./errsub.plAn error occurred in the scriptMessage: DBD::mysql::db do failed: Table 'mydb.Car' doesn't exist

Output of the example.

The kosher example

According to the Perl DBI documentation, the most robust way to deal with DBI errors is to use the eval() method.

#!/usr/bin/perl use strict;use DBI;use DBI qw(:sql_types); my $dsn = "dbi:mysql:dbname=mydb";my $user = "user12";my $password = "34klq*";my %attr = ( RaiseError => 1, AutoCommit => 0 ); my $dbh = DBI->connect($dsn, $user, $password, \%attr) or die "Can't connect to database: $DBI::errstr"; my @data = ( [1, "Audi", 52642], [2, "Mercedes", 57127], [3, "Skoda", 9000], [4, "Volvo", 29000], [5, "Bentley", 350000], [6, "Citroen", 21000], [7, "Hummer", 41400], [8, "Volkswagen", 21601] ); eval { $dbh->do("DROP TABLE IF EXISTS Cars"); $dbh->do("CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INT)");}; my $sql = qq{ INSERT INTO Cars VALUES ( ?, ?, ? ) };my $sth = $dbh->prepare($sql); foreach my $row (@data) { eval { $sth->bind_param(1, @$row[0], SQL_INTEGER); $sth->bind_param(2, @$row[1], SQL_VARCHAR); $sth->bind_param(3, @$row[2], SQL_INTEGER); $sth->execute(); $dbh->commit(); }; if( $@ ) { warn "Database error: $DBI::errstr\n"; $dbh->rollback(); }} $sth->finish();$dbh->disconnect();

The above code example is the most correct way of dealing with errors.

my %attr = ( RaiseError => 1, AutoCommit => 0 );

We raise exceptions rather than check for return codes. We turn the autocommit mode off and manually commit or rollback the changes.

my $sql = qq{ INSERT INTO Cars VALUES ( ?, ?, ? ) };my $sth = $dbh->prepare($sql);

We guard against errors and security issues by using the placeholders.

eval { $sth->bind_param(1, @$row[0], SQL_INTEGER); $sth->bind_param(2, @$row[1], SQL_VARCHAR); $sth->bind_param(3, @$row[2], SQL_INTEGER); $sth->execute(); $dbh->commit();};

Inside the eval() method we put the error prone code. The method traps exceptions and fills the $@ special variable with error messages. We bind the variables to the placeholders with the bind_param() method.

if( $@ ) { warn "Database error: $DBI::errstr\n"; $dbh->rollback(); }

In case of an error, we print the error message and rollback the changes.

In this part of the MySQL Perl tutorial, we were discussing error handling.

PRV MENU NEXT