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?
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