1.50. SQLite-Perl

1.50.1. Installation ¶

SQLite3 can be used Perl DBI The module is integrated with Perl. The Perl DBI module is the database access module of the Perl programming language. It defines a set of methods, variables, and rules that provide a standard database interface.

The installation on the Linux/UNIX machine is shown below DBI Simple steps for the module:

$ 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 

1.50.2. DBI interface API ¶

Here are some important DBI programs that can meet your needs for using SQLite databases in Perl programs. If you need more details, please check the Perl DBI official 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, a database processing object is returned.
	The data source is in the form of DBI:SQLite:dbname=’test.db’. Where 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 an in-memory database in RAM, which will only last for the duration of the session.
	If the file name filename is the actual device file name, it will try to open the database file with this parameter value. If a file with that name does not exist, a new database file with that name is created.
	You can leave the second and third parameters as blank strings, and the last parameter is used to pass various properties, as explained in the example below.
2	$dbh- > do ($sql)
	This routine prepares and executes a simple SQL statement. Returns the number of rows affected, or undef if an error occurs. The return value of-1 means that the number of rows is unknown, or not applicable, or not available. In this case, $dbh is the processing returned by the call DBI- > connect ().
3	$dbh- > prepare ($sql)
	This routine prepares a statement for subsequent execution of the database engine and returns a statement processing object.
4	$sth- > execute ()
	This routine performs any processing required to execute a prepared statement. Returns undef if an error occurs. If executed successfully, true is always returned regardless of the number of rows affected. In this case, $sth is handled by the statement returned by the $dbh- > prepare ($sql) call.
5	$sth- > fetchrow_array ()
	The routine takes the next row of data and returns it as a list containing the values of each field. In this list, the Null field is returned as a undef value.
6	$DBI::err
	This is equivalent to $h-> err. Where $h is any type of processing, such as $dbh, $sth, or $drh. The program returns the database engine error code for the last called driver (driver) method.
7	$DBI::errstr
	This is equivalent to $h-> errstr. Where $h is any type of processing, such as $dbh, $sth, or $drh. The program returns a database engine error message for the last called DBI method.
8	$dbh- > disconnect ()
	This routine closes the database connection that was opened by calling DBI- > connect ().

1.50.3. Connect to the database ¶

The following Perl code shows how to connect to an existing database. If the database does not exist, it will be created and 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 sqlite.pl File, and execute as shown below. If the database is created successfully, the message shown below is displayed:

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

1.50.4. Create a tabl ¶

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; } else { print "Table created successfully\n"; } $dbh->disconnect(); 

When the above program is executed, it will be executed in the test.db Create in COMPANY Table and displays the message shown below:

Opened database successfully Table created successfully 

注意： 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 and found the sqlite3_prepare() Function and change its third argument 0 to-1. Last use make And make install Install DBD::SQLite to 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.

1.50.5. INSERT operation ¶

The following Perl program shows how to create the COMPANY Create a record in the 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(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 is executed, it will be executed in the COMPANY The given record is created in the table and the following two rows are displayed:

Opened database successfully Records created successfully 

1.50.6. SELECT operation ¶

The following Perl program shows how to create the COMPANY Get and display records in the 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(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 

1.50.7. UPDATE operation ¶

The following Perl code shows how to use the UPDATE Statement to update any records, and then from the COMPANY Table to get and display updated records:

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

1.50.8. DELETE operation ¶

The following Perl code shows how to use the DELETE Statement to delete any records, and then from the COMPANY Get and display the remaining records in the 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 