By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

Sample table structure to store pics in DB2

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

Thanks AJ and Knut Stolze..for the DDL
rAinDeEr

May 10 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.