Question

This part of the SQLite Perl tutorial will show, how to create a database connection to the database.

The first step is to connect to the database. We use the connect() DBI method to establish a connection. The disconnect() method is used to close the database connection.

$dbh = DBI->connect($dsn, $username, $password)
  or die $DBI::errstr;
$dbh = DBI->connect($dsn, $username, $password, \%attr)
  or die $DBI::errstr;

The connect() method establishes a database connection to the requested data source. It returns a database handle object if the connection succeeds. We use the disconnect() method to terminate the connection.

The $dsn is the data source name. It is a string that tells the Perl DBI module, what kind of driver it should load and the location of the database to which the connection is going to be created.

dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port

The above strings are examples of data source names in Perl DBI.

dbi:SQLite:dbname=test.db

We are going to use this data source name. The dsn starts always with the dbi: substring. Then we have the driver name. In our case the driver name is SQLite. The third part is the database name. We will work with test.db throughout this tutorial.

dbi:SQLite:dbname=:memory:

We can also create a database in the memory with the above data source name.

We do not give $username and $password for the SQLite database. The database does not support it. We left two empty strings there. The final parameter is a reference to hash, in which we can set attributes to alter the default settings of a connection. For example the RaiseError attribute can be used to force errors to raise exceptions rather than return error codes. The HandleError attribute can be used to provide a subroutine which is called in case of error. The AutoCommit attribute sets or unsets the autocommit mode.

The $DBI::errstr is a DBI dynamic attribute which returns the native database engine error message. In case the connection fails, this message is displayed and the script is aborted.

Version

In the first code example, we will get the version of the SQLite database.

#!/usr/bin/perl

use strict;
use DBI;

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

my $sth = $dbh->prepare("SELECT SQLITE_VERSION()");
$sth->execute();

my $ver = $sth->fetch();

print @$ver;
print "\n";

$sth->finish();
$dbh->disconnect();

In the above Perl script we connect to the previously created test.db database. We execute an SQL statement which returns the version of the SQLite database.

use DBI;

We use the Perl DBI module to connect to the SQLite database.

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

Here we connect to the test.db database. The first parameter is the data source name. In the string we specify the database driver and the database name. The second and third parameters are empty. In other cases we provide the user name and the password there. The last parameter is the database options. We set the RaiseError option to 1. This will cause exceptions to be raised instead of returning error codes.

my $sth = $dbh->prepare("SELECT SQLITE_VERSION()");
$sth->execute();

The prepare() method prepares an SQL statement for later execution. The execute() method executes the SQL statement.

my $ver = $sth->fetch();

We fetch the data.

print @$ver;
print "\n";

We print the data that we have retrieved to the console.

$sth->finish();

Here we indicate that no more data will be fetched from this statement handle.

$dbh->disconnect();

We close the connection to the database.

$ ./version.pl
3.7.9

Executing the verion.pl script we get the version of the SQLite database.

Inserting data

We will create a Cars table and insert several rows to it.

#!/usr/bin/perl

use strict;
use DBI;

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

$dbh->do("DROP TABLE IF EXISTS Cars");
$dbh->do("CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, Price INT)");
$dbh->do("INSERT INTO Cars VALUES(1,'Audi',52642)");
$dbh->do("INSERT INTO Cars VALUES(2,'Mercedes',57127)");
$dbh->do("INSERT INTO Cars VALUES(3,'Skoda',9000)");
$dbh->do("INSERT INTO Cars VALUES(4,'Volvo',29000)");
$dbh->do("INSERT INTO Cars VALUES(5,'Bentley',350000)");
$dbh->do("INSERT INTO Cars VALUES(6,'Citroen',21000)");
$dbh->do("INSERT INTO Cars VALUES(7,'Hummer',41400)");
$dbh->do("INSERT INTO Cars VALUES(8,'Volkswagen',21600)");

$dbh->disconnect();

The above script creates a Cars table and inserts 8 rows into the table.

$dbh->do("CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, Price INT)");

The do() method executes the SQL statements. It combines two method calls, prepare() and execute() into one single call. The do() method is used for non-select statements.

$dbh->do("INSERT INTO Cars VALUES(1,'Audi',52642)");
$dbh->do("INSERT INTO Cars VALUES(2,'Mercedes',57127)");

These two lines insert two cars into the table. Note that by default, we are in the autocommit mode, where all changes to the table are immediately effective.

sqlite> .mode column  
sqlite> .headers on

We verify the written data with the sqlite3 tool. First we modify the way the data is displayed in the console. We use the column mode and turn on the headers.

sqlite> SELECT * FROM Cars;
Id      Name    Price   
----------  ----------  ----------
1       Audi    52642   
2       Mercedes  57127   
3       Skoda     9000    
4       Volvo     29000   
5       Bentley   350000  
6       Citroen   21000   
7       Hummer    41400   
8       Volkswagen  21600 

This is the data that we have written to the Cars table.

The last inserted row id

Sometimes, we need to determine the id of the last inserted row. In Perl DBI, we use the last_insert_id() method to find it.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(      
  "dbi:SQLite:dbname=:memory:", 
  "",              
  "",              
  { RaiseError => 1 },     
) or die $DBI::errstr;


$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");
  
my $id = $dbh->last_insert_id("", "", "Friends", "");
print "The last Id of the inserted row is $id\n";

$dbh->disconnect();

We create a Friends table in memory. The Id is automatically incremented.

$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)");

In SQLite, INTEGER PRIMARY KEY column is auto incremented. There is also an AUTOINCREMENT keyword. When used in INTEGER PRIMARY KEY AUTOINCREMENT a slightly different algorithm for Id creation is used.

$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");

These five SQL statements insert five rows into the Friends table.

my $id = $dbh->last_insert_id("", "", "Friends", "");

Using the last_insert_id() method, we get the last inserted row id.

$ ./lastrowid.pl
The last Id of the inserted row is 5

We see the output of the script.

Fetching data

In the last example of this chapter we fetch some data. More about data fetching will be discussed in the Queries chapter.

#!/usr/bin/perl

use strict;
use DBI;

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

my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id=1" );  
$sth->execute();
    
my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";

my $fields = $sth->{NUM_OF_FIELDS};
print "We have selected $fields field(s)\n";

my $rows = $sth->rows();
print "We have selected $rows row(s)\n";

$sth->finish();
$dbh->disconnect();

In the example we fetch a row from the Cars table. We will also find out how many fields & rows we have selected.

my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id=1" );  
$sth->execute();

We prepare an SQL statement with the prepare() method. The SQL string is sent to the SQLite database engine for processing. Its syntax and validity is checked. The method returns a statement handle. Then the SQL statement is executed. The data is prepared to be sent to the client program.

my ($id, $name, $price) = $sth->fetchrow();
print "$id $name $price\n";

The data is retrieved from the database with the fetchrow() method. The method returns one row from the table in form of a Perl list.

my $fields = $sth->{NUM_OF_FIELDS};

The NUM_OF_FIELDS is a statement handle attribute which gives us the number of returned fields. In our case we have three fields returned: Id , Name , and Price .

my $rows = $sth->rows();

We get the number of selected rows. We have retrieved only one row from the table. The rows() method returns the number of affected rows. It can be used for SELECT , UPDATE , and DELETE SQL statements.

$ ./fetchrow.pl 
1 Audi 52642
We have selected 3 field(s)
We have selected 1 row(s)

Output of the fetchrow.pl script.

In this chapter of the SQLite Perl tutorial, we have shown how to establish a database connection to the SQLite database. We have explained scripts which do some basic work with a database.