Table Of Contents....

OWA Blobs

Have you ever wanted to store a blob in the database, say an image or a spreadsheet, and retrieve it easily via the Oracle Web Server? Have you ever wanted to use the web to upload an image/document/whatever directly into the database or into the file system?

If so, you are in luck.

We have developed two web agent 'replacements' called

HOW it works:

Blob downloading

Blob downloading is straight forward. We have a table with a unique primary key (NAME) and three dependent fields, IMG_SIZE, MIME_TYPE, and IMAGE. The owai program (the download program) will take the NAME sent to it in the PATH_INFO variable and use that to perform an indexed read into the image table. This will fetch the MIME_TYPE, and IMAGE for that unique name out.

The OWAI routine will verify the MIME_TYPE in the database table against the list of valid mime types in the svXXXX.cfg file and if the mime_type does not exist in that file, will use the default mime type for that file type (it will use the URL and the extension in the URL to decide what mime type to associate with this request). So, the OWAI program will print out a mime header using the included mime_type field, for example: 

Content-Type: image/gif
The OWAI program will then piece wise fetch the IMAGE column blob out of the table. 

How to apply compression for downloads

If you use owaigz instead of just owai, pkzip compatible compression will be applied to the data stream. The mime type application/x-gzip will be associated with the downloaded file.

For example, if you enter the following URL: 

http://yourhost/someWebAgentName/owai/MySpreadSheet.xls
The following will happen: And if you enter the following URL: 
http://yourhost/someWebAgentName/owaigz/MySpreadSheet.xls
The following will happen:

Blob Uploading

Blob uploading is somewhat less straight forward. We are making use of a 'new' feature in HTML. If you build forms, then you know the following code: 
<form action=foo method=post>
<input type=submit>
</form>
That will cause all of the variable/value pairs from the form to be pushed onto the stdin of the cgi-bin program. The information will be 'escaped' (for example a space will be turned into a '+' and so on). This works fine for ascii text data, but not so well for lots of binary information.

What the owaup program makes use of is an extension on the form tag as follows: 

<form action=/someWebAgentName/owaup/foo method=post enctype="multipart/form-data">
<input type="file" name="p_file">
<INPUT TYPE="submit" >
<FORM>
This creates a form that looks like: 
Do not submit this form, It will go nowhere, use the browse... button to see what it does
If you do not see a button right after the text field with "Browse..." on it, your browser is not capable of uploading documents. AFAIK, only Netscape 2.x, 3.x, and 4.x support this.

There are two new features here. One is the multipart/form-data attribute on the form tag. The other is the input type=FILE tag. The multipart/form-data attribute causes the information from the form to be encoded in a fashion very different from the GET or the POST methods. It is encoded similar to the way a "multi-part, mime encoded" email would be sent. The normal Oracle Web Agent does not handle this type of data stream (and will crash if it is sent to it). That is where the owaup program comes in. It correctly interprets and parses the data stream and sends it to the pl/sql code. The input type=file tag simply allows the client to use the browser to browse their file system and pick files.

To see the flow of the data, lets assume on the above form you filled it in and pressed the submit button. Then what would happen is:

In short, your pl/sql routine will get a NAME of a file instead of the file itself. The NAME of the file is used as a primary key into the IMAGE table. You can link to the image table or do whatever you want to it.

Note, the insert of the file(s) into the database is part of your transaction. If your procedure rolls back, it will be un-inserted. If you commit, it will be commited. 

How to upload to the file system

Uploading to the file system is quite easy. The logic is to look for a variable named webAgentName_filedir variable in the svXXXX.app file. This variable has the following meaning:
webagentName_filedir NO If you want uploaded files to go into a file system subdirectory for a given web agent instead of INTO the database, you will set this variable. You will not set 'webagentName' but rather replace that with the name of your DCD. For example, to allow ows-bin to upload files into the subdirectory /tmp/ows-bin you would:  
                ows-bin_filedir = /tmp/ows-bin
                
Please note the lack of a trailing directory separator, it is intentional. Don't put a trailing / or \ on the directory name.
This will cause the uploaded files to be written into that directory. The files will still be uniquely named by this function (eg: they will still be named U1234567.ext where U is constant, 1234567 is a sequence that starts at 1 and is zero padded, and .ext is the original file extension of the uploaded file).

Currently, this works at the web agent level. A web agent (DCD) will either save files into the database or they will save them into this directory.

You may have more then one filedir variable in your svXXXX.app file but you should have only one per webagent. For example: 

        ows-bin_filedir = /tmp/ows-bin-stuff
        owa_dba_filedir = /tmp/owa_dba_stuff
is ok. 

How to upload/dnload to/from something other then the IMAGE table

This too is quite easy. The default table name for storing and retrieving images will continue to be 'IMAGE'. Image is a table defined in owarepl/sql/image.sql with the following structure: 
create table image
(
 NAME                                     VARCHAR2(255),
 MIME_TYPE                                VARCHAR2(30),
 IMG_SIZE                                                                 NUMBER,
 IMAGE                                    LONG RAW,
 constraint image_pk primary key( name )
)
pctfree 0
/
With previous versions, this table name was constant. Starting with release 1.2, you may override this name and make it be any value Oracle Tablename. Please note that the names of the columns within the table and their datatypes remain constant. You may take the above create table statement and change the name of the image table but leave the column definitions intact.
webagentName_image_tname NO If you want to rename the IMAGE table that the upload/download code uses. This variable setting will override the default of "IMAGE". For example, if you wanted your ows-bin webagent to save uploaded images into the table named foobar, you would:  
                ows-bin_image_tname = foobar
                
Please note the lack of a qualified username is intentional. Don't put a username on this tablename, the cartridge will always put the username of the webagent on the tablename to qualify it.

How to upload/dnload to/from an Oracle8/8I BLOB type (not long raw)

This too is quite easy. The default table for storing and retrieving images looks like:
create table image
(
 NAME                                     VARCHAR2(255),
 MIME_TYPE                                VARCHAR2(30),
 IMG_SIZE                                                                 NUMBER,
 IMAGE                                    LONG RAW,
 constraint image_pk primary key( name )
)
pctfree 0
/
All you need do is create the table with the IMAGE column being a BLOB type instead of long raw and then set the following svXXXX.app/wrb.app configuration parameter:
 
Parameter Name Mandatory How to use it
webagentName_use_lob NO set it equal to the single character Y.  For example if you wanted your ows-bin webagent to use a table with a blob, you would:
ows-bin_use_lob = Y
We will then use dbms_lob.read/write to access this column