Hi

I came across the blob and the clob data type ? whats the difference here in usage ?

I have a products table that requires extensive use of various types of notes. e.g. multiple different notes and needs to store multiple images per item.

I Just need some best practice for storing

1. notes

2. storing  images in clob or blob ... not sure ?

( or I could just keep images in a folder and load when required to display.)

q. what is most efficient and the better way to do it in your experience ?

kind regards

Kamran

kamran wrote:

> I Just need some best practice for storing
>
> 1. notes
>
> 2. storing  images in clob or blob ... not sure ?

I'd say that CLOB is generally text, BLOB is generally binary (non-text). I expect you can do both with either, but it makes sense to stick with that principle.



> ( or I could just keep images in a folder and load when required to display.)

That depends on various things, including size, longevity, hosting, how critical etc. Both work fine. For example, a reference to the file name allows you to have the files stored in a hierarchy controlled by another system. "File 000001.txt" will be in folder "000". But what if the files all get deleted, or the system is copied to a new server and not the files? Keep them in the database. But they are each 4GB files! Keep them in the file system. Even access to them may vary - if all you have is a database connection, then you can't just go access the \\Server\share\folder\file00001.png.



--

Matthew Jones

kamran

>I came across the blob and the clob data type ? whats the difference here in usage ?

LOB = large object
C = character
B = binary

CLOBs are for stuffing loads of text formatted or otherwise into (ie definitely anything over 1024 characters big and probably lots smaller stuff as well)

BLOBs are for binaries eg graphics, in-memory tables, .docx etc

Back in DBISAM I used to use memo fields for both now I try and be good.

>( or I could just keep images in a folder and load when required to display.)

I have done that but keeping them in sync, stopping people deleting them makes it awkward.

>q. what is most efficient and the better way to do it in your experience ?

A lot depends on size, volumes and usage. I've used a number of different approaches:

1 column for each note and one column for each image - OKish if you know in advance what's coming

1 structured CLOB column and 1 BLOB column in which I store an imagelist (essentially a TStringlist with the images stored in .Objects)

1 CLOB column with graphics and text store as a mime document

1 BLOB column storing an im-memory table structured to hold text and images

One thing I haven't done but is the "virtuous" way of doing it is two extra tables each with a primary key composed of a foreign key back to the main table and one or more reference columns plus a column for a CLOB for one table and a BLOB for the other. According to many its the right way but I loath it.

Roy Lambert

Matthew and Roy

* Thank you * for clarifying that

kamran wrote:

Hi

I came across the blob and the clob data type ? whats the difference here in usage ?

Kamran,

<< I came across the blob and the clob data type ? whats the difference here in usage ? >>

Both CLOBs and BLOBs are, technically, BLOBs.  However, CLOBs are given special treatment in EDB so that they behave like a VARCHAR without a length limit.  This means that they have a collation, can be searched like a VARCHAR, and SQL functions like LENGTH() return the number of *characters*, not their size in bytes (important for Unicode).

However, CLOBs and BLOBs cannot be directly indexed, and you must use either text indexes or generated columns to extract the portion of the CLOB or BLOB that you want to be indexed.

Tim Young
Elevate Software
www.elevatesoft.com