Question

In the first chapter of the SQLite Perl tutorial, we will introduce the Perl DBI module and the SQLite 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, SQLite database, sqlite3 command line tool, Perl DBI and DBD::SQLite modules installed. The DBI is the standard Perl database interface. Each database has its driver. In our case, DBD::SQLite is the driver for the SQLite database.

$ sudo perl -MCPAN -e shell
cpan> install DBI
cpan[2]> install DBD::SQLite

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

SQLite database

SQLite is an embedded relational database engine. It is a self-contained, serverless, zero-configuration and transactional SQL database engine. SQLite implements most of the SQL-92 standard for SQL. The SQLite engine is not a standalone process. Instead, it is statically or dynamically linked into the application. An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy.

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

$ sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

We provide a parameter to the sqlite3 tool. The test.db is a database name. It is a single file on our disk. If it is present, it is opened. If not, it is created.

sqlite> .tables
sqlite> .exit
$ ls
test.db

The .tables command gives a list of tables in the test.db database. There are currently no tables. The .exit command terminates the interactive session of the sqlite3 command line tool. The ls Unix command shows the contents of the current working directory. We can see the test.db file. All data will be stored in this single file.

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
DBM
ExampleP
File
Gofer
Proxy
SQLite
Sponge
mysql

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 SQLite Perl tutorial was an introduction to the Perl DBI module and the SQLite database.