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:
- Read through the table that holds the image location.
- Retrieve the image from the IFS.
- 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. . . .