How to Combine Data from Several Sources Using SQL and VirtualQuery

The VirtualQuery component allows executing SQL queries to sources that are not a database, but a TDataSet or any of its descendants. It also allows you to connect to several data sources at a time and work with them using SQL queries as with a single data source. Thus, work in a heterogeneous environment is implemented, when execution results of queries to different sources can be retrieved in a single data set.

Query to different DBMS’s

For example, let’s combine DEPT tables from two DBMS’s, Oracle and PostgreSQL, within a single VirtualQuery.

Content of the Oracle DEPT table:

Content of the PostgreSQL DEPT table:

1. Place a TOraSession component onto the form and set Oracle DB connection parameters using Session Editor.
2. Add a TOraQuery component onto the form, set Name=OraQuery, and specify the following query in the OraQuery Editor:

Select * From Dept

3. Place a TPgConnection component onto the form and set PostgreSQL DB connection parameters using Connection Editor.
4. Add a TPgQuery component onto the form, set Name=PgQuery, and specify the following query in the PgQuery Editor:

Select * From Dept

5. Place a TVirtualQuery component onto the form and open its Source DataSet Editor using the component’s shortcut menu
6. In the editor that appeared, add description of the source for Oracle:

   

7. Then, in the similar way, add description of the source for PostgreSQL:

   

In this case, within the scope of VirtualQuery, we can call the OraQuery query using code name OraDept, and call PgQuery via PgDept. Moreover, for data source identification, in addition to specifying the table code name, we can also use schema code name (the SchemaName property). This allows a developer to refer to data sources flexibly, e.g., using the same table name and different schema names.

8. Let’s open the prepared queries:

OraQuery.Active := True;
PgQuery.Active := True;

9. In the VirtualQuery Editor, execute the following query:

Select DName, Loc 
  From OraDept

Union

Select DName, Loc 
  From PgDept

Order By DName

We will get the following result set:

Thus, using VirualQuery, we have combined data from two different sources. We got the possibility to work with this data via SQL. This may appear quite useful, for example, when generating common reports containing data from various databases.

Using custom data sources

VirtualQuery functionality is not limited by work with queries to various DBMS’s. A data source for it can be any information retrieved with some TDataSet descendant. This can be XML documents, text files, various devices logs, etc. It is not a secret for anyone, that volumes of data to be processed constantly grow. Loggers, tracking systems, Smart House systems, various IoT devices, and other modern digital gadgets operate huge amounts of data nowadays. At this, data is often stored not in databases, but as files of various formats stored in a distributed environment. It is often handy to process such information with regular SQL queries.

Let’s consider an example of retrieving information about smart-phone models as the following data set:

• VendorName – smartphone vendor name
• ModelName – model name
• Specification — short technical characterstics

We’ll try to obtain required data from 3 different sources.

Let the vendor list be an XML document ‘Vendor.xml’

Load this document to the TVirtualTable component:

VT := TVirtualTable.Create(nil);
VT.LoadFromFile('Vendor.xml');

The result set will look as follows:

where ID – primary key, Name – vendor name

Let’s prepare the vendor list using the TClientDataSet component. Fill it in with the following data:

where ID – primary key, VendorID — link to a smartphone vendor, ModelName — model name.

The code for filling in the ClientDataSet:

  CDS := TClientDataSet.Create(nil);
   ...
  CDS.AppendRecord([9800, 10, 'Galaxy S7 Edge']);
  CDS.AppendRecord([9830, 10, 'Galaxy Note 5']);
  CDS.AppendRecord([1001, 20, 'iPhone 6']);
  CDS.AppendRecord([1356, 20, 'iPhone 6 Plus']);
  CDS.AppendRecord([3582, 40, 'Lumia 950 XL Dual Sim']);

And, finally, we are going to take tech specs of smartphone models from a normal text file named «NoteData.txt», that includes lines as follows:

Model ID Value , Model Specification Value

where Model ID Value – link to the smartphone model, Model Specification Value – model characteristics description.

The comma “ , “ character serves as a field separator. Data from the text file will be presented as a dataset using the TvirtualDataSet component. For this, let’s implement OnGetRecordCount and OnGetFieldValue methods by applying the TStringList class. In the OnGetRecordCount we should specify the number of rows contained in TVirtualDataSet. In our case, the number of rows is defined by the number of elements in TStringList.

In the OnGetFieldValue method, we should define how we are going to fetch data for each field of TVirtualDataSet. At this, the RecNo parameter will define the current record number. We have only to describe obtaining the necessary data using an output parameter – out Value: Variant .

Thus, we have prepared three different data sources:

• VT — vendor data
• CDS – model data
• VDS – smartphone specifications

Execute the following code to retrieve the resulting dataset:

   VirtualQuery := TVirtualQuery.Create(nil);
   …
  VT.Open;
  CDS.Open;
  VDS.Open;
  

  VirtualQuery.SourceDataSets.Add(VT, '', 'Vendor');
  VirtualQuery.SourceDataSets.Add(CDS, '', 'Model');
  VirtualQuery.SourceDataSets.Add(VDS, '', 'Info');

  VirtualQuery.SQL.Text := '  Select Vendor.Name As VendorName,' +
                           '         Model.ModelName, ' +
                           '         Info.Specification ' +
                           '    From Model ' +
                           '         LEFT JOIN Vendor ON Model.VendorID = Vendor.ID ' +
                           '         LEFT JOIN Info ON Model.ID = Info.ID ' +
                           'Order By 1 ,2 ';
  VirtualQuery.Open;

As a result, we will get the following dataset:

Data editing with VirtualQuery

VirtualQuery can help a developer work with third-party datasets using unified Devart DAC products’ functionality in an application. So, for example, VirtualQuery can serve not only for data reading, as shown above, but for data modification in data sources linked to it as well. Let’s consider an example of such behavior below.

By clicking the «Edit» button in our project, the following code will be executed:

  VirtualQuery.UpdatingTable := 'Model';
   VirtualQuery.Edit;
   VirtualQuery.FieldByName('ModelName').AsString := VirtualQuery.FieldByName('VendorName').AsString + ' ' + VirtualQuery.FieldByName('ModelName').AsString;
   VirtualQuery.Post;

In this case, using UpdatingTable, we have specified the name of the edited data source – ‘Model’. The modification of the field value ‘ModelName’ is implemented with calls of the Edit and Post methods.

Summary

The projects above demonstrate how to work with the TVirtualQuery component, including execution of a SQL query to several data sources, as well as data reading/writing in prepared XML and text documents.

The used files «NoteData.txt», «Vendor.xml» are available for download ….
And the source code of the considered projects is included in Demos, that are distributed with VirtualDAC.