How can I solve this issue with the BLOB-Column?

Question

I have created a databasetable "Blob", which can contain Textfiles, XML-Files or zip-Files:

CREATE TABLE "RT"."BLOB" 
(   "B_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20),
        "L_ID" INTEGER NOT NULL,
        "ATTACHMENTS" BLOB(1048576) NOT NULL,
        "NAME" VARCHAR(600)
    )

I want to now that the name of the original files whose content has landed in the blob-column ("ATTACHMENTS") should be shown as a link on the JSP page. This goes without problems. If I click on the blob-Name (Column "NAME" in the database) in my JSP-Page, the blob should be loaded from the table and then either displayed accordingly depending on format - without additional treatment in the next JSP-Page. How can I could implement that in JSP? How should the INSERT-command to look for the blob table? I need urgend help. Thankss

Answer 1

In its present state, your JSP is attempting several steps, so it's important to figure out where the first failure is occurring. The way you're using the same physical file as the source of the INSERT and the target of the SELECT is making the troubleshooting process unnecessarily difficult. At the very least, use two different files so you can tell whether a specific SQL statement in the JSP is failing or succeeding.

You mention that the ATTACHMENTS BLOB column could contain a ZIP file. Based on your DDL, I assume the NAME column will reveal the file type of the attachment. A ZIP file or any other binary data cannot be dumped inline into an HTML document as your JSP is currently attempting. Returning actual binary data from a JSP requires some extra considerations that are described in another SO post: Display BLOB (image) through JSP.

As far as TXT attachments are concerned, a JSP could print them inline on an HTML page (see DtLob.java for examples), but even then you'll want to ensure the file's text encoding and/or code page are compatible with the surrounding HTML document. For instance, certain text encoding schemes start with a byte order mark that doesn't belong in the middle of a web page. For these and other reasons, I recommend storing text attachments as CLOBs instead of BLOBs if you intend to show their contents inline on HTML pages. For XML attachments displayed inline on a web page, you'll also need to escape the XML in a way that prevents the client browser from trying to render it as HTML markup.

Tangentially, I must point out that BLOB is a poor name for a table in any DBMS, even if it isn't a reserved word in DB2.