Sqlite Perl n4h5a

SQLITE PERL TUTORIAL http://www.tutorialspoint.com/sqlite/sqlite_perl.htm

Copyright © tutorials point.com

Inst allat ion The SQLit e3 can be int egrat ed wit h Perl using Perl DBI module which is a dat abase access module for t he Perl programming language. It defines a set of met hods, variables and convent ions t hat provide a st andard dat abase int erface. Here are simple st eps t o inst all DBI module on your Linux/Unix machine: $ $ $ $ $ $

wget http://search.an.org/AN/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 t o inst all SQLit e driver for DBI, t hen it can be inst alled as follows: $ $ $ $ $ $

wget http://search.an.org/AN/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 Int erface APIs Following are import ant DBI rout ines which can sufice your requirement t o work wit h SQLit e dat abase from your Perl program. If you are looking for a more sophist icat ed applicat ion t hen you can look int o Perl DBI official document at ion.

S.N.

API & Descriptio n

1

DBI->co nnect($data_so urce, "", "", \%attr) Est ablishes a dat abase connect ion, or session, t o t he request ed $dat a_source. Ret urns a dat abase handle object if t he connect ion succeeds. Dat asource has t he form like : DBI: SQLite: dbname='test.db' SQLit e is SQLit e driver name and t est .db is t he name of SQLit e dat abase file. If t he filename is given as ': memo ry: ', it will creat e an in-memory dat abase in RAM t hat last s only for t he durat ion of t he session. If filename is act ual device file name, t hen it at t empt s t o open t he dat abase file by using it s value. If no file by t hat name exist s t hen a new dat abase file by t hat name get s creat ed. You keep second and t hird paramt er as blank st rings and last paramet er is t o various at t ribut es as shown below in t he example.

2

$dbh->do ($sql) This rout ine prepares and execut es a single SQL st at ement . Ret urns t he number of rows affect ed or undef on error. A ret urn value of -1 means t he number of rows is not known, not applicable, or not available. Here $dbh is a handle ret urned by DBI->connect () call.

3

$dbh->prepare($sql)

This rout ine prepares a st at ement for lat er execut ion by t he dat abase engine and ret urns a reference t o a st at ement handle object . 4

$sth->execute() This rout ine performs what ever processing is necessary t o execut e t he prepared st at ement . An undef is ret urned if an error occurs. A successful execut e always ret urns t rue regardless of t he number of rows affect ed. Here $st h is a st at ement handle ret urned by $dbh->prepare($sql) call.

5

$sth->fetchro w_array() This rout ine fet ches t he next row of dat a and ret urns it as a list cont aining t he field values. Null fields are ret urned as undef values in t he list .

6

$DBI: : err This is equivalent t o $h->err, where $h is any of t he handle t ypes like $dbh, $st h, or $drh. This ret urns nat ive dat abase engine error code from t he last driver met hod called.

7

$DBI: : errstr This is equivalent t o $h->errst r, where $h is any of t he handle t ypes like $dbh, $st h, or $drh. This ret urns t he nat ive dat abase engine error message from t he last DBI met hod called.

8

$dbh->disco nnect() This rout ine closes a dat abase connect ion previously opened by a call t o DBI>connect ().

Connect ing To Dat abase Following Perl code shows how t o connect t o an exist ing dat abase. If dat abase does not exist , t hen it will be creat ed and finally a dat abase object will be ret urned. #!/usr/bin/perl use DBI; use strict; my my my my my my

$driver = "SQLite"; $database = "test.db"; $dsn = "DBI:$driver:dbname=$database"; $id = ""; $ = ""; $dbh = DBI->connect($dsn, $id, $, { RaiseError => 1 }) or die $DBI::errstr;

print "Opened database successfully\n";

Now let 's run above program t o creat e our dat abase test.db in t he current direct ory. You can change your pat h as per your requirement . Keep above code in sqlit e.pl file and execut e it as shown below. If dat abase is successfully creat ed t hen it will give following message: $ chmod +x sqlite.pl $ ./sqlite.pl Open database successfully

Creat e a Table Following Perl program will be used t o creat e a t able in previously creat ed dat abase: #!/usr/bin/perl use DBI; use strict; my my my my my my

$driver = "SQLite"; $database = "test.db"; $dsn = "DBI:$driver:dbname=$database"; $id = ""; $ = ""; $dbh = DBI->connect($dsn, $id, $, { 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 above program is execut ed, it will creat e COMPANY t able in your test.db and it will display following messages: Opened database successfully Table created successfully

NOT E: in case you see following error in any of t he operat ion: DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398

In t his case you will have open dbdimp.c file available in DBD-SQLit e inst allat ion and find out sqlite3_prepare() funct ion and change it s t hird argument t o -1 inst ead of 0. Finally inst all DBD::SQLit e using make and do make install t o resolve t he problem.

INSERT Operat ion Following Perl program shows how we can creat e records in our COMPANY t able creat ed in above example: #!/usr/bin/perl use DBI; use strict; my my my my my my

$driver = "SQLite"; $database = "test.db"; $dsn = "DBI:$driver:dbname=$database"; $id = ""; $ = ""; $dbh = DBI->connect($dsn, $id, $, { 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;

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 above program is execut ed, it will creat e given records in COMPANY t able and will display following t wo line: Opened database successfully Records created successfully

SELECT Operat ion Following Perl program shows how we can fet ch and display records from our COMPANY t able creat ed in above example: #!/usr/bin/perl use DBI; use strict; my my my my my my

$driver = "SQLite"; $database = "test.db"; $dsn = "DBI:$driver:dbname=$database"; $id = ""; $ = ""; $dbh = DBI->connect($dsn, $id, $, { RaiseError => 1 }) or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(SELECT id, name, address, salary 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();

from COMPANY;);

When above program is execut ed, it will produce 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 Operat ion Following Perl code shows how we can use UPDATE st at ement t o updat e any record and t hen fet ch and display updat ed records from our COMPANY t able: #!/usr/bin/perl use DBI; use strict; my my my my my my

$driver = "SQLite"; $database = "test.db"; $dsn = "DBI:$driver:dbname=$database"; $id = ""; $ = ""; $dbh = DBI->connect($dsn, $id, $, { 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 above program is execut ed, it will produce 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 Operat ion Following Perl code shows how we can use DELETE st at ement t o delet e any record and t hen fet ch and display remaining records from our COMPANY t able: #!/usr/bin/perl use DBI; use strict; my my my my my my

$driver = "SQLite"; $database = "test.db"; $dsn = "DBI:$driver:dbname=$database"; $id = ""; $ = ""; $dbh = DBI->connect($dsn, $id, $, { 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 above program is execut ed, it will produce 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