Add blobs in your database

For some reason, I always overlooked the possibility to include pictures in a database. I decided to overcome this flaw and started experimenting with it.
The first step was to create a table in Access. In Access, we have the possibility to use the datatype “OLE Object”. A field with this datatype can be used to store pictures. The inclusion of a picture is then straightforward. Rightclick on the field brings you in a menu that allows you to include a picture:

That was really easy.
Let us move to Oracle.
In Oracle, it is really easy sailing. Let us first create a table.

CREATE TABLE "SCOTT"."BLOBJE" 
   (	"NUMMER" NUMBER, 
	"PLAATJE" BFILE
   )

Then, we create a storage where to put the images in.

CREATE OR REPLACE DIRECTORY 
PLAATJE_DIR AS 
'/home/tom/oracle';

This is then followed by storing a nice picture in the storagearea (=/home/tom/oracle). Let us say, we stored a file “thumbsup.gif” there.
The final step is to store a record in the table that was created first:

INSERT INTO blobje VALUES(4,bfilename('PLAATJE_DIR','thumbsup.gif'));

We can then see the picture from within an Oracle table:

Then with Teradata, it was not so simple. Like in Oracle, I created a table first:

CREATE SET TABLE financial.blobje
     (
      plaatje BLOB(2097088000),
      nummer INTEGER NOT NULL)
UNIQUE PRIMARY INDEX ( nummer );

I then changed the mode of Teradata SQL Assistant by listening to import. This was accomplished by File>Import Data. I then started a query, saying:

insert into  financial.blobje(nummer,plaatje) values (3,?B);

.
This started a dialogue where a reference was made to a control file that contained the name of the picture. The controlfile was stored in the same directory as the picture and it only contained the name of the picture.
This is not trivial. Nor could it be found in Teradata manual (at least I couldn’t). The information was retrieved from “http://developer.teradata.com/applications/articles/large-objects-part-1-loading”.

In this website, I found another solution that uses the BTEQWIN client. Let us first create a table:

create table financial.blobje(
    id varchar (80),
    binary_lob binary large object
)
unique primary index (id);

Then store several pictures in a directory (say in D:\tmaanen\Desktop\). Say we store “thumpsup.gif” and “thumbsdown.gif” in tht directory.
Also create an import.ctl file that contains:

D:\tmaanen\Desktop\thumbsup.gif,xxxx
D:\tmaanen\Desktop\thumbsdown.gif,yyyy

In BTEQWIN, we create a loop that reads the lines in this import.ctl file. This can be achieved by:

.import vartext ‘,’ LOBCOLS=1 file=’D:\tmaanen\Desktop\import.ctl’;

followed by:

.repeat *
using (lobFile blob as deferred,id varchar(40))
insert into financial.blobje (id, binary_lob)
values (:id, :lobFile);

It is not really easy, but it can be admittingly done.

Uoef: