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.
To work with this tutorial, we must have Perl language, SQLite database,
sqlite3 command line tool, Perl
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> install DBD::SQLite
The above commands show, how to install Perl DBI and DBD::SQLite modules.
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
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
.tables command gives a list of tables in the test.db
database. There are currently no tables. The
terminates the interactive session of the
sqlite3 command line tool.
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.
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
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
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.
We import the DBI module for our script.
my @ary = DBI->available_drivers();
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
Common DBI methods
The following table lists some common DBI methods.
|Returns a list of all available drivers|
|Establishes a connection to the requested data source|
|Disconnects from the database server|
|Prepares an SQL statement for execution|
|Executes the prepared statement|
|Prepares and executes an SQL statement|
|Associates a value with a placeholder in a prepared statement|
|Binds a Perl variable to an output field of a SELECT statement|
|begin_work()||Starts a new transaction|
|Writes the most recent series of uncommitted database changes to the database|
|Undoes the most recent series of uncommitted database changes|
|Quotes a string literal for use as a literal value in an SQL statement|
|Fetches all the rows and prints them|
|Fetches the next row as an array of fields|
|Fetches the next row as a reference array of fields|
|Fetches the next row as a reference to a hashtable|
|Fetches all data as an array of arrays|
|Finishes a statement and lets the system free resources|
|Returns the number of rows affected|
|Provides information about columns|
|Provides information about tables|
|Provides information about primary keys in tables|
|Provides information about foreign keys in tables|
Perl programmers usually use the following variable names when working with Perl DBI. In this tutorial we will adhere to these conventions too.
|Database handle object|
|Statement handle object|
|Driver handle object (rarely seen or used in applications)|
|Any of the handle types above ($dbh, $sth, or $drh)|
|General Return Code (boolean: true=ok, false=error)|
|General Return Value (typically an integer)|
|List of values returned from the database, typically a row of data|
|Number of rows processed (if available, else -1)|
|NULL values are represented by undefined values in Perl|
|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.