How to union data from different databases?

Question

I came across the necessity to union two selects from different databases, namely paradox (in bde) and ms sql server.

Currently bde (through TQuery) is used only in this part of the programm (i.e. dbgrid). Now I need to add some data stored in ms sql server database (with which I usually use TADOQuery) to the same grid.

Although queries are executed over completely different tables, the result set of columns is named and typed similarly (I mean, if I had these tables, say, in ms sql server database, I could use a trivial union for that).

Is there any way to unite recordsets selected from these in delphi7 that I could use the result as a data source for a dbgrid?

I don't know delphi but in .net we have Lists and you can add all same-type records to a List and then cast it to a DataTable. — Mahdi Tahsildari, Dec 19 '12 at 07:34
BDE supports (or supported) heterogeneous queries - see http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/devwin32/fhxr27506_xml.html. IIRC I used a few over a decade ago for some quick'n'dirty datamerges. — Gerry Coll, Dec 19 '12 at 10:23

score 5 · Accepted Answer · answered Dec 19 '12 at 07:36

You could use a clientdataset, created by the definitions of eg. the dataset of your SQL-Server dataset and add data of your paradox dataset. TFieldDefArray can be empty in your case.

type
  TMyFieldDef = Record
    Name: String;
    Size: Integer;
    DataType: TFieldType;
  end;

  TFieldDefArray = array of TMyFieldDef;


function GetClientDSForDS(ADataSet: TDataSet; AFieldDefArray: TFieldDefArray; AClientDataSet: TClientDataSet = nil; WithRecords: Boolean = true)
  : TClientDataSet;
var
  i: Integer;
  Function NoAutoInc(ft: TFieldType): TFieldType;
  begin
    if ft = ftAutoInc then
      Result := ftInteger
    else
      Result := ft;
  end;

begin

  if Assigned(AClientDataSet) then
    Result := AClientDataSet
  else
    Result := TClientDataSet.Create(nil);
  Result.Close;
  Result.FieldDefs.Clear;

  for i := 0 to ADataSet.FieldCount - 1 do
  begin
    Result.FieldDefs.Add(ADataSet.Fields[i].FieldName, NoAutoInc(ADataSet.Fields[i].DataType), ADataSet.Fields[i].Size);
  end;

  for i := 0 to High(AFieldDefArray) do
    Result.FieldDefs.Add(AFieldDefArray[i].Name, AFieldDefArray[i].DataType, AFieldDefArray[i].Size);

  Result.CreateDataSet;
  for i := 0 to ADataSet.FieldCount - 1 do
  begin
    Result.FieldByName(ADataSet.Fields[i].FieldName).DisplayLabel := ADataSet.Fields[i].DisplayLabel;
    Result.FieldByName(ADataSet.Fields[i].FieldName).Visible := ADataSet.Fields[i].Visible;
  end;

  if WithRecords then
  begin
    ADataSet.First;
    while not ADataSet.Eof do
    begin
      Result.Append;
      for i := 0 to ADataSet.FieldCount - 1 do
      begin
        Result.FieldByName(ADataSet.Fields[i].FieldName).Assign(ADataSet.Fields[i]);
      end;
      Result.Post;
      ADataSet.Next;
    end;
  end;
end;

another attempt might be creating a linked server for paradox, I didn't try that...

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_24067488.html

Though I do not have access to _experts-exchange.com_ anyway thanks (i.e. +1) for the idea with linked server. Maybe I'll give it a try someday. And accept - for the `TClientDataSet`. I managed to fullfil my task with it. Thanks a lot — horgh, Dec 19 '12 at 23:52

Branko · Answer 2 · 2012-12-21T16:41:56.423

4

No problem with AnyDAC LocalSQL. You can execute SQL's with any DataSet, not only select SQL, insert, update, delete SQL too.

edited Dec 21 '12 at 16:41

answered Dec 19 '12 at 13:24

Branko

1,384
1
16
35

Unfortunately I tend to use built-in functionality. Nevertheless, thank you. – horgh Dec 20 '12 at 00:07

score 3 · Answer 3 · answered Dec 19 '12 at 15:04

You can use the Built-in TClientDataSet functionality to union the data by appending the data from the second dataset to the data of the first one.

There are different ways to do it, my preferred one because the simple code would be to add two DataSetProviders and link it to each of your DataSets, for example

dspBDE.DataSet := MyTQuery;
dspADO.DataSet := MyAdoQuery;

Then, to open your DataSets, you can just do:

MyClientDataSet.Data := dspBDE.Data;
MyClientDataSet.AppendData(dspADO.Data, True);

To make this to work, both DataSets have to match the field number and data types. Since your structures are similar, you can work by typecasting in your SQL if this does not happen automatically.

+1 - this would really simplify working with `TClientDataSet`. Thanks — horgh, Dec 19 '12 at 23:54

score 2 · Answer 4 · answered Dec 19 '12 at 10:27

2

BDE supports (or supported) heterogeneous queries This allows queries to span more than one dataset, but with a limited SQL syntax.

IIRC I used a few over a decade ago for some quick'n'dirty datamerges, but I can't remember the specifics - I haven't touched BDE for years.

answered Dec 19 '12 at 10:27

Gerry Coll

5,867
1
27
36

Really glad for you: "_I haven't touched BDE for years_". I am waiting for the time when I'll be able to say the same about myself impatiently))... – horgh Dec 19 '12 at 23:45

score 0 · Answer 5 · answered Dec 19 '12 at 09:19

0

Several years ago (Delphi 7) i used TxQuery, but i'dont know if it is still in development

I have found this link

answered Dec 19 '12 at 09:19

ErvinS

1,116
7
13

How to union data from different databases?

5 Answers5