Loading metadata from several databases at once (VCL, ActiveX and Java)
Last modified:
Warning:
This article is related to Java, ActiveX, and VCL editions.
The relevant article for Active Query Builder .NET Editon can be found here:
Active Query Builder is intended to build, parse and analyze SQL queries, but not to execute them in any way. This means that you can load metadata from several databases at once into the component's metadata container, and the component will be able to build a query using these objects (by the rules of specific syntax provider).
Thus, if you add objects from several databases of a single MS SQL Server, this server will be able to execute this query. But if you'll try to add an object from several servers not linked with each other, no one can run this query.
Some database servers (ex. MS SQL, Oracle) allow to link tables and entire databases from other servers and execute SQL queries against them.
The other way is to analyze the query, then execute it by parts against respective database servers and combine their results. It is not easy to implement, but I know that some of our customers have dealt successfully with this task.
To load metadata from different databases, use the QueryBuilder.MetadataContainer.LoadMetadataByMask method.
This method accepts three parameters: Object name mask, Schema name mask and Database name. First two parameters are used for metadata filtration. These masks act exactly as the LIKE operator, so passing '%' value to these parameter instructs the component to load all names. Database name could be omitted when loading metadata from the default database.
To load all objects from the "db_name" database, you should write the following code:
QueryBuilder.MetadataContainer.LoadMetadataByMask("%", "%", "db_name");
To load metadata from different servers, follow the steps below.
- Set the WorkOffine (OfflineMode in .NET Edition) property to True.
- Set the right syntax and metadata providers for a database server.
- Connect to the database server.
- Load Metadata from the database using the LoadMetadataByMask method.
- Disconnect from the server.
Repeat steps 2-5 for all subsequent databases from which you want to extract metadata.
After that,you can save all metadata to the XML file and use it afterward.
---
To get the list of all databases on the server, use the GetDatabaseList method:
Code samples:
SQLQualifiedNameList dbList = new SQLQualifiedNameList(queryBuilder1.SQLContext);
queryBuilder1.GetDatabaseList(dbList);
for (int I = 0; I <= dbList.Count - 1; I++)
{
queryBuilder1.LoadMetadata(dbList[I]);
}
procedure TForm1.SpeedButton4Click(Sender: TObject); var qualifiedNamesList: TSQLQualifiedNamesList; i: integer; begin qualifiedNamesList := TSQLQualifiedNamesList.Create(queryBuilder.SQLContext); try queryBuilder.GetDatabasesList(qualifiedNamesList); for i:=0 to qualifiedNamesList.Count - 1 do begin queryBuilder.LoadMetadata(qualifiedNamesList[i]); end; finally qualifiedNamesList.Free; end; end;