Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General Discussion » View Thread |
Messages 1 to 10 of 15 total |
StringList Name/Value Pairs in a Table |
Thu, May 24 2007 9:18 AM | Permanent Link |
"Johnnie Norsworthy" | If I store a stringlist (BLOB) in a table of the form:
Name1=Value Name2=Value (about 400 more, "virtual fields") Is there an easy way to work with the individual name/value pairs, possibly going as far a using DB-aware controls? Basically, I need to implement my own routine whenever a virtual field is read or written, probably including some field-type properties. I have a considerable amount of sparse data and this would be an excellent way of storing it efficiently. I would create real fields from some of the name/value pairs for indexing and searching. -Johnnie |
Thu, May 24 2007 5:25 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Johnnie,
<< If I store a stringlist (BLOB) in a table of the form: Name1=Value Name2=Value (about 400 more, "virtual fields") Is there an easy way to work with the individual name/value pairs, possibly going as far a using DB-aware controls? >> The quickest way to deal with this is via the TDBMemo control and accessing the Public Lines property as needed. Since it is a TStrings object, you can use the name-value pair functionality in it to do the dirty work. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, May 25 2007 8:09 AM | Permanent Link |
"Charles Bainbridge" | We implemented such a scheme some years ago, including crude data-aware
controls. We finally concluded that the lack of flexibility by not being able to access the data via ODBC, report writers, and other third party tools outweighed the advantages. We've abandonned it and everything is back in native table data columns. Disk space is relatively cheap... or consider an architecture change - a detail table perhaps, where 'name' is a key value? -- Charles Bainbridge "Johnnie Norsworthy" <jln206@verizon.net> wrote in message news:128FF779-5594-47AA-8FCB-E84806B1A44B@news.elevatesoft.com... > If I store a stringlist (BLOB) in a table of the form: > Name1=Value > Name2=Value > (about 400 more, "virtual fields") > > Is there an easy way to work with the individual name/value pairs, > possibly going as far a using DB-aware controls? Basically, I need to > implement my own routine whenever a virtual field is read or written, > probably including some field-type properties. > > I have a considerable amount of sparse data and this would be an excellent > way of storing it efficiently. I would create real fields from some of the > name/value pairs for indexing and searching. > > -Johnnie > > |
Fri, May 25 2007 9:30 AM | Permanent Link |
"Johnnie Norsworthy" | "Charles Bainbridge" <charles.bainbridge@ntlworld.com> wrote in message
news:2859E1FC-5E15-49B3-896A-4959A9932E92@news.elevatesoft.com... > We implemented such a scheme some years ago, including crude data-aware > controls. We finally concluded that the lack of flexibility by not being > able to access the data via ODBC, report writers, and other third party > tools outweighed the advantages. We've abandonned it and everything is > back in native table data columns. Disk space is relatively cheap... or > consider an architecture change - a detail table perhaps, where 'name' is > a key value? > -- > Charles Bainbridge I know disc space is cheap, but being able to accomodate a change in structure without database updates is a great feature I used to have in my DOS application that I miss sorely. Plus, I think it would lend itself to replication to portable computers much better. I'm talking about a file so sparsely populated that 143 meg compressed to 10. What I might do is dynamically create calculated fields for the tables for the name/value pairs for ReportBuilder (and other data-aware components) to use. Still thinking about this. I really miss the ability to "add" a field without a restructuring process at my customer sites. |
Fri, May 25 2007 10:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Johnnie
Why not just use a memory table. Keep a master template for all the fields as the fields bit of an sql create so you can create the necessary table. Store the value pairs (only where a value exists) in a memo field in the real table. Populating the memo table is easy 1. delete the existing record 2. insert a new record 3. grab the value pairs into a stringlist 4. for..next through the stringlist memorytable.fieldbyname(bit before the =).asstring := bit after the = 5. Post the record You can also use TEXTSEARCH in filters/sql (I replace all #32 with #160 to make each value pair a single "word") Roy Lambert |
Fri, May 25 2007 12:18 PM | Permanent Link |
"Johnnie Norsworthy" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:810ED953-42A7-4AB7-8FC7-00733EA571FA@news.elevatesoft.com... > Johnnie > > > Why not just use a memory table. Keep a master template for all the fields > as the fields bit of an sql create so you can create the necessary table. > Store the value pairs (only where a value exists) in a memo field in the > real table. Populating the memo table is easy > > 1. delete the existing record > 2. insert a new record > 3. grab the value pairs into a stringlist > 4. for..next through the stringlist memorytable.fieldbyname(bit before the > =).asstring := bit after the = > 5. Post the record > > You can also use TEXTSEARCH in filters/sql (I replace all #32 with #160 to > make each value pair a single "word") Roy, This might just work. I'll have to noodle on the details for my application. Good idea - THANKS! -Johnnie |
Fri, May 25 2007 1:41 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Johnnie
Its very similar to what I do in a couple of apps, but I treat each value pair as a record not a field. Roy Lambert |
Fri, May 25 2007 1:46 PM | Permanent Link |
Bill Mullen | Johnny,
Instead of using hundreads of name/value pairs in a blob field why not design your tables so that you have a name/vair pair table such as the following image shows. http://www.flickr.com/photos/8561575@N08/513678045/ This design allows you to add new fields as you need. It also allows you to add only those fields you need versus having to add every field for every master record. You can probably tweak the design to better suit your needs. "MasterTable" is self explanitory - this is the master table where you keep the main portion of your data. "DataTypes" allows you to define what the data fields and types are, for example, string, integer, float, blob, image, memo, etc. You can even use this to store structured data, for example say you get an XML document and want to store the entire document as is, the datatype could be XML. "DataTypes" also holds the NAME part of the Name/Value so that you don't accidentally create two data elements (of different types) with the same name. This does not prevent you from inserting multiple rows of the same data name for the master record if needed (more on this in a moment). "NameValues" stores the actual value of the data and the foreign keys. Generally all values will be stored in the FieldValue column. If you have string data that won't fit, you might store the first 50 characters for indexing purposes and the entire string would then go into FieldData and the DataType should indicate that the data uses the blob (DataTypes.UsesBlob = true). As you can see, this allows you to pratically store any kind of data you want and can be expanded without redesigning the database at all. Eventually, when it is time to update the schema of the database, it should be fairly easy to move data out of this structure into the MasterTable (or a child table of MasterTable). You can perform searches for data types, data names, all data by mastertable.id, etc. I believe this is a much better design than a stringlist with name/value pairs, especially if you might have upwards to 400 name/value pairs per mastertable row. Now about duplicates, you could place a table contraint on NameValues that does not allow duplicate MasterTableID and DataID combinations. I threw this design together fairly quickly so that are certain to be areas that can be fine tuned if needed. One thing you may want to look at for example might be to store UsesBlob in NameValues versus DataTypes. Then you would set it only if the data was too large to fit in FieldValue, versus it being set just becuase of the DataTypes definition. Hope this helps. >If I store a stringlist (BLOB) in a table of the form: >Name1=Value >Name2=Value >(about 400 more, "virtual fields") > >Is there an easy way to work with the individual name/value pairs, possibly >going as far a using DB-aware controls? Basically, I need to implement my >own routine whenever a virtual field is read or written, probably including >some field-type properties. > >I have a considerable amount of sparse data and this would be an excellent >way of storing it efficiently. I would create real fields from some of the >name/value pairs for indexing and searching. > >-Johnnie > |
Fri, May 25 2007 2:05 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Bill
I thought I'd have a look but after creating a flickr account Oops! You don't have permission to view this photo. Roy Lambert |
Fri, May 25 2007 2:26 PM | Permanent Link |
Bill Mullen | Well crap! Wait one...
>Bill > > >I thought I'd have a look but after creating a flickr account > > >Oops! You don't have permission to view this photo. > > >Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, March 28, 2024 at 08:36 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |