SQLite - Perl

Installation

SQLite3 can be integrated with Perl using the Perl DBI module. The Perl DBI module is the database access module for the Perl programming language. It defines a set of methods, variables, and rules that provide a standard database interface.

The following shows the simple steps to install the DBI module on a Linux/UNIX machine:

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625
$ perl Makefile.PL
$ make
$ make install

If you need to install the SQLite driver for DBI, you can follow the steps below to install it:

$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz
$ tar xvfz DBD-SQLite-1.11.tar.gz
$ cd DBD-SQLite-1.11
$ perl Makefile.PL
$ make
$ make install

DBI Interface API

The following are important DBI programs that can meet your needs for using the SQLite database in Perl programs. For more details, please refer to the official Perl DBI documentation.

Serial No.	API & Description
1	DBI->connect($data_source, "", "", \%attr) Establishes a database connection or session to the requested $data_source. If the connection is successful, it returns a database handle object. The data source takes the following form:DBI:SQLite:dbname='test.db'Here, SQLite is the name of the SQLite driver, and test.db is the name of the SQLite database file. If the file namefilenameis assigned':memory:'then it will create an in-memory database in RAM, which will only persist for the duration of the session. If the file name filename is an actual device file name, it will attempt to open the database file using this parameter value. If a file with that name does not exist, a new database file with that name will be created. You can leave the second and third parameters as blank strings, and the last parameter is used to pass various attributes; see the example explanation below for details.
2	$dbh->do($sql) This routine prepares and executes a simple SQL statement. It returns the number of affected rows, or undef if an error occurs. A return value of -1 means the number of rows is unknown, not applicable, or unavailable. Here, $dbh is the handle returned by the DBI->connect() call.
3	$dbh->prepare($sql) This routine prepares a statement for later execution by the database engine and returns a statement handle object.
4	$sth->execute() This routine performs whatever processing is necessary to execute a prepared statement. It returns undef if an error occurs. If executed successfully, it always returns true regardless of the number of affected rows. Here, $sth is the statement handle returned by the $dbh->prepare($sql) call.
5	$sth->fetchrow_array() This routine fetches the next row of data and returns it as a list containing the values of each field. In this list, Null fields are returned as undef values.
6	$DBI::err This is equivalent to $h->err. Here, $h is any handle type, such as $dbh, $sth, or $drh. This routine returns the database engine error code from the last-called driver method.
7	$DBI::errstr This is equivalent to $h->errstr. Here, $h is any handle type, such as $dbh, $sth, or $drh. This routine returns the database engine error message from the last-called DBI method.
8	$dbh->disconnect() This routine closes a database connection previously opened by calling DBI->connect().

Connect to Database

The following Perl code shows how to connect to an existing database. If the database does not exist, it will be created, and finally a database object will be returned.

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite"; 
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
                      or die $DBI::errstr;

print "Opened database successfully\n";

Now, let's run the above program to create our database in the current directory.test.dbYou can change the path as needed. Save the above code to the sqlite.pl file and execute it as shown below. If the database is created successfully, a message like the one shown below will be displayed.

$ chmod +x sqlite.pl
$ ./sqlite.pl
Open database successfully

Create Table

The following Perl code snippet is used to create a table in the previously created database:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL););
my $rv = $dbh->do($stmt);
if($rv < 0){
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}
$dbh->disconnect();

When the above program is executed, it willtest.dbcreate the COMPANY table in it, and display the following message:

Opened database successfully
Table created successfully

Note:If you encounter the following error in any operation: in case you see following error in any of the operation:

DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398

In this case, you have opened the available dbdimp.c file in the DBD-SQLite installation, and find sqlite3_prepare()function, and change its third parameter from 0 to -1. Finally usemakeandmake installinstall DBD::SQLite, and the problem will be solved. sqlite3_prepare() function and change its third argument to -1 instead of 0. Finally install DBD::SQLite using make and do make install to resolve the problem.

INSERT Operation

The following Perl program shows how to create records in the COMPANY table created above:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfully\n";
$dbh->disconnect();

When the above program is executed, it will create the given records in the COMPANY table and display the following two lines:

Opened database successfully
Records created successfully

SELECT Operation

The following Perl program shows how to retrieve and display records from the COMPANY table created earlier:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0){
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

When the above program is executed, it will produce the following result:

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

UPDATE Operation

The following Perl code shows how to use the UPDATE statement to update any record, and then retrieve and display the updated record from the COMPANY table:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
   print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0){
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

When the above program is executed, it will produce the following result:

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

DELETE Operation

The following Perl code shows how to use the DELETE statement to delete any record, and then retrieve and display the remaining records from the COMPANY table:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
   print "Total number of rows deleted : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0){
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

When the above program is executed, it will produce the following result:

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Other Extensions

SQLite Advanced Tutorial

SQLite Interfaces

SQLite - Perl

Installation

DBI Interface API

Connect to Database

Create Table

INSERT Operation

SELECT Operation

UPDATE Operation

DELETE Operation