| 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 format is as follows: 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 the value ':memory:', then it will create an in-memory database in RAM that will only last for the duration of the session. If the filename filename is the actual device file name, then it will use this parameter value to try to open the database file. 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 blank strings, and the last parameter is used to pass various attributes. See the examples below for details. |
2 | ##$dbh->do($sql)This routine is prepared and executed A simple SQL statement. Returns the number of rows affected, or undef if an error occurred. A return value of -1 means that the number of rows is unknown, not applicable, or not available. Here, $dbh is the handle returned by the DBI->connect() call.
|
3| ##$dbh->prepare($sql) | This routine is the database engine Subsequent execution prepares a statement and returns a statement processing object.
| 4
##$sth->execute() | This routine performs any execution preparation The statement requires processing. Returns undef if an error occurs. If executed successfully, always returns true regardless of the number of rows affected. Here, $sth is processed by the statement returned by the $dbh->prepare($sql) call.
##5 | ##$sth->fetchrow_array()
| This routine gets the next row of data, And returned as a list containing the values of each field. In this list, Null fields will be returned as undef values. |
| 6 | $DBI::err
This is equivalent to $h->err. where $h is any processing type, such as $dbh, $sth, or $drh. This program returns the database engine error code for the last driver method called. |
##7 | $DBI::errstr
This is equivalent to $h->errstr. where $h is any processing type, such as $dbh, $sth, or $drh. This program returns the database engine error message for the last DBI method called. |
8 | ##$dbh->disconnect()This routine is closed before calling DBI- >connect() The database connection opened.
|
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 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 us 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 into the sqlite.pl file and execute it as shown below. If the database is created successfully, a message like the one shown below will be displayed:
$ chmod +x sqlite.pl
$ ./sqlite.pl
Open database successfully
Create table
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 create the COMPANY table in test.db and display the message shown below:
Opened database successfully
Table created successfully
Note: If 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 turned on the available dbdimp in the DBD-SQLite installation. c file, find the sqlite3_prepare() function, and change its third parameter 0 to -1. Finally, use make and make install to 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.
INSERT operation
The following Perl program shows how to create the COMPANY table created above 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(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 create the given record in the COMPANY table and will display the following two lines:
Opened database successfully
Records created successfully
SELECT Operation
Perl below The program shows how to get and display records from the COMPANY table created earlier:
#!/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
UPDATE operation
Below The Perl code shows how to update any record using the UPDATE statement and then get and display the updated record from the COMPANY 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(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
DELETE Operation
The following Perl code shows how to delete any record using the DELETE statement and then get and display the remaining records from the COMPANY 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 generates 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
