May 16, 2021 SQLite
SQLite3 can be integrated with Perl using the Perl DBI module. T he 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.
Here are some 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, follow these steps:
$ 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
The following are important DBI programs to meet your needs for using the SQLite database in Perl programs. If you need more details, check out the official Perl DBI documentation.
| Serial number | API & Description |
|---|---|
| 1 |
DBI->connect($data_source, "", "", \%attr)
Create a database connection or session to the requested $ DATA_SOURCE.If the connection is successful, a database processing object is returned. Data source forms are as follows: DBI:SQLite:dbname='test.db' .Where SQLite is the SQLite driver name, Test.db is the name of the SQLite database file.If the file name filename Assign ':memory:' Then it will create a memory database in the RAM, which will only continue within the effective time of the session. If file name filename is the actual device file name, it will use this parameter value to try to open the database file.If the name of the name does not exist, you will create a new database file that named the name. You can keep the second and third parameters as blank strings, and the last parameter is used to pass various properties, see the following instance explanation. |
| 2 |
$dbh->do($sql)
This routine is ready and executes a simple SQL statement.Returns the number of rows affected, returns undef if an error occurs.Return Value-1 means that the number of rows is unknown, or not applicable, or not available.Here, $ dBH is the process returned by DBI-> Connect (). |
| 3 |
$dbh->prepare($sql)
This routine is subsequently executed by the database engine and returns a statement processing object. |
| 4 |
$sth->execute()
This routine performs processing that is required to perform pre-prepared statements.Returns Undef if an error occurs.If it is successful, it always returns true regardless of the number of rows affected.Here, $ STH is processed by the statement returned by $ dbh-> prepare ($ SQL). |
| 5 |
$sth->fetchrow_array()
This routine acquires the next row of data and returns in the form of a list containing each field value.In this list, the NULL field will be returned as the UNDEF value. |
| 6 |
$DBI::err
This is equivalent to $ H-> ERR.Among them, $ H is any processing type, such as $ dbh, $ st or $ drh.This program returns the database engine error code for the last calling driver (DRIVER) method. |
| 7 |
$DBI::errstr
This is equivalent to $ H-> Errstr.Among them, $ H is any processing type, such as $ dbh, $ st or $ drh.This program returns a database engine error message for the last calling DBI method. |
| 8 |
$dbh->disconnect()
This routine is turned off before calling DBI-> Connect () open database connections. |
The following Perl code shows how to connect to an existing database. If the database does not exist, it is created and a database object is 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 program above and create our database test in the .db. Y ou can change the path as needed. S ave the above code sqlite.pl file and execute as follows. If the database is successfully created, the message shown below is displayed:
$ chmod +x sqlite.pl $ ./sqlite.pl Open database successfully
The following Perl snippet will be used to create a table in a 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 executes, it creates a COMPANY .db in the test table and displays the message shown below:
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, found the sqlite3_prepare() function, and changed its third argument, 0, to -1. F inally, you can fix the problem by installing DBD::SQLite with make and make install. i n 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.
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 executes, it creates a given record in the COMPANY table and displays the following two lines:
Opened database successfully Records created successfully
The following Perl program shows how to get and display records from the COMPANY table you 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 procedure 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
The following Perl code shows how to use the UPDATE statement to update any record, and then get 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 procedure 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
The following Perl code shows how to use the DELETE statement to delete any records, and then get 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 procedure 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