CODEWITHSUNDEEP
sqlsql-serverdelphidelphi-7bde
horgh
horgh

Reputation: 18553

How to union data from different databases?

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?

Upvotes: 11

Views: 2315

Answers (5)

Branko
Branko

Reputation: 1468

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

Upvotes: 4

jachguate
jachguate

Reputation: 17203

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.

Upvotes: 3

Gerry Coll
Gerry Coll

Reputation: 5975

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.

Upvotes: 2

ErvinS
ErvinS

Reputation: 1116

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

I have found this link

Upvotes: 0

bummi
bummi

Reputation: 27384

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

Upvotes: 5

Related Questions