Perl DBI

last modified October 8, 2026

Perl DBI tutorial shows how to do database programming in Perl with DBI module.

In this article we work with a MariaDB database. Some examples may work with other databases, but SQL syntax, DSN formats, and driver behavior vary.

Note: While Perl DBI is database independent interface, there are a few attributes and SQL statements that are specific to MariaDB.

Some attributes are driver-specific; for example, the MariaDB driver's mariadb_multi_statements option enables multiple statements in one query. SQL syntax also varies by server: USE dbname is supported by MariaDB and MySQL, while SELECT VERSION() is not portable across database engines.

This tutorial presents Perl DBI with command-line examples. Several examples use DBI's RaiseError attribute for concise error handling; these examples are not a substitute for application-specific logging, recovery, and transaction policies, especially when modifying important data or serving concurrent users.

In general, the error messages should not be presented to users. They should be written to log files. All possible error messages should be logged; even if they are considered harmless. Usernames and passwords should be retrieved from configuration files or from environment variables.

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.

Other languages have created similar universal interfaces for working with databases. Java has JDBC and PHP has PDO.

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 that implements the DBI interface for a specific database engine. Driver modules are maintained separately from DBI, by database vendors or community contributors.

$ sudo cpanm DBD::MariaDB

In this article we work with the MariaDB database. We install the DBD::MariaDB module. The module is a MariaDB and MySQL driver for the Perl5 Database Interface.

drivers.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my @drvs = DBI->available_drivers;
say join "\n", @drvs;

The example lists the drivers available on the current system; the results vary with the installed DBI driver modules.

$ ./drivers.pl
DBM
ExampleP
File
Gofer
MariaDB
Mem
Proxy
Sponge
mysql

Perl 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`	Closes the database connection
`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 value for use as an SQL string literal; placeholders are generally preferred for values
`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 retrieving a statement's results; it may release driver resources
`rows`	Returns the number of rows affected or fetched when supported; for SELECT statements the result is driver-dependent and may be -1
`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

Perl DBI conventions

Perl programmers usually use the following variable names when working with Perl DBI.

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

Perl DBI database connection

The connect method is used to establish a connection to the database. 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 DBI what kind of driver it should load and the location of the database to which the connection is going to be created.

dbi:MariaDB:dbname=testdb;host=localhost;port=3306
dbi:Pg:dbname=testdb;host=localhost;port=5432

The part after the driver name is driver-specific; these are examples for MariaDB and PostgreSQL, respectively.

dbi:MariaDB:dbname=testdb

The dsn starts always with the dbi: substring. Then we have the driver name. In our case the driver name is MariaDB. The third part is the database name. We work with testdb throughout this tutorial.

The $username and the $password are the user name and the 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.

$DBI::errstr is a DBI dynamic attribute containing the most recent error message. If the connection fails, the example prints it and aborts.

Perl DBI ping

The ping method asks the driver whether an existing database handle appears to be alive. Its behavior is driver-specific; a successful ping does not guarantee that a later query will succeed.

pinging.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';

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

if ($dbh->ping) {

    say 'database is up';
} else {

    say 'database is down';
}

$dbh->disconnect;

The example checks whether the connected handle responds to the driver's ping method.

my $dsn = "dbi:MariaDB:";

We specify a MariaDB data source without a database name. Whether a driver can connect without selecting a database depends on that driver and server setup.

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

The connect method establishes a database connection, or session, to the requested datasource. It returns a database handle object if the connection succeeds. At the end we call disconnect to terminate the connection. If it fails, it returns undef and sets both $DBI::err and $DBI::errstr. We check the returned value with die.

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.

Perl DBI get_info

The get_info method returns details about a database.

version.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use DBI::Const::GetInfoType;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';

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

say $dbh->get_info($GetInfoType{SQL_DBMS_NAME});
say $dbh->get_info($GetInfoType{SQL_DBMS_VER});

$dbh->disconnect;

The example prints the name and the version of the database server.

use DBI::Const::GetInfoType;

The use statement imports a %GetInfoType hash which maps names for GetInfo Type Codes into their corresponding numeric values.

say $dbh->get_info($GetInfoType{SQL_DBMS_NAME});
say $dbh->get_info($GetInfoType{SQL_DBMS_VER});

We get the database server name and version.

$ ./version.pl
MariaDB
10.03.2400

The server version string depends on the MariaDB installation; this is sample output.

The next example connects to a PostgreSQL database and gets its version.

$ sudo apt install libpq-dev

On Debian or Ubuntu, install the PostgreSQL client development files before building the driver.

$ sudo cpanm DBD::Pg

We install the DBD::Pg driver.

version2.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use DBI::Const::GetInfoType;

my $dsn = "dbi:Pg:dbname=testdb";
my $user = "user7";
my $password = 's$cret';

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

say $dbh->get_info($GetInfoType{SQL_DBMS_NAME});
say $dbh->get_info($GetInfoType{SQL_DBMS_VER});

$dbh->disconnect;

The DBI driver name for PostgreSQL is Pg. This DSN names the database explicitly; accepted DSN attributes are driver-specific.

$ ./version2.pl
PostgreSQL
11.00.0500

The server version string depends on the PostgreSQL installation; this is sample output.

Perl DBI DBI_DSN

DBI checks the DBI_DSN environment variable if the DSN parameter is undef or empty.

dsn_env.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use DBI::Const::GetInfoType;

my $user = "user7";
my $password = 's$cret';

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

my $db_server_name = $dbh->get_info($GetInfoType{SQL_DBMS_NAME});
say $db_server_name;

In the example, we set the DSN to an empty string. The datasource details are retrieved from the DBI_DSN environment variable.

$ export DBI_DSN=dbi:MariaDB:
$ ./dsn_env.pl 
MariaDB

$ export DBI_DSN=dbi:Pg:dbname=testdb
$ ./dsn_env.pl 
PostgreSQL

Perl DBI fetch single field

In the next example, we determine the MariaDB server version with SELECT VERSION(). This statement is vendor-specific and returns a single value; use get_info when a portable DBI interface is needed.

mariadb_select_version.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';
my %attr = (PrintError => 0);

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

my $sth = $dbh->prepare("SELECT VERSION()")
    or die "failed to prepare statement $DBI::errstr";

$sth->execute or die "failed to execute statement $DBI::errstr";

my $row = $sth->fetch or die "failed to fetch data $DBI::errstr";

say $row->[0];

$dbh->disconnect;

The example executes an SQL statement which returns the version of the MariaDB database. By default, each of the DBI methods returns an error code; we check the code with the die method.

my $dsn = "dbi:MariaDB:";

For executing the SELECT VERSION() statement, the database name is not necessary.

my %attr = (PrintError => 0);

The PrintError attribute can be used to force errors to generate warnings (using warn) in addition to returning error codes in the default way. We set it to 0 to avoid duplicate error messages. (We are generating our own errors with die).

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

The connection attributes are passed as the fourth (optional) parameter to the connect.

my $sth = $dbh->prepare("SELECT VERSION()")
     or die "failed to prepare statement $DBI::errstr";

$sth->execute or die "failed to execute statement $DBI::errstr";

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

my $row = $sth->fetch or die "failed to fetch data $DBI::errstr";

The data is retrieved with fetch; it fetches the next row of data and returns a reference to an array.

say $row->[0];

We print first field of the row which contains the version of the database. The Perl array dereference syntax is used to get the field.

$dbh->disconnect;

We close the connection to the database.

$ ./mariadb_select_version.pl
10.3.24-MariaDB-2

Creating a table

The do method executes an SQL statement. It combines two method calls: prepare and execute into one single call. The do method is used for non-select statements.

create_cars.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attr = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attr);

$dbh->do("DROP TABLE IF EXISTS cars");
$dbh->do("CREATE TABLE cars(id INT PRIMARY KEY, name VARCHAR(255), price INT)");
$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;

say 'table cars created';

The example creates the cars table. It consists of non-select statements--all are executed with the do method.

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

Setting the RaiseError to 1 forces the errors to raise exceptions rather than return error codes. Therefore, we do not use the die functions for each statement. Now in case of an error, the script is terminated and the error is printed to the console.

$ ./create_cars.pl 
table cars created
     
MariaDB [testdb]> 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.000 sec)

We execute the program and check the data in the table.

Perl DBI prepared statements

Prepared statements use placeholders instead of directly writing the values into the statements. Placeholders keep data values separate from SQL syntax, preventing those values from being interpreted as SQL. They can also let a driver or server reuse a statement plan, but performance benefits depend on the driver and workload.

my $id = shift;

my $query1 = "SELECT * FROM cars WHERE id=$id";
my $query2 = "SELECT * FROM cars WHERE id=" . $id;
my $query3 = sprintf "SELECT * FROM cars WHERE id=%s", $id;

Interpolating untrusted values into SQL can allow SQL injection. Use placeholders for data values, as in the following example. Placeholders cannot stand for table or column names; dynamic identifiers require validation and proper identifier quoting.

prepared.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attr = (RaiseError => 1, PrintError => 0);

my $id1 = shift || 1;
my $id2 = shift || 2;
my $id3 = shift || 3;

my $dbh = DBI->connect($dsn, $user, $password, \%attr);

my $sth = $dbh->prepare("SELECT * FROM cars WHERE id IN (?, ?, ?)");
$sth->execute($id1, $id2, $id3);

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

$dbh->disconnect;

The example selects three rows from the cars table.

my $dsn = "dbi:MariaDB:dbname=testdb";

We are going to create a new database table so we need to provide a database name to connect. In our case, the database name is testdb specified with the dbname in the datasource.

my $sth = $dbh->prepare("SELECT * FROM cars WHERE id IN (?, ?, ?)");

We use three placeholders for values which are going to be bound later; the placeholders are denoted with question marks (?).

$sth->execute($id1, $id2, $id3);

The parameters passed to the execute method are bound to the placeholders.

$ ./prepared.pl 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
$ ./prepared.pl 2 4 7
2 Mercedes 57127
4 Volvo 29000
7 Hummer 41400

An alternative syntax for prepared statements is to use the bind_param method.

prepared2.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attr = (RaiseError => 1, PrintError => 0);

my $id1 = shift || 1;
my $id2 = shift || 2;
my $id3 = shift || 3;

my $dbh = DBI->connect($dsn, $user, $password, \%attr);

my $sth = $dbh->prepare("SELECT * FROM cars WHERE id IN (?, ?, ?)");

$sth->bind_param(1, $id1);
$sth->bind_param(2, $id2);
$sth->bind_param(3, $id3);

$sth->execute;

while (my($id, $name, $price) = $sth->fetchrow) {

    say "$id $name $price";
}

$dbh->disconnect;

In this example, the parameters are not bound by passing them to the execute method, but are bound with the bind_param method.

Perl DBI error handling

In some cases, proper error handling may require more time and energy than the actual work with the data. Automatic error handling provided by Perl DBI is fine for command line programs, provided they are run be knowledgeable users.

But when we deal with critical data, systems that run 24/7, or applications that are simultaneously used by thousands of users, the requirements for error handling are very different.

In general, errors are never reported directly to the users, but rather written to log files. Errors should be never ignored, even if they are considered harmless. Usernames and passwords are not written in programs, but retrieved from configuration files or environment variables.

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.

By default, Perl DBI reports errors with the warn function. This is controlled by the PrintError option, which is set to 1. In case of en error, it effectively does warn("$class $method failed: $DBI::errstr), where $class is the driver class and $method is the name of the method that failed.

The RaiseError attribute can be used to force errors to raise exceptions rather than simply return error codes in the default way. It is 0 by default. When set 1 any method which results in an error will cause the DBI to effectively do a die("$class $method failed: $DBI::errstr").

The current best practice is to set the RaiseError to 1 and handle the errors with the eval method, or by using Try::Tiny module.

Perl DBI 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.

The AutoCommit option controls if each statement is automatically committed, or the statements are run within a transaction. Each transaction is then finished with the commit or rollback method.

Note: In DBI, the AutoCommit option is set to 1 by default.

The begin_work method turns AutoCommit off for a transaction. After the next commit or rollback, DBI turns AutoCommit on again.

err_sub.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0, AutoCommit => 0, 
    HandleError => \&handle_error);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

$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;
    return 0;
}

We have two SQL UPDATE statements; the second statement has an error: the table name is incorrect. We handle the error inside the handle_error subroutine.

my %attrs = (RaiseError => 1, PrintError => 0, 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;
    return 0;
}

The handler rolls back and returns false, so DBI continues with the configured error handling. With RaiseError enabled, the failed statement then raises an exception; PrintError is disabled to avoid a duplicate warning.

$ ./err_sub.pl
DBD::MariaDB::db do failed: Table 'testdb.car' doesn't exist

The program fails with an error. Since both statements were run inside a single transaction, no row was updated.

Perl DBI error handling with eval

The eval function executes some Perl code, traps possible errors without crashing the program.

eval_error.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

eval {

    $dbh->begin_work;

    $dbh->do("UPDATE cars SET price=52000 WHERE id=1");
    $dbh->do("UPDATE cars SET price=22000 WHERE id=8");

    $dbh->commit;

} or do {

    warn "Database error: $DBI::errstr\n";
    $dbh->rollback;
};

$dbh->disconnect;

With begin_work, we put two update statements into a transaction.

eval {

    $dbh->begin_work;

    $dbh->do("UPDATE cars SET price=52000 WHERE id=1");
    $dbh->do("UPDATE cars SET price=22000 WHERE id=8");

    $dbh->commit;

} or do {
...

The statements are placed into eval block, which traps possible errors and prevents script from crashing.

} or do {

    warn "Database error: $DBI::errstr\n";
    $dbh->rollback;
};

When the eval fails, we rollback the transaction.

Perl DBI error handling with Try::Tiny

The Try::Tiny module allows us to use try/catch/finally block to handle errors. This is the traditional way of handling exceptions in languages such as C# or Java.

The module copies the $@ into the $_.

catching.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use Try::Tiny;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

try {
    $dbh->begin_work;

    $dbh->do("UPDATE cars SET price=52000 WHERE id=1");
    $dbh->do("UPDATE cars SET price=22000 WHERE id=8");
    
    $dbh->commit;

} catch {

    $dbh->rollback;
    warn "failed to update data\n$_";

} finally {

    $dbh->disconnect;
}

The error prone code is placed into the try block. If an error occurs, the catch block is executed. The finally block is always executed, regardless of error. There we place the code that cleans up resources.

Perl DBI fetching data

Data is retrieved from the database with the SELECT statement. In DBI, first we prepare the SQL statement with the prepare method. The SQL string is sent to the database engine, which checks the statement validity, syntax and in some databases also the user permissions to perform certain queries. If all is OK, a reference to the statement handle is returned.

The next step is the call to the execute method. The method executes the query within the database. At this moment the result stays in the database. The Perl script does not contain the data yet. For non-select statements, the execute method returns the number of rows affected if known. In the last step the data is fetched from the database. The data is pulled row by row and populated into the Perl data structures.

The Perl DBI has several methods to fetch data from database tables.

Method	Description
`fetchrow_arrayref`	Fetches the next row of data and returns a reference to an array.
`fetchrow_array`	Fetches the next row of data and returns it as a list.
`fetchrow_hashref`	Fetches the next row of data and returns it as a reference to a hash.
`fetchall_arrayref`	Fetches all data & returns a reference to an array that has one reference per row.
`fetch`	The method is an alias for `fetchrow_arrayref`.
`fetchrow`	The method is an alias for `fetchrow_array`.

After the SQL statement was prepared and executed, we call one of the available fetch methods.

Method	Description
`selectrow_arrayref`	Combines `prepare`, `execute` and `fetchrow_arrayref` into a single call.
`selectrow_hashref`	Combines `prepare`, `execute` and `fetchrow_hashref` into a single call.
`selectrow_array`	Combines `prepare`, `execute` and `fetchrow_array` into a single call.
`selectall_arrayref`	Combines `prepare`, `execute` and `fetchall_arrayref` into a single call.
`selectall_hashref`	Combines `prepare`, `execute` and `fetchall_hashref` into a single call.
`selectcol_arrayref`	Combines `prepare`, `execute` and fetching one col from all rows into a single call.

In the second table we have a list of utility methods which combine three methods into one call. They are convenience methods.

Perl DBI fetchrow

The fetchrow method gets the next row of data and returns it as a list.

fetch_single_row.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $id = shift || 1;

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->prepare("SELECT * FROM cars WHERE id=?");
$sth->execute($id);
      
my ($id, $name, $price) = $sth->fetchrow;
say "$id $name $price";

my $n_fields = $sth->{NUM_OF_FIELDS};
say "query contains $n_fields field(s)";

my $n_rows = $sth->rows;
say "We have selected $n_rows row(s)";

$dbh->disconnect;

The example gets and prints a single row from the cars table. In addition, we print the number of fields and rows retrieved.

my ($id, $name, $price) = $sth->fetchrow;
say "$id $name $price";

We get the row as a list and assign them into three variables.

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

We get the number of result columns with the NUM_OF_FIELDS statement-handle attribute.

my $n_rows = $sth->rows;

For SELECT statements, rows is driver-dependent and may be unknown (reported as -1) until all rows have been fetched. The sample output reflects a driver that reports the count.

$ ./fetch_single_row.pl 3
3 Skoda 9000
query contains 3 field(s)
We have selected 1 row(s)

In the next example, we get all rows from the table.

fetch_all_rows.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

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

while (my($id, $name, $price) = $sth->fetchrow) {
    
    say "$id $name $price";
}

$dbh->disconnect;

The fetchrow is placed inside the while loop to get all the rows. It returns false when there are no more rows to read.

while (my @row = $sth->fetchrow) {

     say "$row[0] $row[1] $row[2]";
}

This is an alternative syntax to get the rows with fetchrow.

$ ./fetch_all_rows.pl 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

Perl DBI fetchrow_hashref

The fetchrow_hashref method fetches the next row as a reference to a hashtable.

fetch_all_rows2.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

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

while (my $row = $sth->fetchrow_hashref) {

    say "$row->{id} $row->{name} $row->{price}";
}

$dbh->disconnect;

The example retrieves all rows with fetchrow_hashref.

while (my $row = $sth->fetchrow_hashref) {

     say "$row->{id} $row->{name} $row->{price}";
}

We get each row as a reference to a hash. We dereference the fields of the row with the arrow operator.

Perl DBI selectall_arrayref

The selectall_arrayref convenience method combines prepare, execute, and fetching all rows as array references into one call.

selectall_arrayref.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $rows = $dbh->selectall_arrayref("SELECT * FROM cars");

foreach my $row (@$rows) {
    
    my ($id, $name, $price) = @$row;
    say "$id $name $price";
}

$dbh->disconnect;

The example retrieves all rows from the cars table with selectall_arrayref. Each returned row is an array reference.

Perl DBI binding columns to variables

Column fields of SELECT statements can be bound to variables with bind_col and bind_columns methods.

bind_col.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my ($id, $name, $price);

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

$sth->bind_col(1, \$id);
$sth->bind_col(2, \$name);
$sth->bind_col(3, \$price);

while ($sth->fetch) {

    say "$id $name $price";
}

$dbh->disconnect;

In the example, we bind the three columns fields to the $id, $name, and $price variables with bind_col.

We can bind all fields in one go with bind_columns.

bind_columns.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

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

$sth->bind_columns(\my($id, $name, $price));

while ($sth->fetch) {

    say "$id $name $price";
}

$dbh->disconnect;

The bind_columns calls the bin_col method for each column of the SELECT statement. It tasks a list of variable references as its parameter. The number of variable references should match the number of columns in the SELECT statement.

Perl DBI multiple queries

MariaDB allows to execute multiple SQL statements in one query. The feature is turned off by default; we need to enable it with the mariadb_multi_statements option.

multiple_queries.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb;mariadb_multi_statements=1";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $query = qq{
    SELECT * FROM cars WHERE id=1;
    SELECT * FROM cars WHERE id=2;
    SELECT * FROM cars WHERE id=3;
};

my $sth = $dbh->prepare($query);
$sth->execute;

do  {

    while (my @row = $sth->fetchrow)
    {
        say "@row";
    }

} while ($sth->more_results);

$dbh->disconnect;

In the example, we execute three SELECT statement in a single query.

do  {

     while (my @row = $sth->fetchrow)
     {
          say "@row";
     }
     
} while ($sth->more_results);

The data is retrieved in a do/while loop. We check if there are additional result sets with more_results method.

Perl DBI insert image

In the next example, we insert an image into the table.

CREATE TABLE images(id INT PRIMARY KEY AUTO_INCREMENT, data MEDIUMBLOB);

We create the images table. For the images, we use the MEDIUMBLOB data type, which stores binary objects.

insert_image.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI qw(:sql_types);

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $filename = 'sid.jpg';

open(my $fh, "<:raw", $filename)
  or die "cannot open file $filename: $!";

my ($img_data, $buff);

while (1) {

    my $bytes = read $fh, $buff, 1024;
    die "failed to read file $filename: $!" unless defined $bytes;
    last unless $bytes;

    $img_data .= $buff;
}

my $stm = $dbh->prepare("INSERT INTO images(data) VALUES (?)");
$stm->bind_param(1, $img_data, SQL_BLOB);
$stm->execute;

close $fh or die "cannot close file $filename: $!";
$dbh->disconnect;

We read image data from a file and write it into the images table.

$stm->bind_param(1, $img_data, SQL_BLOB);

We import the SQL_BLOB type constant from DBI and pass it to bind_param to identify the value as binary data.

Perl DBI read image

The next example reads the image back from the database table and stores it into a file.

read_image.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $stm = $dbh->prepare("SELECT data FROM images WHERE id=1");
$stm->execute;

my $row = $stm->fetch or die "image not found";

my $filename = 'sid2.jpg';

open(my $fh, ">:raw", $filename)
  or die "cannot open file $filename: $!";

print {$fh} $row->[0] or die "cannot write file $filename: $!";
close $fh or die "cannot close file $filename: $!";
$dbh->disconnect;

The example uses the SELECT statement to retrieve the binary data.

Perl DBI metadata

Metadata describes database objects and query results. It includes information about tables and columns; row counts may also be available, depending on the statement and driver.

Method name	Description
`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

The above table lists four Perl DBI methods which are used to retrieve metadata.

primary_key_info.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->primary_key_info(undef, "testdb", "cars");
my @ary = $sth->fetchrow;

say "@ary";

$dbh->disconnect;

The primary_key_info returns an active statement handle that can be used to fetch information about columns that make up the primary key for a table.

$ ./primary_key_info.pl 
testdb cars id 1 PRIMARY

The table_info method returns an active statement handle that can be used to fetch information about tables and views that exist in the database.

$sth = $dbh->table_info($catalog, $schema, $table, $type);

The arguments may accept search patterns using the % character.

list_tables.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->table_info('', 'testdb', '', 'TABLE');

while (my $row = $sth->fetchrow_hashref) {

  say $row->{TABLE_NAME};
}

$dbh->disconnect;

We get all the tables in the testdb database with table_info.

my $sth = $dbh->table_info('', 'testdb', '', 'TABLE');

We specify that we want table names in the testdb database.

$ ./list_tables.pl 
cars
cities
countries
friends
images
writers

list_databases.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->table_info('', '%', '', '%');

my %seen;

while (my $row = $sth->fetchrow_hashref) {

  my $schema = $row->{TABLE_SCHEM};
  say $schema if defined $schema && !$seen{$schema}++;
}

$dbh->disconnect;

This example prints each schema/database name returned by the driver's metadata once. Schemas without matching tables may not be returned.

$ ./list_databases.pl 
information_schema
mydb
mysql
performance_schema
testdb

The last_insert_id returns a value identifying the row just inserted, if possible. Typically this would be a value assigned by the database server to a column with an auto_increment or serial type. It returns undef if the driver does not support the method or can't determine the value.

last_insert_id.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

$dbh->do("DROP TABLE IF EXISTS friends");
$dbh->do("CREATE TABLE friends(id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255))");
$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', '');
say "The last Id of the inserted row is $id";

$dbh->disconnect;

We create a new table. Then we determine the last inserted id.

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

In the following example, we print all rows from the cars table with their column names.

column_names.pl

#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

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

my $headers = $sth->{NAME};

my ($id, $name, $price) = @$headers;
printf "%s %-10s %s\n", $id, $name, $price;

while (my $row = $sth->fetchrow_hashref) {

    printf "%d %-10s %d\n", $row->{id}, $row->{name}, $row->{price};
}

$dbh->disconnect;

The records are aligned with the column names.

my $headers = $sth->{NAME};

The NAME statement handle attribute returns a reference to an array of field names for each column.

$ ./column_names.pl 
id name       price
 1 Audi       52000
 2 Mercedes   57127
 3 Skoda      9000
 4 Volvo      29000
 5 Bentley    350000
 6 Citroen    21000
 7 Hummer     41400
 8 Volkswagen 22000

In this article we have worked with the Perl DBI module.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Perl tutorials.

Ebooks

Perl DBI

Perl DBI

Perl common DBI methods

Perl DBI conventions

Perl DBI database connection

Perl DBI ping

Perl DBI get_info

Perl DBI DBI_DSN

Perl DBI fetch single field

Creating a table

Perl DBI prepared statements

Perl DBI error handling

Perl DBI error subroutines

Perl DBI error handling with eval

Perl DBI error handling with Try::Tiny

Perl DBI fetching data

Perl DBI fetchrow

Perl DBI fetchrow_hashref

Perl DBI selectall_arrayref

Perl DBI binding columns to variables

Perl DBI multiple queries

Perl DBI insert image

Perl DBI read image

Perl DBI metadata

Author