Getting started with SQLite database in Perl

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects. [https://www.sqlite.org/about.html]

Following program demonstrates how to Create a new SQLite database, how to connect to a database with credentials, how to create a table, how to insert values and how to select values.

#!/usr/bin/perl
use DBI;
use strict;
use warnings;

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

print "Opened database successfully\n";

my $table = "SIEMENS";
my $stmt = qq(CREATE TABLE IF NOT EXISTS $table
   (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";
}

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

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

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

$stmt = qq(INSERT INTO $table (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";

$table = "PHILIPS";
$stmt = qq(CREATE TABLE IF NOT EXISTS $table
   (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););

$rv = $dbh->do($stmt);

if($rv < 0) {
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}

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

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

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

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

print "Records created successfully\n";

$stmt = qq(SELECT id, name, address, salary from PHILIPS;);
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->commit();
$dbh->disconnect();