June 21, 2013, 12:28 PM
linnex[TECHNIQUE] HTML report with BLOB images
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
June 24, 2013, 04:45 AM
Wep5622Interesting 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)
;
July 16, 2013, 03:55 AM
linnexHi 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