Rate this page

Flattr this

Connect to a SQLite database using Perl DBI

Content

Tested on

Debian (Etch, Lenny, Squeeze)
Ubuntu (Hardy, Intrepid, Jaunty, Karmic, Lucid, Maverick, Natty, Oneiric, Precise, Quantal)

Objective

To open an SQLite database using the Perl DBI module

Background

The DBI module is an abstraction mechanism by which a Perl script can interact with a database with minimal regard for which DBMS (in this case SQLite) is being used to host the database.

In order to use a particular database it is first necessary to connect to it. This gives you a connection handle which is needed when calling other DBI functions.

(For an embedded DBMS like SQLite this does not result in a network connection, but the DBI library refers to database sessions as connections regardless of how they are implemented.)

To open an SQLite database file it is necessary to use a copy of the library with a matching major version number. At the time of writing SQLite version 3 was recommended for new developments, but version 2 was still in widespread use.

Scenario

Suppose that you have a SQLite version 3 database file with the pathname foo.sqlite. You wish to access this from within a Perl script.

Method

The DBI module itself does not have the ability to communicate with any specific DBMS: for that it is necessary to install the appropriate back-end module. In the case of SQLite this is:

On Debian-based systems (including Ubuntu) the corresponding package names are libdbd-sqlite3-perl and libdbd-sqlite2-perl:

apt-get install libdbd-sqlite3-perl

On Redhat-based systems they are perl-DBD-SQLite and perl-DBD-SQLite2:

yum install perl-DBD-SQLite

In both of these cases, installing the back-end DBD package should automatically install the front-end DBI package as a dependency. Within Perl the dependency relationship is reversed: it is the DBI module that must be loaded explicitly, it then loads any required DBD modules as and when they are needed:

use DBI;

The connection to the database is opened using the function connect. It returns a connection handle, which is needed when making subsequent calls to the DBI module:

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.sqlite','','',{AutoCommit=>1,RaiseError=>1,PrintError=>0});

The first argument to connect is a string which specifies the required ‘data source’. For SQLite it should begin with the prefix dbi:SQLite: or dbi:SQLite2: as appropriate. Following this prefix is a list of settings separated by semicolons. Only one is needed in this instance, that being dbname which specifies the pathname of the database file. The file is created automatically if it does not already exist.

The second and third arguments would be the username and password to gain access to the database, except that SQLite has no need of them (access to the database being determined by whether you have access to the underlying file). The empty string is a suitable value for both.

The fourth argument is a hash ref containing a set of options. The documentation recommends that AutoCommit should always be specified explicitly. For most purposes it should be enabled in the first instance: if particular transactions require it to be disabled then this can be done later.

By default the option RaiseError is false and PrintError is true, meaning that errors are printed as warnings but do not raise exceptions. It is safer to reverse these defaults (as was done above), because then it is not necessary to explicitly check for errors after every DBI function call.

Testing

The script below opens a connection then tests it using a statement that does not depend on the content of the database:

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.sqlite','','',{AutoCommit=>1,RaiseError=>1,PrintError=>0});
print "2+2=",$dbh->selectrow_array("SELECT 2+2"),"\n";

It should produce the output:

2+2=4

(Note that a SELECT statement without a FROM clause is not standard SQL, so cannot necessarily be used for testing other types of database.)

Troubleshooting

Provided that you have enabled the RaiseError option (as recommended above), any serious problems should result in either a compilation error or an exception. By default these will be reported to stderr. Some of the more common errors are addressed in detail below.

If you are writing a CGI script then you should look in the web server error log. Alternatively, it may be feasible to execute the CGI script from the command line for testing purposes.

Errors

Can't locate DBI.pm

An error similar to:

Can't locate DBI.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.10.0 /usr/local/share/perl/5.10.0 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10 /usr/local/lib/site_perl .) at ./example.pl line 3.
BEGIN failed--compilation aborted at ./example.pl line 3.

could indicate that:

On Debian-based systems the DBI module is provided by the libdbi-perl package, and on Redhat by perl-DBI. In both of these cases it should be installed automatically as a dependency of the DBD package, so if this has not happened then it is quite likely that the DBD module has not been installed either. You can obtain a list of the installed DBI and DBD modules with the command:

dpkg --get-selections | grep "^libdb[id]-"

on Debian and

yum list installed | grep "^perl-DB[ID]"

on RedHat.

An issue with the include path would be unusual if you are using pre-packaged modules and have not overridden the default. You can check by inspecting the content of @INC:

print join "\n",@INC;

Bear in mind that the path seen by (for example) a CGI script will not necessarily be the same as for a script executed from the shell. For this reason, you should try to ensure that any script used for testing is invoked in exactly the same manner as the script being investigated.

Can't locate DBD/SQLite.pm

An error similar to:

install_driver(SQLite) failed: Can't locate DBD/SQLite.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.10.1 /usr/local/share/perl/5.10.1 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10 /usr/local/lib/site_perl .) at (eval 3) line 3.
Perhaps the DBD::SQLite perl module hasn't been fully installed,
or perhaps the capitalisation of 'SQLite' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge.
 at ./example.pl line 5

indicates that the DBI module has been successfully loaded, and that some DBD modules are available (listed on the penultimate line), but that the specific DBD module needed to access SQLite cannot be found. Possible explanations are that:

You can check the last point by inspecting the first line of the error message, where it gives the path to the module that could not be loaded. For connecting to SQLite it should be DBD/SQLite.pm or DBD/SQLite2.pm (upper case letters for the first three letters of SQLite, lower case for the remainder).

Missing dbi:driver: prefix

An error similar to:

Can't connect to data source 'bdi:SQLite:dbname=finance;host=db.example.com' because I can't work out what driver to use (it doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set) at ./example.pl line 5

probably indicates an error in the data source passed to DBI::connect. In this case it is a typo: the prefix dbi has been erroneously replaced with bdi.

File is encrypted or is not a database

An error similar to:

DBD::SQLite::db do failed: file is encrypted or is not a database at ./example.pl line 6.

could indicate one of the causes suggested by the message, or alternatively that the major version number of the library is older than the file (to the extent that it does not recognise that the file is a SQLite database).

If you encounter this error then it may be helpful to check what the file command makes of the file you are trying to open. You could also try opening it using an appropriate version of the SQLite command-line shell.

Unable to open database file

A message similar to:

DBI connect('dbname=foo.sqlite','',...) failed: unable to open database file at ./example.pl line 5

could indicate that:

See also

Further reading

Tags: perl | sqlite