Question

In this chapter of the SQLite Perl tutorial, we will work with image files. Note that some people argue against putting images into databases. Here we only show how to do it. We do not talk about whether to save images in databases or not.

sqlite> CREATE TABLE Images(Id INTEGER PRIMARY KEY, Data BLOB);

For this example, we create a new table called Images . For the images, we use the BLOB data type, which stands for Binary Large Object.

Inserting images

In the first example, we are going to insert an image to 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;

open IMAGE, "mushrooms.jpg" or die $!;

my ($image, $buff);
while(read IMAGE, $buff, 1024) {
  $image .= $buff;
}

my $stm = $dbh->prepare("INSERT INTO Images(Data) VALUES (?)");
$stm->bind_param(1, $image, DBI::SQL_BLOB);
$stm->execute();

close(IMAGE);
$stm->finish();
$dbh->disconnect();

We read an image from the current working directory and write it into the Images table of the SQLite test.db database.

open IMAGE, "mushrooms.jpg" or die $!;

We open an image. It is a JPG image called mushrooms.jpg .

my ($image, $buff);
while(read IMAGE, $buff, 1024) {
  $image .= $buff;
}

We read binary data from the image file.

my $sth = $dbh->prepare("INSERT INTO Images(Data) VALUES (?)");
$sth->bind_param(1, $image, DBI::SQL_BLOB);
$sth->execute();

The three code lines prepare the SQL statement, bind the image data to the statement and execute it.

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

Finally, we are releasing the resources.

Reading images

In this section, we are going to perform the reverse operation. We will read an image from the database table.

#!/usr/bin/perl

use strict;
use DBI;

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

my $stm = $dbh->prepare("SELECT Data FROM Images WHERE Id=1");
$stm->execute();
my $image = $stm->fetch();

print "Image: $image";

open IMAGE, ">mushrooms2.jpg" or die $!;
print IMAGE @$image;
close(IMAGE);

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

We read image data from the Images table and write it to another file, which we call mushrooms2.jpg.

my $sth = $dbh->prepare("SELECT Data FROM Images WHERE Id=1");
$sth->execute();
my $image = $sth->fetch();

These three lines select the image data from the table.

open IMAGE, ">mushrooms2.jpg" or die $!;
print IMAGE @$image;
close(IMAGE);

We open a new image file and write the retrieved data into that file. Then we close the file.

This part of the SQLite Perl tutorial was dedicated to reading and writing images.