Sample table structure to store pics in DB2

rAinDeEr

Hi,

Can any one show me a sample create table structure for storing .BMP's
and .GIF images. togther with its description.

Thanks in advance,
rAinDeEr

May 5 '06 #1

aj

Perhaps something like this?

CREATE TABLE IMAGE (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 ,
INCREMENT BY +1 , CACHE 20 ) ,
SUBMIT_DATE DATE NOT NULL WITH DEFAULT CURRENT DATE ,
SUBMIT_TIME TIME NOT NULL WITH DEFAULT CURRENT TIME ,
EXPIRE_DATE DATE ,
SUBMITTER VARCHAR(50) NOT NULL WITH DEFAULT USER ,
DESCRIPTION VARCHAR(250) ,
FILENAME VARCHAR(50) NOT NULL ,
MIME_TYPE_ID INTEGER NOT NULL ,
IMAGE BLOB(10485760) LOGGED COMPACT NOT NULL
)

HTH

aj

rAinDeEr wrote:

Hi,

Can any one show me a sample create table structure for storing .BMP's
and .GIF images. togther with its description.

Thanks in advance,
rAinDeEr

May 5 '06 #2

Knut Stolze

rAinDeEr wrote:

Hi,

Can any one show me a sample create table structure for storing .BMP's
and .GIF images. togther with its description.

CREATE TABLE pics (
id INTEGER NOT NULL PRIMARY KEY, -- not mandatory but usually needed
picture BLOB(10M),
description VARCHAR(1000)
)@

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

May 8 '06 #3

Knut Stolze

aj wrote:

Perhaps something like this?

CREATE TABLE IMAGE (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 ,
INCREMENT BY +1 , CACHE 20 ) ,
SUBMIT_DATE DATE NOT NULL WITH DEFAULT CURRENT DATE ,
SUBMIT_TIME TIME NOT NULL WITH DEFAULT CURRENT TIME ,
EXPIRE_DATE DATE ,
SUBMITTER VARCHAR(50) NOT NULL WITH DEFAULT USER ,
DESCRIPTION VARCHAR(250) ,
FILENAME VARCHAR(50) NOT NULL ,
I'm curious: what would the filename be for? You store the image as BLOB,
so the filename is a bit pointless, I think. (And 50 bytes is very short
for a filename).
MIME_TYPE_ID INTEGER NOT NULL ,
IMAGE BLOB(10485760) LOGGED COMPACT NOT NULL
)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

May 8 '06 #4

aj

I have a web-based front end for end-users to insert files into the
database - they use a
enctype="multipart/form-data" and <input type="file">
mechanism, so the filename has to be specified anyways. Rather than
tossing it, I just keep it.

Plus it makes searching easier - they don't always enter a good desc,
or one at all.

Plus, the web-based front end allows them to view the documents also.
I use the extension on the filename to store a mime type, which is then
used to clue the browser in for display and show an appropriate icon
next to the document link.

Paperless has always been a goal for us, and to a great extent, we have
done it...

cheers

aj

Knut Stolze wrote:

aj wrote:

Perhaps something like this?

CREATE TABLE IMAGE (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 ,
INCREMENT BY +1 , CACHE 20 ) ,
SUBMIT_DATE DATE NOT NULL WITH DEFAULT CURRENT DATE ,
SUBMIT_TIME TIME NOT NULL WITH DEFAULT CURRENT TIME ,
EXPIRE_DATE DATE ,
SUBMITTER VARCHAR(50) NOT NULL WITH DEFAULT USER ,
DESCRIPTION VARCHAR(250) ,
FILENAME VARCHAR(50) NOT NULL ,

I'm curious: what would the filename be for? You store the image as BLOB,
so the filename is a bit pointless, I think. (And 50 bytes is very short
for a filename).

MIME_TYPE_ID INTEGER NOT NULL ,
IMAGE BLOB(10485760) LOGGED COMPACT NOT NULL
)

May 8 '06 #5

rAinDeEr

Thanks AJ and Knut Stolze..for the DDL
rAinDeEr

May 10 '06 #6