Return a BLOB to a Browser

Article ID: 58108

Posted May 28th, 2009

Application Development

By:

Because HTTP is used so frequently with HTML, I sometimes forget that HTTP is really a file transfer protocol, and it can be used with any type of file. When I view a web page, I not only use HTTP to retrieve the HTML that makes up the body of the page, but I also use HTTP to download any pictures, sound files, videos, spreadsheets, PDFs, and so forth that are on that page.

Just as HTTP supports many types of media, so also does CGI. You can write a CGI program that returns any type of data you want. Perhaps you'd like to generate a spreadsheet on-the-fly and return it to the user. CGI won't generate the spreadsheet for you, but it will provide the means for the user to download it. It's not necessary to save the data to an IFS file and then return a link to the IFS file. CGI can return the data directly. This behavior has distinct advantages, such as the ability to code your own security into an application, and the elimination of temporary IFS files that would otherwise need to be cleaned up periodically.

In this article, I demonstrate this technique by using CGI with CGIDEV2 to download a JPEG picture from a BLOB in a database table. The BLOB gets read into an RPG program and then returned by that RPG program to the browser.

The Gist

Of course, when you have a JPEG file in the IFS, you can simply point a URL to it to view it. For example, I might display an image directly in the browser with the first URL below, or embed it into an HTML tag as shown in the second example:

http://www.example.com/images/ScottsHead.jpg

<img src="http://www.example.com/images/ScottsHead.jpg" />

What might be less obvious is that I can run a program that returns a JPEG instead of referencing a file directly.

http://www.example.com/clubtechp/viewjpg.pgm?empno=98730

<img src="http://www.example.com/clubtechp/viewjpg.pgm?empno=98730" />

These URLs are configured to run RPG programs that are loaded on my IBM i. The program is responsible for returning a stream of bytes that makes up a valid image so it can be viewed by the browser. The program will need to tell the HTTP server that the output is a JPEG image, and it'll have to write the bytes that make up that image to its standard output stream so the HTTP server can receive the data and return it to the browser.

If you've ever written a CGI program, you know that it's important to specify a line containing "Content-Type" at the top of your output. What you may not realize is that this content type is what tells the HTTP server and the browser what sort of data you're returning. Although you may be accustomed to returning HTML, you can return many other types of data, simply by specifying the correct content type.

Reading a BLOB in RPG

Reading a BLOB field from a database can be tricky because RPG in IBM i version 5.4 and earlier can store only up to 64KB in an alphanumeric string. That limitation makes it difficult to retrieve an image that can be as large as (in this example) 4MB. Although I can easily solve that problem by using dynamically allocated memory, the embedded SQL code that we typically use in ILE RPG can't read a BLOB into dynamic memory. It can only read a BLOB into an RPG-defined field or into an IFS file.

But, there's a trick . . .

The IFS support in the operating system can write to user spaces. User spaces are, under the covers, very much like dynamic memory. They perform just as well and can be addressed with a pointer, just like dynamic memory can. If I tell SQL it's outputting to an IFS file, and the IFS pathname points to a user space in QTEMP, I can have SQL write to a user space in QTEMP! Neat, huh?

Sample Code

This example is a continuation of the application described in Easy400 Improves Support for Uploads. It lets the HR department retrieve a picture of an employee, given the employee number.

What I need to do first is get the employee number from the URL. To do that, I use CGIDEV2's standard routines for retrieving input from a browser.

         qusbprv = 0;
         zhbGetInput(savedQuery: qusec);

         monitor;
           empno = %dec(zhbGetVar('empno'): 5: 0);
         on-error;
           return;
         endmon;

Now that I have the employee number, I use the User Space APIs to create a user space, and I use SQL to retrieve the employee's picture into that user space in QTEMP:

     D USRSPC          C                   'PHOTO     QTEMP'
     D HaveUserSpace   s              1n   inz(*OFF)
     D p_PhotoData     s               *
     D photo           s                   sqltype(blob_file)
           .
           .
         monitor;
            if (not HaveUserSpace);
               QUSDLTUS( USRSPC: qusec );
               QUSCRTUS( USRSPC
                       : 'JPG'
                       : 4 * 1024 * 1024
                       : x'00'
                       : '*EXCLUDE'
                       : 'Photo retrieved from SQL' );
               QUSPTRUS( USRSPC: p_PhotoData );
               HaveUserSpace = *ON;
            endif;
         on-error;
            HaveUserSpace = *OFF;
            return;
         endmon;
         .
         .
         photo_name = '/QSYS.LIB/' + %trimr(%subst(USRSPC:11)) + '.LIB/'
                                 + %trimr(%subst(USRSPC:1:10)) + '.USRSPC';
         photo_nl   = %len(%trimr(Photo_Name));
         photo_dl   = 0;
         photo_fo   = SQFOVR;

         exec SQL Select photo
                    into :photo
                    from EmpPhot
                   where empno = :empno;

The IFS pathname will result in /QSYS.LIB/QTEMP.LIB/PHOTO.USRSPC, but I built the name by using string operations, since I needed to specify it in four different places in the program, and I figured it would be better to use a named constant rather than hard-code it four times!

Whenever you code SQLTYPE(BLOB_FILE), the SQL preprocessor generates a data structure. The data structure starts with the name of your BLOB_FILE and contains the following fields:

xxxxx_name = path name of the IFS file to operate on.
xxxxx_nl = (name length) the length of the name in the preceding field
xxxxx_dl = (data length) when reading from a LOB (BLOB, CLOB or DBCLOB) this will be set to the length of the data extracted from the LOB
xxxxx_fo = (file options) whether the file should be created if it doesn't exist, overwritten if it does exist, etc. Here are the possible values:
- SQFRD = open IFS file for reading only
- SQFCRT = create IFS file if it doesn't exist, and open for writing
- SQFOVR = create IFS file if it doesn't exist, overwrite it if it does exist, and open for writing
- SQFAPP = append data to the end of an existing IFS file

When I called the Retrieve Pointer to User Space (QUSPTRUS) API, I assigned p_PhotoData as a pointer to the data in the user space. So once the SQL statement has loaded the photo into the user space, p_PhotoData will point to the start of that data, and photo_dl will contain the length of data located at that pointer. This means that I can use that pointer to write the data to the browser.

         if sqlstt='00000';
            data = 'content-type: image/jpeg' + x'1515';
            QtmhWrStout( %addr(data): %len(%trimr(data)): qusec );
            QtmhWrStout( p_PhotoData: photo_dl: qusec );
         endif;

The QtmhWrStout() API writes data to standard output. You'll note that I have to write to standard output manually--the CGIDEV2 routines are designed to help me with text data, but they're unsuitable for writing an image or similar media.

The first call to QtmhWrStout() tells the HTTP server, and ultimately the browser, that I'm going to send data in image/jpeg format. This is a far cry from the normal text/html content-type that specifies an HTML document! By saying image/jpeg, I'm telling the browser to expect binary data from an image. Writing two consecutive x'15' (newline) characters tells the HTTP server that I'm done writing keywords, and that I'm ready to send the actual data.

The second call to QtmhWrStout() specifies the actual data from the user space. This is how I return the actual image data from my program.