SQLite - Perl

installation

SQLite3 can use Perl DBI Perl module integration. Perl DBI module is a database access module Perl programming language. It defines a set of methods, variables and rules provide a standard database interface.

The following shows the simple steps to install DBI module on Linux / 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 SQLite DBI driver can then be installed by following 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

DBI interface API

The following are important DBI program to meet your needs using SQLite database in Perl programs. If you need more details, please see the official Perl DBI documentation.

序号	API & 描述
1	DBI->connect($data_source, "", "", \%attr) 建立一个到被请求的 $data_source 的数据库连接或者 session。如果连接成功，则返回一个数据库处理对象。数据源形式如下所示：DBI:SQLite:dbname='test.db'。其中，SQLite 是 SQLite 驱动程序名称，test.db 是 SQLite 数据库文件的名称。如果文件名filename赋值为':memory:'，那么它将会在 RAM 中创建一个内存数据库，这只会在 session 的有效时间内持续。如果文件名 filename 为实际的设备文件名称，那么它将使用这个参数值尝试打开数据库文件。如果该名称的文件不存在，那么将创建一个新的命名为该名称的数据库文件。您可以保留第二个和第三个参数为空白字符串，最后一个参数用于传递各种属性，详见下面的实例讲解。
2	$dbh->do($sql) 该例程准备并执行一个简单的 SQL 语句。返回受影响的行数，如果发生错误则返回 undef。返回值 -1 意味着行数未知，或不适用，或不可用。在这里，$dbh 是由 DBI->connect() 调用返回的处理。
3	$dbh->prepare($sql) 该例程为数据库引擎后续执行准备一个语句，并返回一个语句处理对象。
4	$sth->execute() 该例程执行任何执行预准备的语句需要的处理。如果发生错误则返回 undef。如果成功执行，则无论受影响的行数是多少，总是返回 true。在这里，$sth 是由 $dbh->prepare($sql) 调用返回的语句处理。
5	$sth->fetchrow_array() 该例程获取下一行数据，并以包含各字段值的列表形式返回。在该列表中，Null 字段将作为 undef 值返回。
6	$DBI::err 这相当于 $h->err。其中，$h 是任何的处理类型，比如 $dbh、$sth 或 $drh。该程序返回最后调用的驱动程序（driver）方法的数据库引擎错误代码。
7	$DBI::errstr 这相当于 $h->errstr。其中，$h 是任何的处理类型，比如 $dbh、$sth 或 $drh。该程序返回最后调用的 DBI 方法的数据库引擎错误消息。
8	$dbh->disconnect() 该例程关闭之前调用 DBI->connect() 打开的数据库连接。

Connect to the database

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

#! / 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, create our databasetest.db in the current directory.You can change the path as needed. Save the above code to sqlite.pl file and press shown is performed as follows. If the database is successfully created, it will display the message shown below:

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

Create a table

The following Perl code snippet will be used to create a table in the database 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 (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 procedure, it creates COMPANY table intest.db 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 DBD-SQLite installed in the available dbdimp.c file, findsqlite3_prepare () function, and it is the third parameter 0 to -1.Finally, the use ofmake and make installto install DBD :: SQLite, you can 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 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 ();

The above program is executed, it will be created in the COMPANY table for a given record, and displays the following two lines:

Opened database successfully
Records created successfully

SELECT operations

The following Perl program shows how to get from the COMPANY table created earlier and displays the record:

#! / 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 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 shows how to use the UPDATE statement to update any records, then get from COMPANY table and display the updated record:

#! / 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 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 shows how to use the DELETE statement deletes any records, and then taken from the COMPANY table and displays the remaining recording:

#! / 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 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

Previous: SQLite - PHP

Next: SQLite - Python

SQlite Tutorial

SQLite Advanced Tutorial

SQLite interface

SQLite - Perl

installation

DBI interface API

Connect to the database

Create a table

INSERT operation

SELECT operations

UPDATE operation

DELETE operation