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?

Espousal

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. – Angus

BDE supports (or supported) heterogeneous queries - see docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/…. IIRC I used a few over a decade ago for some quick'n'dirty datamerges. – Coulter

Answer 1

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

Glynisglynn

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 – Espousal

Answer 2

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

Locksmith

Unfortunately I tend to use built-in functionality. Nevertheless, thank you. – Espousal

Answer 3

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.

Lasonyalasorella

+1 - this would really simplify working with TClientDataSet. Thanks – Espousal

Answer 4

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.

Coulter

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))... – Espousal

Answer 5

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

I have found this link

Warp