Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Working with nested datasets

Posted on 2004-09-20
3
Medium Priority
?
1,947 Views
Last Modified: 2011-09-26
Hello,

I want to understand Delphi's Database mechanism and the many features it offers. I tried to use Nested Datasets to show the contents of two tables in one TDBGrid. For a test application I use the Demo-Database DBDEMOS (BDE). I can access and show the tables "Customer" and "Orders" with a TTable and a TDatasource for every table: tbCustomer, dsCustomer, tbOrders, dsOrders. Now I want to access the Orders of every Customer via the Customer's DBGrid: TDBGrid is able to show a nested dataset in a separate form (TDBGrid.ShowPopupEditor). I create two TClientDataset's: cdsCustomer, cdsOrders. I link these to the TTable's via TDatasetProviders dspCustomer, dspOrders. I create persistent fields for cdsCustomer with the field editor: I import every data field and add a new field "Orders" and set its Type in "Field properties" to "Dataset". This changes the FieldKind to fkCalculated, so I assume this is correct. Now I set cdsOrders.DatasetField to the new field component cdsCustomerOrders.

When I try to activate any of the ClientDataset's, I get an error message "cdsCustomer: Field 'Orders' cannot be a calculated or lookup field." I tried every other FieldKind - it won't work. I tried to create an Index on Orders.CustNo and make it the active Index for cdsOrders - same result.

Here is everything I did with the components (at design time) step by step:

tbCustomer.DatabaseName := 'DBDEMOS';
tbCustomer.TableName := 'customer.db';
dsCustomer.Dataset := tbCustomer;
tbCustomer.Active := true;

tbOrders.DatabaseName := 'DBDEMOS';
tbOrders.TableName := 'orders.db';
dsOrders.Dataset := tbOrders;
tbOrders.Active := true;

dspCustomer.Dataset := tbCustomer;
cdsCustomer.ProviderName := dspCustomer;

dspOrders.Dataset := tbOrders;
cdsOrders.ProviderName := dspOrders;

<Set persistent fields for cdsCustomer, add new Dataset-field "Orders">
cdsOrders.DatasetField := cdsCustomerOrders
cdsOrders.ProviderName := dspOrders; // have to reset it because SetDatasetField changed it to ''

cdsCustomer.Active := true; // error
cdsOrders.Active := true; // error

Maybe I understood something wrong. Why does TClientDataset.SetDatasetField delete the ProviderName? From where does the nested Dataset get it's data if not from the Provider? Is what I want possible at all? But if not, what is the idea behind nested datasets?

Thank you!

0
Comment
Question by:KDamm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12102388
Nested datasets are often exactly that: datasets in fields of another dataset. Some databases are capable of doing this. Nested datasets can also occur with specific queries where you ask something like:
Select * from Customers, Orders where Customer.CustomerID = Orders.CustomerID
In this case, you'd get a huge list of all data, but if you'd retrieve it as a nested dataset, you'd only get one record for each customer. But each customer record would have a nested dataset with orders.

The idea behind nested datasets are just levels of data, jumping from one level to another. You could, for example, include a third level for articles. Every order would have a list of articles, thus in the Orders nested dataset you'd get another nested dataset for articles.

However, you could also want things to be nested the other way around. E.g. start with the articles, with customers who ordered these articles in a nested dataset. Basically, they're used to view your data from a certain perspective.

But in general you could use normal master-detail datasets for this. But the Master-Detail model requires a new query every time a user selects a new master record to get the new detail records. With nested datasets, you don't have these roundtrips back to the server. The database just kicks over all the data you want and then you use it client-side. Means that you will be sending over a large amount of data but once the client has the data, it can walk through it pretty fast without the need to connect to the server again to beg for more data.
The old Master-Detail technique would just send over small amounts of data but while the user walks through the list, the client keeps asking new detail info from the server, keeping the server quite busy.

So basically, the use of nested datasets is a strategical decision. If you want to remove a lot of workload away from the server, nested datasets are more practical. If users need the latest data all the time, the old master-detail methods are preferred.
0
 
LVL 6

Accepted Solution

by:
vadim_ti earned 900 total points
ID: 12103004
try it:

unit source

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBClient, Provider, DBTables;

type
  TForm1 = class(TForm)
    Database1: TDatabase;
    tbcustomer: TTable;
    dsCustomer: TDataSource;
    tborders: TTable;
    dspCustomer: TDataSetProvider;
    cdsCustomer: TClientDataSet;
    cdsOrder: TClientDataSet;
    cdsCustomerCustNo: TFloatField;
    cdsCustomerCompany: TStringField;
    cdsCustomerAddr1: TStringField;
    cdsCustomerAddr2: TStringField;
    cdsCustomertborders: TDataSetField;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

end.

form source:
object Form1: TForm1
  Left = 192
  Top = 133
  Width = 544
  Height = 375
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Database1: TDatabase
    AliasName = 'DBDEMOS'
    DatabaseName = 'db'
    SessionName = 'Default'
    Left = 8
    Top = 8
  end
  object tbcustomer: TTable
    DatabaseName = 'db'
    TableName = 'customer.db'
    Left = 56
    Top = 8
  end
  object dsCustomer: TDataSource
    DataSet = tbcustomer
    Left = 56
    Top = 40
  end
  object tborders: TTable
    DatabaseName = 'db'
    MasterSource = dsCustomer
    TableName = 'orders.db'
    Left = 104
    Top = 8
  end
  object dspCustomer: TDataSetProvider
    DataSet = tbcustomer
    Left = 56
    Top = 80
  end
  object cdsCustomer: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    ProviderName = 'dspCustomer'
    Left = 56
    Top = 120
    object cdsCustomerCustNo: TFloatField
      FieldName = 'CustNo'
      ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
    end
    object cdsCustomerCompany: TStringField
      FieldName = 'Company'
      Size = 30
    end
    object cdsCustomerAddr1: TStringField
      FieldName = 'Addr1'
      Size = 30
    end
    object cdsCustomerAddr2: TStringField
      FieldName = 'Addr2'
      Size = 30
    end
    object cdsCustomertborders: TDataSetField
      FieldName = 'tborders'
    end
  end
  object cdsOrder: TClientDataSet
    Aggregates = <>
    Params = <>
    Left = 104
    Top = 120
  end
end
0
 

Author Comment

by:KDamm
ID: 12103638
Hello Workshop_Alex,

thanks for your comment, I think nested datasets are better then master-detail-relationships for what I have in mind.

Thank you vadim_ti for your answer - I just needed to set tborders.MasterField to 'CustNo' to get the table to show just the orders for the selected customer.

For everyone who will have the same problem in future (the solution is different from Borland's documentation: Nowhere is suggested to set the field's name to the dataset name) - here the complete source of the working test form:

{ Source }

unit ufmMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBClient, Provider, DBTables, Grids, DBGrids;

type
  TForm1 = class(TForm)
    Database1: TDatabase;
    tbcustomer: TTable;
    dsCustomer: TDataSource;
    tborders: TTable;
    dspCustomer: TDataSetProvider;
    cdsCustomer: TClientDataSet;
    cdsOrder: TClientDataSet;
    cdsCustomerCustNo: TFloatField;
    cdsCustomerCompany: TStringField;
    cdsCustomerAddr1: TStringField;
    cdsCustomerAddr2: TStringField;
    cdsCustomertborders: TDataSetField;
    DBGrid1: TDBGrid;
    dsCustomerNestedOrders: TDataSource;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

end.

{ Form }

object Form1: TForm1
  Left = 192
  Top = 133
  Width = 544
  Height = 375
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 0
    Top = 0
    Width = 536
    Height = 348
    Align = alClient
    DataSource = dsCustomerNestedOrders
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object Database1: TDatabase
    AliasName = 'DBDEMOS'
    Connected = True
    DatabaseName = 'db'
    SessionName = 'Default'
    Left = 8
    Top = 8
  end
  object tbcustomer: TTable
    DatabaseName = 'db'
    TableName = 'customer.db'
    Left = 56
    Top = 8
  end
  object dsCustomer: TDataSource
    DataSet = tbcustomer
    Left = 56
    Top = 40
  end
  object tborders: TTable
    DatabaseName = 'db'
    IndexName = 'CustNo'
    MasterFields = 'CustNo'
    MasterSource = dsCustomer
    TableName = 'orders.db'
    Left = 104
    Top = 8
  end
  object dspCustomer: TDataSetProvider
    DataSet = tbcustomer
    Left = 56
    Top = 80
  end
  object cdsCustomer: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    ProviderName = 'dspCustomer'
    Left = 56
    Top = 120
    object cdsCustomerCustNo: TFloatField
      FieldName = 'CustNo'
      ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
    end
    object cdsCustomerCompany: TStringField
      FieldName = 'Company'
      Size = 30
    end
    object cdsCustomerAddr1: TStringField
      FieldName = 'Addr1'
      Size = 30
    end
    object cdsCustomerAddr2: TStringField
      FieldName = 'Addr2'
      Size = 30
    end
    object cdsCustomertborders: TDataSetField
      FieldName = 'tborders'
    end
  end
  object cdsOrder: TClientDataSet
    Aggregates = <>
    Params = <>
    Left = 104
    Top = 120
  end
  object dsCustomerNestedOrders: TDataSource
    DataSet = cdsCustomer
    Left = 156
    Top = 120
  end
end
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question