Hi everybody,

once in a while people ask if you can add images (e.g. jpegs) from BLOB fields to WebFOCUS reports. The straight forward answer mostly is: yes for PDF, no for HTML.
The most recent Forum Link with some approaches from Wep5622 and J how to achieve such a BLOB-Image inclusion in HTML using servlets or using PDF.

I would like to present another technique which actually generates an MHT file + using techniques from the database.
So strictly speaking, this will not a plain HTML but an HTML wrapped in another format - but it is nowadays supported by many browsers - either directly or using plugins (e.g. unmht for firefox)

My example also uses Oracle and PL/SQL but hopefully this is easily portable to other DBs - I do not know:

You need to create 2 types + a table valued function in ORA first

 
-- SQL code originally from https://forums.oracle.com/message/10366452
-- adapted to match WF requirements

CREATE OR REPLACE TYPE ENCODE_TO_BASE64_RECORD as object (
  lines number,
  base64 varchar(4000 char)
);
/
CREATE OR REPLACE TYPE ENCODE_TO_BASE64_TABLE is table OF ENCODE_TO_BASE64_RECORD;
/

CREATE OR REPLACE 
FUNCTION encode_to_base64_wf( blobSource in blob )
  return ENCODE_TO_BASE64_TABLE pipelined 
as
        step    constant number := 2700;
        o_record ENCODE_TO_BASE64_RECORD := ENCODE_TO_BASE64_RECORD(0,'');
begin
        
        for i in 0..trunc( (DBMS_LOB.GetLength(blobSource) - 1)/step ) loop
                o_record.lines := i;
                o_record.base64 := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.SubStr(blobSource, step, i * step + 1)));
          pipe row (o_record);
        end loop;
        return;
end;
/

The function "encode_to_base64_wf" returns the base64 coded stream of the input BLOB parameter in a special form:
column "lines" is the "index" of the base64 lines (if sorting is necessary)
column "base64" will contain several base64 coded text-lines (64 chars each)

You could also convert lets say 45 chars -> base64 and pipe each row but the performance is much better using the function above.
+ Using WFs ALPHA format to dump the result into a file will produce exactly the same as you will see...

-* WebFOCUS part with a "CAR" example

TABLE FILE CAR
BY CAR
-* for this example I had uploaded an "AUDI" + "BMW" image to MyDB
WHERE CAR IN ('AUDI', 'BMW')
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS H_CARLIST FORMAT ALPHA
END
-SET &NUM_CARS = &LINES;
-RUN

-REPEAT L_LOOP1 FOR &IDX FROM 1 TO &NUM_CARS
-* readfile is always NOCLOSE (hopefully)
  -READFILE H_CARLIST
  -SET &CAR = TRUNCATE(&CAR);
  -SET &CAR.&IDX = &CAR;
  ENGINE SQLORA SET DEFAULT_CONNECTION MyDB
  SQL SQLORA
-* our blobs reside in a table called tmp_blob_test identified by "filename"
    select b64tab.lines, b64tab.base64 from tmp_blob_test, table(encode_to_base64_wf(tmp_blob_test.data)) b64tab where filename=&CAR.QUOTEDSTRING;
  TABLE FILE SQLOUT
  BY LINES NOPRINT
  PRINT BASE64/A4000
  ON TABLE HOLD AS H_CAR&IDX FORMAT ALPHA
  END
  -RUN
-L_LOOP1

TABLE FILE CAR
PRINT CAR COUNTRY
COMPUTE IMAGE/A96='<IMG SRC="file://pic_' || CAR || '.jpg" style="width:300px;height:auto">';
ON TABLE HOLD AS H_HTML FORMAT HTML
END
-RUN

SET HTMLFORMTYPE = MHT

-* The "CRLF" is necessary as some browsers (e.g. IE) do not like "empty" rows in MHT that contain a single space
-* unfortunately WF inside HTMLFORM creates one space for each otherwise row
-SET &CRLF=HEXBYT(13, 'A1') | HEXBYT(10, 'A1');


-HTMLFORM BEGIN
From: <Created by Me>
Subject: 
Date: Fri, 21 Jun 2013 15:41:36 +0200
MIME-Version: 1.0
X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7601.17609
Content-Type: multipart/related;
	boundary="----=_Part_4DC54EB_C12C6B5.1371822096933";
	type="text/html"&CRLF|
------=_Part_4DC54EB_C12C6B5.1371822096933
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit&CRLF
!IBI.FIL.H_HTML;
-REPEAT L_LOOP2 FOR &IDX FROM 1 TO &NUM_CARS
-SET &CAR = TRUNCATE(&CAR.&IDX);
&CRLF|------=_Part_4DC54EB_C12C6B5.1371822096933
Content-Location: file://pic_&CAR|.jpg
Content-Transfer-Encoding: base64
Content-Type: image/jpeg&CRLF|
!IBI.FIL.H_CAR&IDX|;
-L_LOOP2
&CRLF|------=_Part_4DC54EB_C12C6B5.1371822096933--
-HTMLFORM END
 

Each CAR gets its own base64 text holdfile which is added to one multipart of the output MHT. In the HTML part such an inline image can be referenced using the < img src="file://pic_AUDI.jpg" > tag - the image is automatically referenced by your browser.

Result: A table of all countries + cars where AUDI and BMW will show a nice image in column 3. (AUDI and BMW are of course chosen randomly - it has nothing to do with the car brands I prefer )

Please keep in mind that the included HTML in this multipart is "supposed" to contain 7bit chars only (see Content-Transfer-Encoding). If needed you can try "binary" or use an external app (-DOS) to convert the file (in TMPPATH) to quoted-printable or base64.

Hopefully this whole idea works for you, too.

Cheers Linne

Interesting approach.

My Oracle foo is a bit stale, but for cutting up that BLOB into multiple base64-encoded lines I think you can omit that stored procedure and use a recursive query instead.

Here's an attempt, but it gets stuck in the loop:

SELECT
--	least(level * 2700 +1, DBMS_LOB.GetLength(blobSource)) -2700 AS offset,
	UTL_RAW.cast_to_varchar2(
		UTL_ENCODE.base64_encode(
			DBMS_LOB.SubStr(blobSource, 2700, least(level * 2700 +1, DBMS_LOB.GetLength(blobSource)) -2700)
		)) AS line
  FROM tmp_blob_test
 WHERE filename = &CAR.QUOTEDSTRING
 CONNECT BY level * 2700 < DBMS_LOB.GetLength(blobSource)
;

Hi Wep5622,

thanks for your idea using CONNECT BY in ORA to get rid of the stored procedure I mentioned above.
I did not get it to work directly (ORA seems to be stuck in an endless loop when using CONNECT BY with GetLength). In an SQL expression my ORA instance also had problems with the 2700 -> 3600 byte length of the cast_to_varchar2 function (which indeed had worked in the stored proc - strange).
So I transformed the SQL to the following which worked pretty well

 
    select 
      LINES
    ,  UTL_RAW.cast_to_varchar2(
        UTL_ENCODE.base64_encode(
          DBMS_LOB.SubStr(data, 1200, (LINES * 1200) + 1)
        )
      ) AS BASE64
    from
      tmp_blob_test,
      (
        SELECT
          (level - 1) AS LINES,
          filename
        FROM ( 
            select 
              CEIL(DBMS_LOB.GetLength(data) / 1200) AS maxLine,
              filename
            from tmp_blob_test
            WHERE filename = &CAR.QUOTEDSTRING
          )
        CONNECT BY level <= maxLine
        group by level, filename
        order by level
      ) numsrc
    WHERE tmp_blob_test.filename = numsrc.filename;

Reducing the blocksize to 1200 (still divisible by 3 due to base64 reasons).

Cheers Linne