YouTipSqlite Perl
# 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 simple steps to install the DBI module on a Linux/UNIX machine:
```bash
$ 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 these steps:
```bash
$ 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 routines that will fulfill your needs to use the SQLite database in your Perl program. If you need more details, please check the Perl DBI official documentation.
| 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 is in the form: **DBI:SQLite:dbname='test.db'**. Here, SQLite is the SQLite driver name, and test.db is the name of the SQLite database file. If the filename _filename_ is assigned the value **':memory:'**, it will create an in-memory database in RAM, which will only last for the duration of the session. If the filename is an actual device file name, it will try 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 empty strings, and the last parameter is used to pass various attributes, as explained in the example below. |
| 2 | **$dbh->do($sql)** This routine prepares and executes a simple SQL statement. It returns the number of rows affected, or undef if an error occurs. A return value of -1 means that the row count is unknown, not applicable, or not available. 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 any processing needed to execute a prepared statement. It returns undef if an error occurs. If execution is successful, it always returns true, regardless of the number of rows affected. 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 of field values. In this list, Null fields are returned as undef values. |
| 6 | **$DBI::err** This is equivalent to $h->err, where $h is any handle type, such as $dbh, $sth, or $drh. This routine returns the database engine error code from the last driver method called. |
| 7 | **$DBI::errstr** This is equivalent to $h->errstr, where $h is any handle type, such as $dbh, $sth, or $drh. This routine returns the database engine error message from the last DBI method called. |
| 8 | **$dbh->disconnect()** This routine closes a database connection previously opened with DBI->connect(). |
## Connecting to a 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.
```perl
#!/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 successfullyn";
Now, let's run the above program to create our database **test.db** in the current directory. You can change the path as needed. Save the above code in a file named sqlite.pl and execute it as shown below. If the database is created successfully, the following message will be displayed:
```bash
$ chmod +x sqlite.pl
$ ./sqlite.pl
Open database successfully
## Creating a Table
The following Perl code segment will be used to create a table in the previously created database:
```perl
#!/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 successfullyn";
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 disconnect();
When the above program is executed, it will create the COMPANY table in **test.db** and display the following message:
Opened database successfully
Table created successfully
**Note:** In case you see the 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 will have to open the dbdimp.c file available in the DBD-SQLite installation, find the **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:
```perl
#!/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 successfullyn";
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 successfullyn";
$dbh->disconnect();
When the above program is executed, it will create the given records in the COMPANY table and will display the following two lines:
Opened database successfully
Records created successfully
## SELECT Operation
The following Perl program shows how to fetch and display records from the COMPANY table created above:
```perl
#!/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 successfullyn";
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 fetchrow_array()) {
print "ID = ". $row . "n";
print "NAME = ". $row ."n";
print "ADDRESS = ". $row ."n";
print "SALARY = ". $row ."nn";
}
print "Operation done successfullyn";
$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 fetch and display the updated records from the COMPANY table:
```perl
#!/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 successfullyn";
my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv fetchrow_array()) {
print "ID = ". $row . "n";
print "NAME = ". $row ."n";
print "ADDRESS = ". $row ."n";
print "SALARY = ". $row ."nn";
}
print "Operation done successfullyn";
$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 fetch and display the remaining records from the COMPANY table:
```perl
#!/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 successfullyn";
my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv fetchrow_array()) {
print "ID = ". $row . "n";
print "NAME = ". $row ."n";
print "ADDRESS = ". $row ."n";
print "SALARY = ". $row ."nn";
}
print "Operation done successfullyn";
$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