Basic SQLite Tutorial

Advanced SQLite Tutorial

SQLite Program Interface

SQLite Perl

Installation

SQLite3 Perl DBI module can be integrated with Perl. The Perl DBI module is a 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 on Linux/Simple steps to install the DBI module on UNIX machines:

$ 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 following steps 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

Here are the important DBI programs that can meet your needs for using SQLite databases in Perl programs. If you need more details, please refer to the official Perl DBI documentation.

Serial Number	API & Description
1	DBI->connect($data_source, "", "", %attr) Establish a database connection or session to the requested $data_source. If the connection is successful, it returns a database handle object. The data source is as shown below:DBI:SQLite:dbname='test.db'. Among them, SQLite is the name of the SQLite driver, and test.db is the name of the SQLite database file. If the filename filename Is assigned to ':memory:'Then it will create a memory database in RAM, which will only last within the valid time of the session. If the filename filename is the actual device file name, it will try to open the database file using this parameter value. If the file with this name does not exist, a new database file named with this 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.
2	$dbh->do($sql) This subroutine prepares and executes a simple SQL statement. It returns the number of affected rows, or undefined if an error occurs. The return value -1 It means the number of rows is unknown, not applicable, or unavailable. In this case, $dbh is obtained from DBI-The returned handle is called by >connect().
3	$dbh->prepare($sql) This subroutine prepares a statement for subsequent execution of the database engine and returns a statement handle object.
4	$sth->execute() This subroutine performs any necessary processing required for executing any prepared statement. If an error occurs, it returns undefined. If it is executed successfully, it always returns true, regardless of the number of affected rows. In this case, $sth is obtained from $dbh-The returned statement handle is called by >prepare($sql).
5	$sth->fetchrow_array() This subroutine retrieves the next row of data and returns it as a list containing the values of each field. In this list, a Null field will be returned as an undefined value.
6	$DBI::err This is equivalent to $h->err. Among them, $h represents any processing type, such as $dbh, $sth, or $drh. This program returns the database engine error code of the last called driver method.
7	$DBI::errstr This is equivalent to $h->errstr. Among them, $h represents any processing type, such as $dbh, $sth, or $drh. This program returns the database engine error message of the last called DBI method.
8	$dbh->disconnect() This routine closes the DBI call before it is called->connect() opened database connection.

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.db. You 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 successfully created, the following message will be displayed:

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

Create table

The following Perl code snippet will be 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;
}
   print "Table created successfully\n";
}
$dbh->disconnect();

When the above program is executed, it will test.db Create the COMPANY table in the following message displayed:

Opened database successfully
Table created successfully

Note:If you encounter the following error in any operation:

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

In this case, you have already opened the dbdimp.c file in DBD-SQLite opened the available dbdimp.c file during installation, and found sqlite3_prepare() function, and change its third parameter 0 to -1Finally use make and make install Installing DBD::SQLite will solve the problem. in this case you will have open dbdimp.c file available in DBD-SQLite installation and find out 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 demonstrates how to create records in the COMPANY table that was previously created:

#!/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;
my $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;
my $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;
my $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 record in the COMPANY table and display the following two lines:

Opened database successfully
Records created successfully

SELECT operation

The following Perl program demonstrates how to retrieve and display records from the COMPANY table that was previously created:

#!/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 produces the following results:

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 demonstrates 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 produces the following results:

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 demonstrates 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 produces the following results:

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

SQLite PHP