Guru: Storing Images In An SQL Table, Part 1

June 26, 2017 Mike Larsen

In the first part of this series, we’ll look at how we can store images (or other media) in an SQL table on the IBM i. To accomplish this goal, we’ll write an RPG program that reads a table that has the name and location of the images from a folder in the IFS, and writes those images to a table.

The images will be stored in a column that is defined as a BLOB data type. ‘BLOB’ stands for ‘Binary Large Object’, and is a collection of binary data that is stored as a single entity in a database. Storing images in a table versus storing just the location of images in a table is something that is always heavily debated. The intent of this article is not to weigh in on that debate, but to show how to store images in a table if that’s the route you choose.

Before we get started, let’s look at the folder in the IFS that contains the images. It contains five photos of my dog.

Just so we can see we’re working with valid images, let’s look one of them. I download ‘lexie1.png’ from the IFS using IBM i Access Client Solutions and view it with Windows Photos.

Now that we know we have valid images to work with, we can dive into the program that will load these images into a table.

This story contains code, which you can download here.

In order to identify the images we’re interested in, I’ll be using table Photo_Loc, which has information about the images we’re looking to process from the IFS. We’re particularly interested in the name and location of the image.  For this example, the table that contains information about the images has been pre-loaded with the values we need.

The ‘Photo_Loc’ table was created with the ‘create table’ command, as shown below.

Create Table Photo_loc (

-- auto generated id field

   ID Numeric (5, 0) Generated always as Identity(
   start with 1 increment by 1 no minvalue no maxvalue
   no cycle no order cache 20) implicitly hidden,

-- photo name should be unique

   PhotoName   Char(10) Ccsid 37 not null default '' ,
   Description Char(25) Ccsid 37 not null default '' ,
   Location    Char(50) Ccsid 37 not null default '' ,

-- audit fields for when record was added

   AddDate Date not null default Current_Date,
   AddTime Time not null default Current_Time,
   AddPgm  Char(10) Ccsid 37 not null default '',
   AddUser Varchar(128) Allocate(18) Ccsid 37 not null default User,

-- audit fields for when record was updated

   UpdateDate Date not null default Current_Date,
   UpdateTime Time not null default Current_Time,
   UpdatePgm  Char(10) Ccsid 37 not null default '' ,
   UpdateUser Varchar(128) Allocate(18) Ccsid 37 not null default User,

   Constraint PK_ID_Photo_loc PRIMARY KEY("PHOTONAME"))
   RcdFmt Photo_locR;

  Label on Table Photo_loc
	 is 'Photo location table';

  Label on Column Photo_loc
  (
    ID          Text is 'id',
    PhotoName   Text is 'Photo Name',
    Description Text is 'Description of photo',
    Location    Text is 'Location of photo',
    AddDate     Text is 'Added date',
    AddTime     Text is 'Added time',
    AddPgm      Text is 'Added by program',
    AddUser     Text is 'Added by user',
    UpdateDate  Text is 'Updated date' ,
    UpdateTime  Text is 'Updated time',
    UpdatePgm   Text is 'Updated by program',
    UpdateUser  Text is 'Updated by user');

Grant Alter, Delete, Index, Insert, References, Select, Update
on Photo_loc to Public With Grant Option;

Grant Delete, Insert, Select, Update
on Photo_loc to Public;

Our goal is simple, and has three steps:

1. Read through the table that holds the image location.
2. Retrieve the image from the IFS.
3. Insert the image into PHOTOS, an SQL table that has a column defined with a BLOB data type.

Create Table Photos (

-- photo name should be unique

   NameOfPhoto Char(10) Ccsid 37 not null default '' ,
   Photo       Blob (2G),

-- audit fields for when record was added

   AddDate Date not null default Current_Date,
   AddTime Time not null default Current_Time,
   AddPgm  Char(10) Ccsid 37 not null default '',
   AddUser Varchar(128) Allocate(18) Ccsid 37 not null default User,

-- audit fields for when record was updated

   UpdateDate Date not null default Current_Date,
   UpdateTime Time not null default Current_Time,
   UpdatePgm  Char(10) Ccsid 37 not null default '' ,
   UpdateUser Varchar(128) Allocate(18) Ccsid 37 not null default User,

   Constraint PK_ID_Photos PRIMARY KEY("NAMEOFPHOTO"))
   RcdFmt PhotosR;

  Label on Table Photos
	 is 'Photos table';

  Label on Column Photos
  (
    NameOfPhoto Text is 'Photo Name',
    Photo       Text is 'Photo Blob',
    AddDate     Text is 'Added date',
    AddTime     Text is 'Added time',
    AddPgm      Text is 'Added by program',
    AddUser     Text is 'Added by user',
    UpdateDate  Text is 'Updated date' ,
    UpdateTime  Text is 'Updated time',
    UpdatePgm   Text is 'Updated by program',
    UpdateUser  Text is 'Updated by user');

Grant Alter, Delete, Index, Insert, References, Select, Update
on Photos to Public With Grant Option;

Grant Delete, Insert, Select, Update
on Photos to Public;

Let’s step through the code. The first step of the process, subroutine ProcessPhotos, reads the image location table, and for each row (record), executes subroutine WriteToSqlTable to store the image in a table.

Begsr ProcessPhotos;

  // read thru the table that holds the location of the photos in the Ifs

  Exec sql
    Declare CsrC01 Cursor For

      Select PhotoName, Location
             from Photo_loc;

  Exec Sql
     Close CsrC01;

  Exec Sql
     Open  CsrC01;

  DoU 1 = 0;

    Exec Sql
       Fetch Next From CsrC01 into :photoLocationDs;

       If SqlCode < *Zeros or SqlCode = 100;

          If SqlCode < *Zeros;
             //  Perform error handling
          EndIf;

          Exec Sql
             Close CsrC01;

          Leave;

       EndIf;

    // for each row we read in the photo location table, write a row to
    // the sql table that will store the image

    Exsr WriteToSqlTable;

  Enddo;

 Endsr;

In the ProcessPhotos subroutine, we use a cursor to fetch rows from the photo location table. The select statement includes the two columns (fields) we need for our process, the photo name and the photo location, which are fetched into a data structure.

// photo location data structure

dcl-ds photoLocationDs;
       photoNameDs char(10);
       locationDs  char(50);
end-ds;

For every row we fetch, we call subroutine WriteToSqlTable to insert the image into the PHOTOS table. After we’ve fetched the last row, we close the cursor. To keep the example simple, I haven’t coded any error handling, but I noted where it might be placed.

The bulk of the work takes place in the ‘WriteToSqlTable’ subroutine.

Begsr WriteToSqlTable;

    // write the photo to the Sql table as a BLOB data type

    Photo_in_FO   = SQFRD;
    Photo_in_NAME = locationDs;
    Photo_in_NL   = %len(%trimr(Photo_in_NAME));

    Exec sql
         Insert Into Photos
                     (NameOfPhoto, Photo, AddPgm, UpdatePgm)
              Values (:photoNameDs, :Photo_in, :ProgramId, :ProgramId);

 Endsr;

Although there isn’t much code here, there is a lot going on. Before we get to the SQL insert statement, it’s important to identify and understand where the fields Photo_in_FO, Photo_in_NAME, Photo_in_NL come from. In this program, we’ve defined a variable called Photo_in, which is defined with a SQL_type(BLOB_file) data type.

dcl-s Photo_in  sqltype(Blob_file);

The SQL precompiler converts the ‘Photo_in’ variable to a data structure in the following format.

//   D PHOTO_IN         DS
//   D PHOTO_IN_NL                    10U 0
//   D PHOTO_IN_DL                    10U 0
//   D PHOTO_IN_FO                    10U 0
//   D PHOTO_IN_NAME                 255A   CCSID(*JOBRUNMIX)

Let’s break down the meaning of each of these.

• PHOTO_IN_NAME is the name of the IFS file we’re reading.
• PHOTO_IN_NL is the length of the value from PHOTO_IN_NAME (the stream file we’re reading).
• PHOTO_IN_DL is an output field and stands for ‘data length’. We’re not doing anything with this field in our program.
• PHOTO_IN_FO is the type of the file open. We’re setting this field to ‘SQFRD’ which means we’re doing a ‘file read’.

Now that we’ve populated the data structure, we execute the SQL insert, which populates the Photos table with the photo name, photo, and the name of the program that wrote the record. I like to keep audit fields in all my tables to keep track of when the record was added or updated, as well as the user and program that performed the add or update. Having this information is great for audit purposes and for troubleshooting issues that may have occurred in the process.

After running the program, we end up with a table filled with images that can be used in other programs or processes. The resulting table is shown below.

We created five images in the table. One is .png format and the others are in .jpg format. The BLOB field that contains the images is called PHOTO.

Once you understand the pieces of the BLOB data type, writing images to it is easy. In part 2 of this series, we’re going to extract the images from the SQL table and write them back to the IFS. Stay tuned. . . .