Guru: Retrieving Images From An SQL Table
July 10, 2017 Mike Larsen
In the first part of this series, I showed you how to load images from the IFS into a table that has a column 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.
Our final goal is to retrieve the images from the table we loaded in part 1 and write them back to the IFS. Once we complete this task, we should end up with the same five images we worked with in part 1. To keep everything clear, we write the images to a different folder in the IFS, one called photos_out.
This story contains code, which you can download here.
Table Photo_loco contains the location of the IFS folder we want to write the images to. It looks like this:
Our program starts by executing subroutine GetOutputLocation to load the PhotoOutputLocation variable with the location where we want to write the images.
Begsr GetOutputLocation;
// get the Ifs folder we're going to write the photos to
Exec sql
Select location into :PhotoOutputLocation
from Photo_loco;
Endsr;
Subroutine ProcessPhotos loops through the ‘Photos’ table and writes the images to the IFS. As a refresher, the Photos table we loaded in part 1 is shown below.
Begsr ProcessPhotos;
// read thru the table that holds the photos we want to write to the Ifs
Exec sql
Declare CsrC01 Cursor For
Select NameOfPhoto
from Photos;
Exec Sql
Close CsrC01;
Exec Sql
Open CsrC01;
DoU 1 = 0;
Exec Sql
Fetch Next From CsrC01 into :photosDs;
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 photos table, write the photo out to
// the ifs
Exsr WritePhotosToIfs;
Enddo;
Endsr;
In this subroutine, we declare and open a cursor for fetching the rows from the table. We only need the name of the photo we want to process, indicated by field NameOfPhoto. We’re fetching the name of the photo into a data structure called PhotoDs.
// - - - -
// photo location data structure
dcl-ds photosDs;
photoName char(10);
end-ds;
To keep this example simple, I haven’t coded any error handling, but I’ve indicated where it might go. For every row we read, we’ll execute subroutine WritePhotosToIfs. There isn’t much code in this subroutine, but there is a lot happening here and it’s important to understand what’s going on.
Begsr WritePhotosToIfs;
Photo_out_FO = SQFOVR;
Photo_out_NAME = %trim(PhotoOutputLocation) + %trim(photoName);
Photo_out_NL = %Len(%TrimR(Photo_out_NAME));
Exec sql
Select Photo
Into :Photo_Out
From Photos
Where NameOfPhoto = :photoName;
Endsr;
Before we get to the SQL select statement, it’s important to identify and understand where the fields Photo_out_FO, Photo_out_NAME, and Photo_out_NL come from. In this program, we’ve defined a variable called Photo_out that is defined with a SQL_type(BLOB_file) data type.
dcl-s Photo_out sqltype(Blob_file);
When the program is compiled, the SQL precompiler converts Photo_out to a data structure in the following format:
// The SQLTYPE(BLOB_FILE) definition will be converted by the compiler
// into the following data structure:
// D PHOTO_OUT DS
// D PHOTO_OUT_NL 10U 0
// D PHOTO_OUT_DL 10U 0
// D PHOTO_OUT_FO 10U 0
// D PHOTO_OUT_NAME 255A CCSID(*JOBRUNMIX)
Let’s break down the meaning of each of these.
- PHOTO_OUT_NAME is the name of the IFS file we’re creating. In this example, I made this the IFS output location plus the photo name.
- PHOTO_OUT_NL is the length of the value from PHOTO_OUT_NAME.
- PHOTO_OUT_DL is an output field and stands for data length. We’re not doing anything with this field in our program.
- PHOTO_OUT_FO is the type of the file open. We set this field to SQFOVR, which means create a new file or replace an existing one.
Once we populate the data structure with the proper values, we issue the SQL select statement that creates the image in the IFS.
After running this program, we can see our images loaded in the IFS folder ‘/mikel/photos’.
So, we’re done, right? Not exactly. Let’s download and open one of the photos to make sure we can still view the actual image. I’m going to use IBM i Access Client Solutions to download the file, then open it up to view it with Windows Photos. A look at the ‘lexie1.png’ image shows we have the same image that we loaded into the SQL table in part 1 of this series.
Working with images and other media types is pretty easy once you understand how to do it. I’m sure you can come up with other uses of this code. This concludes the series on storing and retrieving images on the IBM i. I hope you found this series both informative and helpful.
