Lazarus

Programming => Databases => Topic started by: Vodnik on February 11, 2020, 10:48:46 am

Title: Which data structure to use?
Post by: Vodnik on February 11, 2020, 10:48:46 am
My application read data to DBGrid from the database with phone calls. I want to add information about the cities to the output of my application. The data with city names is not contained in original DB and it is not allowed to add new tables to it. So I plan to store data about cities somewhere else (495, Moscow; 812, St. Petersburg and so on) and to insert it to DBGrid with OnGetText.
Question is: what are data structures available in Lazarus, that are suitable for this case?
Arrays? Files? Additional database?
Title: Re: Which data structure to use?
Post by: AlexTP on February 11, 2020, 10:59:30 am
It may be TCSVDataset, or maybe TStringList, which have Objects items pointing to TStringLists.
Title: Re: Which data structure to use?
Post by: garlar27 on February 11, 2020, 03:08:23 pm
Create a new DB to hold this kind of data (let's call it system level tables) and attach it to the main db. Thus you can join the city table with the phone calls or whatsoever.
Title: Re: Which data structure to use?
Post by: sash on February 11, 2020, 06:33:00 pm
When using db-aware controls, the first thing I'd consider is to use Dataset (Lookup).
Easiest one: TBuffDataset stored in XML.

Another option (if you need a display in Grid only) - DBGrid's Picklists.
Title: Re: Which data structure to use?
Post by: Vodnik on February 11, 2020, 09:54:07 pm
Quote from: garlar27 on February 11, 2020, 03:08:23 pm
Create a new DB to hold this kind of data (let's call it system level tables) and attach it to the main db. Thus you can join the city table with the phone calls or whatsoever.

This sounds intriguing! How is it possible to attach one DB to another?
Can you please provide a bit more detailes or links?
Title: Re: Which data structure to use?
Post by: Vodnik on February 11, 2020, 10:09:59 pm
Quote from: sash on February 11, 2020, 06:33:00 pm
When using db-aware controls, the first thing I'd consider is to use Dataset (Lookup).
Easiest one: TBuffDataset stored in XML.

Another option (if you need a display in Grid only) - DBGrid's Picklists.
I can't find any description neither for TBuffDataset nor for DBGrid's Picklists, only questions and discussions.
Not documented, but well-known features...
Can you please provide some ideas where to find any info or examples?
Title: Re: Which data structure to use?
Post by: sash on February 12, 2020, 09:38:05 am
TBufDataset (yes, both well-known and not well documented, and also a parent for TSQLQuery) is generally following Delphi's TClientDataset paradigm:
Code: Pascal  [Select][+][-]
  1. uses db, BufDataset, XMLDatapacketReader;
  2. var
  3.   bds, bds2 : TBufDataset;
  4. begin
  5.   bds := TBufDataset.Create(YourForm);
  6.   with bds  do begin
  7.     // define fields
  8.     FieldDefs.Add('id', ftInteger);
  9.     FieldDefs.Add('name', ftString, 30);
  10.     CreateDataset;
  11.  
  12.     // add data
  13.     AppendRecord([1, 'val1']);
  14.     AppendRecord([2, 'val2']);
  15.     SaveToFile('dataset.xml', dfXML); // save
  16.     // save
  17.     MergeChangeLog; // Mostly we don't need persistent changelog
  18.     SaveToFile('dataset.xml', dfXML);
  19.   end;
  20.  
  21.   bds2 := TBufDataset.Create(YourForm);
  22.   bds2.LoadFromFile('dataset.xml', dfXML);                        
  23. end;
  24.  
Title: Re: Which data structure to use?
Post by: garlar27 on February 12, 2020, 05:44:58 pm
Quote from: Vodnik on February 11, 2020, 09:54:07 pm
Quote from: garlar27 on February 11, 2020, 03:08:23 pm
Create a new DB to hold this kind of data (let's call it system level tables) and attach it to the main db. Thus you can join the city table with the phone calls or whatsoever.

This sounds intriguing! How is it possible to attach one DB to another?
Can you please provide a bit more detailes or links?

If you are using relational DB then you have the ATTACH statement and it is something like this:
Code: SQL  [Select][+][-]
  1. ATTACH DATABASE 'C:\MyPrj\data\sysdata.db' AS SYS_DATA;
Title: Re: Which data structure to use?
Post by: Vodnik on February 13, 2020, 09:44:15 pm
Thanks, garlar. I didn't find description of similar construction for Informix. And I guess this will break the rule: "Do not modify DB structure".
Title: Re: Which data structure to use?
Post by: garlar27 on February 17, 2020, 05:36:30 pm
Quote from: Vodnik on February 13, 2020, 09:44:15 pm
Thanks, garlar. I didn't find description of similar construction for Informix. And I guess this will break the rule: "Do not modify DB structure".

Maybe you could use CREATE TEMP TABLE (https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0571.htm) or CREATE EXTERNAL TABLE statement (https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_2053.htm)
As I understand they DO NOT MODIFY the working DB.
Title: Re: Which data structure to use?
Post by: Vodnik on February 19, 2020, 08:41:05 pm
Thanks, garlar, I have succeeded with CREATE TEMP TABLE option.
Now I'm looking for convenient way to store the data for TEMP TABLE creation.
E.g. I load CSV file, then CREATE TEMP TABLE from it, afterwards give the job to DB's SQL engine.
Title: Re: Which data structure to use?
Post by: egsuh on February 25, 2020, 06:05:25 am
I think you may use CSVDataSet itself, without creating TEMP TABLE, by linking two datasets from  Informix and CSVDataSet.   
TinyPortal © 2005-2018