Enabling BLOB support with JHeadstart – Uploading/Downloading files to and from a BLOB column

JHeadstart 10.1.3 has great support for Uploading and Downloading files up to and from Web Applications – and indirectly from the database. Compared to prior releases, JHeadstart 10.1.3 has a little less flexibility: it only supports File manipulation for ViewObject Attributes of one of the Oracle InterMedia Types (that will be MultiMedia starting with Oracle 11g), such as ORDSYS.ORDDOC. When your database column is of that type, your EntityObject and ViewObject will have attributes of the Java Class that is the perfect counterpart –  oracle.ord.im.OrdDocDomain.

However, many databases will have files stored in BLOB columns! And it seems like you are rather stuck with those when it comes to generating your JHeadstart application. I ran into that very same problem today, and was puzzled for some time. At one point I considered migrating all 100.000+ records from their BLOB home into an ORDSYS.ORDDOC column. I then focused on a Middle Tier solution:

• create a Transient Attribute MediaDocument of type OrdDocDomain in my ViewObject
• only use MediaDocument in the Web Application (base all JHeadstart Items on this attribute)
• in the getMediaDocument() method, create and return an OrdDocDomain object based on the BlobDomain attribute
• in the setMediaDocument() method, set the BlobDomain attribute, as well as the filename, mimetype and filesize attributes, from OrdDocDomain

Well, I more or less got it to work – but it was not straightforward. And as Steven Davelaar just informed me: using BlobDomain is disastrous for performance when used with large files, as the BlobDomain does not support streaming the file content); all the more reason for using OrdDocDomain in the middle tier! It turns out there is a much easier solution. It is largely server side and involves the on-the-fly-creation of ORDDOC objects in a SQL Query on the table with the BLOB column holding the files.

Let’s look at an example:

Create a table to store files (of course in reality you already have this table and are stuck with it, otherwise you would create a table with an ORDSYS type based column in it):

create table image
( id number(10)      not null
,      filename      varchar2(100)
,      img_size      number(10)
,      mime_type     varchar2(100)
,      creation_date date default sysdate
,      image         blob
)

Create a View on top of this table. The table has the Blob column – as well as file name, size and mime type – and the View has an OrdDoc column. They both have a Creation_Date column and an ID column:

create view VW_DOCUMENTS
as
select id
,      ORDSYS.ORDDoc( ORDSYS.ORDSource( image, null, null, filename, null, 1 ),
                      null, mime_type, img_size, null    ) document
,      creation_date                      
from   image
/

The ADF BC EntityObject Document is created on top of the View VW_DOCUMENTS. It has no knowledge whatsoever about the BLOB column. Now we can easily create a default ViewObject DocumentsView on top of the Entity.

Using JHeadstart, we quickly create a page to view, query, create/upload and update documents. Well, to be honest, we cannot just start creating, inserting and uploading documents: we need an INSTEAD OF trigger to deal with the insert into the View:

create or replace trigger AMIS_DOCUMENTS_IOIT
INSTEAD OF INSERT
ON VW_DOCUMENTS
FOR EACH ROW
declare
  l_filename varchar2(1000):= :new.filename;
  l_id       number(10):= :new.id;
BEGIN
    if l_filename is null
    then
      l_filename := :new.document.source.srcName;
    end if;
    if inserting
       and 
       l_id is null
    then
      select cxs_seq.nextval
      into   l_id
      from   dual;
    end if;
  INSERT INTO IMAGE
  (id, filename,  img_size, mime_type,image )
  VALUES
  ( l_id
  , l_filename
  , :new.document.contentLength
  , :new.document.mimeType
  , :new.document.source.localData
  );
END;
/

This one will deal with the insert. For the updates, we need a simpler one:

create or replace trigger AMIS_DOCUMENTS_IOUT
INSTEAD OF update
ON VW_DOCUMENTS
FOR EACH ROW
declare
BEGIN
  update IMAGE
  set    filename = :new.document.source.srcName
  ,      image    = :new.document.source.localdata
  ,      img_size = :new.document.contentLength
  ,      mime_type = :new.document.mimeType
  where  id = :new.id
  ;
END;
/

Note that a View with Instead Of triggers cannot just use the Refresh After Insert or Update feature (as they rely on the RETURNING clause that is not supported with Instead Of triggers). We have to override the isUseReturningClause() method in the EntityDefImpl, see: https://technology.amis.nl/blog/?p=1447 for the detailed solution for that issue.