Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread Saving Data in a blob field
Thu, Jan 5 2006 5:28 AMPermanent Link

Paul Baumann
Hello,

i wanna save structured data in a BLOB (binary?) field. Like this

size: 45
position: chief
begin: 01.01.2006
note: yxyxxyxy yxyxyyx yxxyyxxyxxy yxyxxyxy yxyxyxx yxx....

or in other words

field: value

there can be strings, date, time, integer, float, multi line text (memo)

the informations (fields) to be stored will vary from record to record. I will give you an
idea.

record1:
size: 89
begin: 05.05.2006

record2:
note: cvcbcvcbcvc cbcvc cbvc cbcvc cbcvc cbcvc cb...

record3:
position: broker
note: kjh jkh kj hkj h kjh kjh kj h kj h kjh k jh kj h...


the values of the blob field should be displayed and edited in standard VCL like
Tstinggrid. and it should be possible to search for, something like that: "size >100", you
know.


Questions:
What is best approach for this? codesnippet?
What about performance issues?

thanx for replies

PB
Thu, Jan 5 2006 6:19 AMPermanent Link

"Ralf Mimoun"
Paul Baumann wrote:
> Hello,
>
> i wanna save structured data in a BLOB (binary?) field. Like this
>
> size: 45
> position: chief
> begin: 01.01.2006
> note: yxyxxyxy yxyxyyx yxxyyxxyxxy yxyxxyxy yxyxyxx yxx....
>
> or in other words
>
> field: value
>
> there can be strings, date, time, integer, float, multi line text
> (memo)
>
> the informations (fields) to be stored will vary from record to
> record. I will give you an idea.

Use In-Memory-Inifiles, streams and TBLOBField.LoadFromStream/SaveToStream.
No rocket science involved.

....
> the values of the blob field should be displayed and edited in
> standard VCL like Tstinggrid.

You have to extract and refresh the data on your own.

> and it should be possible to search
> for, something like that: "size >100", you know.

Again, you have to write some code. Ugly code, because there is no index, so
you have to use a while loop. And you have to write a parser. Afaik there is
nothing on the market to do what you want.

> What is best approach for this?

Write code.

> What about performance issues?

It will be slow.

Ralf

Thu, Jan 5 2006 8:43 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Paul


If it is truly structured in a consistent fashion as your example seems to show then unless disk space is extremely tight, or there's some other overriding reason, I'd just use a proper table. Performance will be far better as will searchability.

I do use blobs for storing "sort of" structured data and it can be made partially searchable by using full text indexing.

Roy Lambert
Thu, Jan 5 2006 5:02 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Paul,

<< i wanna save structured data in a BLOB (binary?) field. Like this  >>

Like Roy recommended, I would also recommend using a proper table and taking
advantange of the searching, etc. in DBISAM.  It will certainly make things
much easier.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Jan 6 2006 5:19 AMPermanent Link

Paul Baumann
All,

perhabs i should discribe situation from an other point of view.

New project is a kind of CRM application, standandard, btb. From experiences from older
projects i know, there are fields everybody needs, like name, city usw. OTOH every user
has "his own" informations he wants to store in the database.

now the idea: my application comes only with the fields everybody needs. Individual fields
are defined by every of my customers, implemented by their administrator.

This will lead to an situation, you all know! In doubt you make more fields than realy
needed, only to be prepared. Lets say admin defines 25 customer fields. From that amount
only 10% are needed for every record, the rest is needed more random.

My experience is that mostly every customer has needs for his own fields. If you want to
antizipate this in development process and normal tables and structures, you end with
tabels that have a lot of fields. So in my Address-Program (mini CRM), i have over 80
fields in main table. 20 of them are so called freefields like "freefield1" "freefield2"
and so on. this is not good approach for databases. And you all know the question then:
What is the right number 15, 18, 21, 25?

Ok, one could say "Make a memo field, thats it". But for input works, it should be more
structured, and for selections you must be able to say "All companies that have more than
1000 employees".

Now you know the basing point of my request here.

regards
PB


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Paul,

<< i wanna save structured data in a BLOB (binary?) field. Like this  >>

Like Roy recommended, I would also recommend using a proper table and taking
advantange of the searching, etc. in DBISAM.  It will certainly make things
much easier.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Jan 6 2006 5:53 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Paul


OK, if the frequency of change of the table structure isn't too great you could let them simply change the main table, or a linked table which just has these extra fields in it.

The only real problems with using a blob (or I suggest memo) field are searching and processing. Searching can be improved if you can use full text indexing, but processing will always be a pain.

In one of my apps I store what is a stringlist using values eg Size=100. Before storing I convert all #32 to #160 so that for display/print it looks the same but full text indexing treats it all as one word.

Searching that couldn't be handled that way would need to be done through a custom function (which will allow SQL to be used) but it will be slow since it will have to extract the information for every record and check it. eg size > 100 would require something like (and I've not used custom functions with memo fields so its a guess)

var
sl:TStringlist;
begin
sl:= TStringList.Create;
sl.Text :=  FunctionParams[0].AsString;
if sl.Values['Size'] <> '' then begin
Result := StrToInt(sl.Values['Size']) > FunctionParams[1].AsInteger;
end else Result := False;
sl.Free;
end;

Roy Lambert
Sat, Jan 7 2006 12:22 AMPermanent Link

"Ralf Bertoldi"
Paul,

> New project is a kind of CRM application, standandard, btb. From
> experiences from older projects i know, there are fields everybody
> needs, like name, city usw. OTOH every user has "his own"
> informations he wants to store in the database.

if you would like to store a lot of things like simple values or
graphics or other things, but don't want to hassle with parsing the
blobs/memos, maybe xml would be something what could work as you
expect..

just store the structures and contents to xml an "stream" it up to the
blob.. while reading the blob/xml restore the values/controls/graphics..

there are some really fast xml parsers out there that do most of the
basic work managing the contents...

> Ok, one could say "Make a memo field, thats it". But for input works,
> it should be more structured, and for selections you must be able to
> say "All companies that have more than 1000 employees".


if just a couple of records really would have a "virtual field" -
"companies"...? you have to manage your own "indexes".. mmhh, one user
creates a field "companies" the next user calls it "company".. and the
next "Firma"...  really hard to manage...


HIH
ralf
Sat, Jan 7 2006 2:29 PMPermanent Link

"Trev"
If you are committed to storing structured data in a blob you could use a
kbmMemTable and save/restore it to your blobstream. But, as others have
said, I am quite sure that pretty soon someone will want to search for
something and you will be stuck. I'd look at your design again.

HTH

T

"Paul Baumann" <tgrim01@web.de> wrote in message
news:2CE42DDA-95C2-41B0-B9C7-83F8CDA90C69@news.elevatesoft.com...
> All,
>
> perhabs i should discribe situation from an other point of view.
>
> New project is a kind of CRM application, standandard, btb. From
> experiences from older
> projects i know, there are fields everybody needs, like name, city usw.
> OTOH every user
> has "his own" informations he wants to store in the database.
>
> now the idea: my application comes only with the fields everybody needs.
> Individual fields
> are defined by every of my customers, implemented by their administrator.
>
> This will lead to an situation, you all know! In doubt you make more
> fields than realy
> needed, only to be prepared. Lets say admin defines 25 customer fields.
> From that amount
> only 10% are needed for every record, the rest is needed more random.
>
> My experience is that mostly every customer has needs for his own fields.
> If you want to
> antizipate this in development process and normal tables and structures,
> you end with
> tabels that have a lot of fields. So in my Address-Program (mini CRM), i
> have over 80
> fields in main table. 20 of them are so called freefields like
> "freefield1" "freefield2"
> and so on. this is not good approach for databases. And you all know the
> question then:
> What is the right number 15, 18, 21, 25?
>
> Ok, one could say "Make a memo field, thats it". But for input works, it
> should be more
> structured, and for selections you must be able to say "All companies that
> have more than
> 1000 employees".
>
> Now you know the basing point of my request here.
>
> regards
> PB
>
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
>
> Paul,
>
> << i wanna save structured data in a BLOB (binary?) field. Like this  >>
>
> Like Roy recommended, I would also recommend using a proper table and
> taking
> advantange of the searching, etc. in DBISAM.  It will certainly make
> things
> much easier.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Image