Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
To clob or blob ? |
Fri, Nov 18 2016 6:52 AM | Permanent Link |
kamran | 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 |
Fri, Nov 18 2016 8:06 AM | Permanent Link |
Matthew Jones | 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 |
Fri, Nov 18 2016 8:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Nov 18 2016 3:42 PM | Permanent Link |
kamran | 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 ? |
Mon, Nov 21 2016 11:23 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |