HomeActive Query Builder - Metadata handling and filtrationPrinter Friendly Version

Active Query Builder - Metadata handling and filtration

1. Working with metadata in .NET version

1.1. Description of the Metadata Container

Active Query Builder doesn't require the preliminary metadata loading. All you need to do at the beginning of your work with the component is to initialize the Database Schema Tree using the QueryBuilder.InitializeDatabaseSchemaTree method.

Contents

Structure of the Metadata Container

Metadata Container is a tree-like structure that allows for reflection of any modern database server schema sctructure without any distortion. Metadata Container acts as a cache of the underlying server's metadata catalog, and it uses the same naming rules as the underlying server.

There are three types of elements of this structure: namespaces, objects and sub-object items. Namespace nodes reflect object grouping in the database server. They can represent linked servers, databases, schemas and packages. Objects can be tables, views, synonyms, stored procedures or functions (only those procedures and functions that return dataset and can be used as data sources in the SELECT queries). Currently the component deals with three types of sub-object items: fields, parameters and foreign keys, as these types of items are needed by the component to build queries and to represent them visually.

MetadataContainer is the root node of the tree. It stores the MetadataLoadingOptions set of properties, has the OfflineMode property, provides methods to save and load metadata to XML format and events to handle items loading.

MetadataItem is the base type for all metadata items. The following classes are derived from it: MetadataContainer, MetadataNamespace, MetadataObject, MetadataField, MetadataParameter and MetadataForeignKey. The exact type of MetadataNamespace and MetadataObject is determined by the Type property.

Metadata loading process

Metadata Container loads objects from the current database (according to the connection settings) by default and hides objects from other databases and linked servers. If you want to show all available databases as well as from linked servers (if any), you can set the QueryBuilder.MetadataLoadingOptions.LoadDefaultDatabaseOnly property to false. If you want to show metadata from specific databases or linked servers only or you want to limit the schemas visible to the end-user, you can instruct the component to do this using a few simple calls that described in this article. Fine-tuning adjustment of the object's visibility can be achieved by means of the Metadata Structure or Metadata Filters. Also you can add the necessary objects programmatically.

For MS SQL Server, the default structure of Metadata Container can look as follows.

Metadata Container (root)
`-- Database (default)
  |-- schema1
  | `-- database objects
  `-- schema2
    `-- 

For MS SQL Server with the LoadDefaultDatabaseOnly property set to false, the structure can look as follows.

Metadata Container (root)
|-- Linked Server (if any)
| `-- Database(s) 
|   `-- schema(s)
|     `-- database objects
|-- Database (default)
| |-- schema1
| | `-- database objects
| `-- schema2
|   `-- database objects
`-- Database(s) 
  |-- schema1
  | `-- database objects
  `-- schema2
    `-- database objects

For MS Access that does not suppport any namespaces, it will look as follows.

Metadata Container (root)
`-- database objects

The algorithm of filling child nodes of Metadata Container root node is the following:

  • If server supports linked servers - trying to find linked servers - adding them to the Metadata Container.
  • If server supports multiple databases - retrieving the list of databases - adding them to the Metadata Container.
  • If server doesn't support databases, but supports schemas - retrieving the list of schemas - adding them to the Metadata Container.
  • If server doesn't support databases and schemas - retrieving the list of database objects - adding them to the Metadata Container.

Metadata Container loads metadata from the database by demand. This happens when the user expands a node of the Database Schema Tree, when database object is added to the query and when the component associates objects in the parsed query with objects in the database. Learn more: Possible reasons for delays on loading Active Query Builder.

Metadata Container (as well as any other metadata item) can be forced to load all child items with the LoadAll method. It has the withFields parameter that determines whether to load fields or not as loading fields may take a long time in case of large database schema.

Methods and events of the Metadata Container

MetadataItem holds the list of child item within the Items property of the MetadataList class. It has the necessary methods (Find*) to find objects and to load them if they aren't loaded yet in the child items hierarchy. If you want to make searching over the loaded items only, you can get the list of loaded items of specific type using the Items.Find*, Items.GetItems and Items.GetItemsRecursive methods.

The ItemMetadataLoading and ItemMetadataLoaded events of the MetadataContainer allows to perform pre- and post-processing when child metadata items are requested to load from the database. For example, you can override loading of fields or foreign keys for database objects or change their properties when loading is finished. You can find sample handlers of these events here.

Saving and loading content of Metadata Container to XML

Saving the content of the Metadata Container to XML file or string allows for building queries without the necessity to be connected to the database directly. Call the LoadAll and ExportToXML methods of the MetadataContainer object to save the content of Metadata Container in full. The LoadAll method has the "withFields" Boolean parameter which determines the necessity of database objects fields load.

MetadataContainer.LoadAll(<true or false>); // with or without fields
MetadataContainer.ExportToXML(<filename or stream>); // to file or to stream

To load metadata back from XML file or string use the MetadataContainer.ImportFromXML method.

If you have already prepared XML file with metadata for the previous version, you can convert it into a new format by means of the command-line utility "XmlConverter.exe" that is shipped with the component.

1.2. Possible reasons for delays on loading Active Query Builder

Despite the fact that full load of the Database Schema Tree is not necessary in the second version of the component, and can be completed in a separate thread, loading the query diagram may take a long time if you have large database schema. This can happen due to several reasons.

First reason is greater than zero value of the QueryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel property that determines the level of initial Database Schema Tree nodes expansion. Decreasing this property value will lead to faster loading.

The second reason for delay might be due to the fact that the component is inquiring object lists from all schemas that explicitly mentioned in the query text and from schemas marked as default for the current database user. It's needed to determine the correspondence of objects pointed in the query with objects of the currently connected database schema. The process is necessary to retrieve the supplementary information about objects used in the query, such as data field types and foreign keys. This information isn't essential for the component's operability, but it lets increase informational content of the query diagram and make query building process easier.

If delay is considerable and users are ready to refuse informational contents in favor of productivity, you may turn off loading of this information by setting the QueryBuilder.MetadataLoadingOptions.DisableAutomaticMetadataLoading property to True.

We don't recommend to set this property without any necessity, but in some cases set of this property can be justified. These are cases when structure analysis of existing queries is of higher priority than ease of visual query building, and also in case of large database schema.

1.3. Working with Metadata Filters

Using metadata filters you can prevent loading of objects with specific names or to load objects with specific names only. You can define metadata filter to apply to the entire Metadata Container (MetadataContainer.MetadataLoadingOptions.MetadataFilter) or to specific node of the Metadata Structure (MetadataStructureItem.MetadataFilter).

Metadata Filter is a set of filtration rules. Each rule in the list is represented by the MetadataFilterItem object. An item can define objects to be set excluded or included in result set according to the Exclude property.  Metadata Filter can have multiple exclusion and inclusion items (rules). If Metadata Filter has no inclusion items, the exculsion items will be applied to the whole database schema. If Metadata Filter has some inclusion items, they will constitute a subset of database schema, to which the exclusion items will be applied to.

Each Metadata Filter Item has a mask for each type of metadata items: Server, Database, Schema, Package, Object and Field. Each mask determines a subset of objects which names match this mask. Masks act similar to the "LIKE" SQL operator: you can use the percent sign (%) to represent any sequence of characters; use the underscore sign (_) to represent any single character. The CaseSensivite property determines case sensitivity of masks. The ObjectTypes property determines which object types (Table, View, Synonym, Procedure or Function) will be included or excluded.

You may treat those rules as SQL conditions combined with OR operators in WHERE clause of the SELECT statement that retrieves the list of objects to be loaded to the Metadata Container. Conditions marked with the Exclude flag are prefixed with NOT operator. The result SQL condition will look like the following: "([inculsion rule] OR [inclusion rule] OR ...) AND NOT ([exculsion rule] OR [exclusion rule] OR ...)". Masks of one rule are combined with the AND operator.

Alternate way to define metadata filtration rules is to use the MetadataLoadingOptions.ExcludeFilter and MetadataLoadingOptions.IncludeFilter, but it can only be used to filter by database object name, schema name and database object type.  You can use both types of filters at a time: the conditions set using MetadataSimpleFilter will be seamlessly converted to a set of MetadataFilterItem objects which will be added to the MetadataFilter.

The following example adds filter item that excludes objects with names starting with "pre_":

MetadataFilterItem mfi = queryBuilder1.MetadataLoadingOptions.MetadataFilter.Add();
mfi.Exclude = true;
mfi.Object = "pre_%";
mfi.CaseSensitive = true;

And the following example adds filter item that excludes all schemas except the "schema":

MetadataFilterItem mfi = queryBuilder1.MetadataLoadingOptions.MetadataFilter.Add();
mfi.Exclude = false;
mfi.Schema = "dbo";

The following sample demostrates filtration by object type:

MetadataFilterItem metadataFilterItem = queryBuilder.MetadataLoadingOptions.MetadataFilter.Add();
metadataFilterItem.Exclude = false;
metadataFilterItem.ObjectTypes = MetadataType.Table | MetadataType.View;
metadataFilterItem.FlagSystem = false;
metadataFilterItem.FlagUser = true;

queryBuilder.MetadataContainer.Items.Clear();
queryBuilder.InitializeDatabaseSchemaTree();

1.4. Adding objects to the Metadata Container programmatically

Why one may want to modify the content of Metadata Container?

Below are the reasons why one may want to modify the Metadata Container:

  • to delete unwanted objects from the user's field of view,
  • to add descriptions or alternate names for objects and fields,
  • to add virtual objects and fields.
  • to add missing relationships between objects, so the component will be able to join them automatically on adding to the Design Pane.

What one cannot do by means of metadata container modification:

  • It's incorrect to rearrange objects, having placed them in databases, packages and schemas irrelevant to real ones. In case of such changes the incorrect query text will be generated for these objects. Also it'll be impossible to load the fields list dynamically for displaying on the Design Pane.

But there's a way out: you can use the new MetadataStructure object which has a wide range of possibilities for customization of the Database Schema Tree.

Metadata Container Editor

The Metadata Editor tool lets easily delete and add objects, add descriptions, alternate names, etc. To call the Metadata Editor programmatically use the following code:

QueryBuilder.EditMetadataContainer(
   queryBuilder1.MetadataContainer, 
   queryBuilder1.MetadataStructure, 
   queryBuilder1.MetadataLoadingOptions);

It is also available as a standalone executable file for editing metadata XML files.

Programmatic modification of Metadata Container

To match names that were used in a parsed query to the real database objects, and to generate proper object names in the result query text, all metadata objects must reside in appropriate namespaces (databases, schemas and so on).

For example, the "Northwind.dbo.Orders" table of MS SQL Server should be represented as the "Orders" metadata object, which resides in the "dbo" schema, which in turn resides in the "Northwind" database, which finally resides in Metadata Container root namespace:

MetadataItem database = queryBuilder1.MetadataContainer.AddDatabase("Northwind");
MetadataItem schema = database.AddSchema("dbo");
MetadataObject table = schema.AddTable("Orders");

For those database servers that don't provide support for multiple databases, like Oracle, the "HR.JOBS" table should be represented as the "JOBS" metadata object, which resides in the "HR" schema, which resides in Metadata Container root namespace:

MetadataItem schema = queryBuilder1.MetadataContainer.AddSchema("HR");
MetadataObject table = schema.AddTable("JOBS");

For databases like SQLite and MS Access that don't support any namespaces, all objects should be placed directly to the root of Metadata Container:

MetadataObject table = queryBuilder1.MetadataContainer.AddTable("Customers");

To add case sensitive object names to the Metadata Container you should quote them according to the quotation rules of your database server.

Note that its incorrect to rearrange objects, having placed them in databases, packages and schemas irrelevant to real ones for the above reasons. Instead, you can use the new MetadataStructure object which has a wide range of possibilities for customization of the Database Schema Tree.

If you want to omit unnecessary database and schema prefixes in the query text, you should mark the appropriate namespaces as default by setting the Default property to true. Note that the query with objects with omitted prefixes could be executed successfully only in case of executing it against the database server and connection with matching default settings.

To get the default database and schema names from the underlying database server use appropriate static methods of the MetadataNamesFetcher object: GetDefaultDatabaseName, GetDefaultSchemaNames.

To find the default namespace objects in the Metadata Container use the following code:

// load the first level of the Metadata Container which should have the default database 
// in case of connecting to the database server that supports multiple databases
queryBuilder1.MetadataContainer.Items.Load(MetadataType.Database, false);

// get list of loaded databases
List<MetadataNamespace> databases = queryBuilder1.MetadataContainer.Items.GetItems<MetadataNamespace> (MetadataType.Database);

// find the default database in the list
foreach (MetadataNamespace database in databases)
{
    if (database.Default)
    {
        database.Items.Load(MetadataType.Schema, false);
        List<MetadataNamespace> schemas = database.Items.GetItems<MetadataNamespace> (MetadataType.Schema);

        foreach (MetadataNamespace schema in schemas)
        {
            if (schema.Default)
            {
                MetadataObject table = schema.AddTable("Demo Table");
                // ...
            }
        }
    }
}

1.5. Loading, finding and removing objects from the Metadata Container

To load all objects from the database schema to the Metadata Container (taking into account limitations specified by Metadata Filters), use the MetadataContainer.LoadAll method. The withFields parameter instructs whether to load fields for objects or not. Fields loading is the longest operation (separate query is executed for every database object), so the LoadAll(true) call might work very long on large databases. If you fill the metadata container with objects without fields, the fields will be loaded on demand when you add an object to a query.

Metadata Container is a tree-like structure, that groups objects by namespaces in a tree-like structure (servers, databases, schemas, packages - each level can be omitted if it's not applicable to specific database server). To load only one level of this hierarchy, or objects of specifiс type, use the MetadataItem.Items.Load method. For example the MetadataContainer.Items.Load(Database|Schema, true) call will load all database and schema levels of the metadata container.

The other methods allow to iterate items that are already loaded into the MetadataContainer.

  • The MetadataList.Find* methods find and return matching metadata items or their indices (without loading them from database).
  • The MetadataList.GetItems and MetadataList.GetItemsRecursive methods return items from the list (and child items lists) without loading them from database.
  • The MetadataItem.Find* methods recursively load items of child hierarchy from database, and rerurn matching ones.
  • The MetadataStructure.GetAllMetadataItems method loads and returns all metadata items that might be visible in the Database Schema Tree (according to the current Metadata Structure).

To remove an object from the Metadata Conainer, you should remove it from the list of parent metadata items:

    metadataItem.Parent.Items.Remove(metadataItem);

1.6. Adding fields for objects in the Metadata Container using events

Below are the sample handlers of the MetadataContainer.ItemMetadataAdding and the MetadataContainer.ItemMetadataAdded events that perform pre- and post-processing on requesting child metadata for a Table metadata item.

void MetadataContainer_ItemMetadataLoading(object sender, MetadataItem item, MetadataType loadTypes)
{
	if (item.Type == MetadataType.Table && item.Name == "Demo Table")
	{
		MetadataField pk = item.AddField("Field 1");
		pk.FieldTypeName = "int";
		pk.PrimaryKey = true;

		MetadataField f2 = item.AddField("Field 2");
		f2.FieldTypeName = "nvarchar";
		f2.Size = 30;

		item.AddField("Field 3").FieldTypeName = "datetime";
	}
}
private void MetadataContainer_ItemMetadataLoaded(object sender, MetadataItem item, MetadataType loadtypes)
{
	if (item.Type == MetadataType.Table && item.Name == "Demo Table")
	{
		foreach (MetadataField field in item.Items.Fields)
		{
			field.Name = field.Name.ToUpper();
		}
	}
}

1.7. How to detect incorrect object and field names in the query?

On parsing a query, the component gets objects listed in the FROM clause of the query and tries to find a corresponding objects in the Metadata Container. Of course, the Metadata Container should be properly filled with database objects to find a correspondence. This can be done by connecting Active Query Builder to live database connection or you can pre-load the Metadata Container from the XML file.

There are two collections that lists objects and fields in the query: QueryBuilder.QueryStatistics.UsedDatabaseObjects and QueryBuilder.QueryStatistics.UsedDatabaseObjectFields. Each item in these collections has the MetadataObject property that refers to the MetadataItem object contained in the MetadataContainer. If an item has null reference in the MetadataObject property, this may indicate that this object does not really exist in the database.

Another reason of failure might be that objects the query are typed with omitted database or schema names, while the appropriate database and schema objects is not properly marked as default in the Metadata Container. This is usually happens when the query was written on behalf of another database user. In this case the database server itself will fail to execute such a query.

For some reasons you may want to deny parsing of queries with incorrect object names by setting the QueryBuilder.BehaviorOptions.ParsingErrorOnUnknownObjects property to True.

1.8. Instructing to show metadata from several databases or servers

To instruct Active Query Builder to show metadata from some other databases or linked servers, you just need to add few lines of code before initializing the Database Schema Tree.

To add a database:

MetadataItem database = queryBuilder1.MetadataContainer.AddDatabase("DbName");

To add a linked server and all it's databases:

MetadataItem server = queryBuilder1.MetadataContainer.AddServer("ServerName");

To add a database from linked server:

MetadataItem server = queryBuilder1.MetadataContainer.AddServer("ServerName");
MetadataItem database = server.AddDatabase("DbName");

Note that adding a database to the server using this helper method prevents loading of other databases from this server. In the same way you can prevent loading of unnecessary schemas from a database. It's quite simple for Oracle database server which does not support multiple databases:

MetadataItem schema = queryBuilder1.MetadataContainer.AddSchema("SchemaName");

For those database servers that support multiple databases, use the following code:

// load the first level of the Metadata Container which should have the default database
queryBuilder1.MetadataContainer.Items.Load(MetadataType.Database, false);

// get list of loaded databases
IList databases = queryBuilder1.MetadataContainer.Items.GetList(MetadataType.Database);

// find the default database in the list
foreach (MetadataItem database in databases)
{
    if (database.Default)
    {
        MetadataItem schema = database.AddSchema("SchemaName");
    }
}

Executing the code snippets above won't actually load metadata, but only add the root nodes for further loading on demand. After adding the necessary items you may initialize the Database Schema Tree as usual:

queryBuilder1.InitializeDatabaseSchemaTree();

During the initialization process the tree will be expanded according to the QueryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel property. Note that increasing of this property value will lead to increased initialization time as metadata loading is performed on expansion of the Database Schema Tree nodes.


Some users wants to load metadata from different servers that are not linked with each other. Although it is possible to do this, and the component will be able to build a query using objects from different servers, note that there will be no way to execute such queries, until you create your own query execution engine. If this doesn't stop you, follow the steps below.

  1. Set the right syntax and metadata providers for the first database server and connect to it.
  2. Load Metadata by calling the MetadataContainer.LoadAll(true) method. (The withFields parameter set to true instructs to load fields for each database object that may take a long time for large database schema, so please be patient.)
  3. Set the right syntax and metadata providers for the next database server and connect to it.
  4. Add new database (or server) as described above.
  5. Load Metadata from the database by calling the Metadataitem.LoadAll(true) method, where MetadataItem is the newly added database object.

Repeat steps 3-5 for all subsequent databases and servers that you want to load metadata from. After that you may save all metadata to the XML file and use it afterwards.

2. Metadata handling and filtration in Java, VCL and ActiveX Editions

2.1. Metadata pre-filtration

Warning:

This article is valid for Java, ActiveX and VCL editions.

The appropriate article for Active Query Builder 2 .NET Editon can be found here:

Using metadata filters you can prevent loading objects to the Metadata Container from specific schema or with specific object name.

Metadata filters should be defined BEFORE the RefreshMetadata method call.

The following example excludes objects with names starting with "pre_":

MetadataFilterItem mfi = queryBuilder1.MetadataFilter.Add();
mfi.Exclude = true;
mfi.ObjectMask = "pre_%";

And the following example excludes all schemas except the "schema":

MetadataFilterItem mfi = queryBuilder1.MetadataFilter.Add();
mfi.Exclude = false;
mfi.SchemaMask = "schema";

The Exclude flag set to False instructs to load only objects that meet the mask. Being set to True, this mask instructs to load all objects except those that meet the mask.

Object and Schema masks act similar to the "LIKE" SQL operator added to the metadata fetching query.

You can define more than one filter at a time. In this case, filter items will be applied consistently.

Note that masks are case sensitive by default. Set the SchemaMaskCaseSensitive and ObjectMaskCaseSensitive properties to false to define case insensitive masks.

---

The LoadMetadataByMask method is an alternative way to load metadata from the database using filtration:

LoadMetadataByMask("%", "%", "database");

First argument is an object name mask, second is a schema mask and the third argument is a database name, that could be omitted if you database server doesn't support multiple databases.

Usage of the LoadMetadataByMask method substitutes simple metadata filter definition (with the Exclude flag set to False) and call of the RefershMetadata method.

Please note that the RefreshMetadata method will clear the Metadata Container before loading, while the LoadMetadataByMask will not clear it.

2.2. Loading metadata from several databases at once

Warning:

This article is valid for Java, ActiveX and VCL editions.

The appropriate article for Active Query Builder 2 .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 object from several servers not linked with each other, no one can execute 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, execute it's 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, to load metadata from the currently active 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.

  1. Set the WorkOffine (OfflineMode in .NET Edition) property to True.
  2. Set the right syntax and metadata providers for a database server.
  3. Connect to the database server.
  4. Load Metadata from the database using the LoadMetadataByMask method.
  5. Disconnect from the server.

Repeat steps 2-5 for all subsequent databases you want to extract metadata from.

After that you can save all metadata to the XML file and use it afterwards.

---

To get list of all databases from server, use the GetDatabaseList method:

.NET and Java code sample:

SQLQualifiedNameList dbList = new SQLQualifiedNameList(queryBuilder1.SQLContext);
 
queryBuilder1.GetDatabaseList(dbList);
  
for (int I = 0; I <= dbList.Count - 1; I++)
{
   queryBuilder1.LoadMetadata(dbList[I]);
}

VCL code sample:

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;

2.3. Working with the Metadata Container

Warning:

This article is valid for Java, ActiveX and VCL editions.

The appropriate article for Active Query Builder 2 .NET Editon can be found here:

Metadata Container (QueryBuilder.MetadataContainer) is collection of database objects displayed in the metadata tree. Each object contains collections of Fields and Relations (foreign keys). You can easily add or remove objects of this collection to add or remove database objects from the tree. Some database objects can be hidden, i.e. they are loaded in the metadata container, but not visible in the tree. System objects are hidden by default.

Metadata container is normally filled with RefreshMetadata or LoadMetadataByMask, LoadMetadataByFilter methods. These methods fill the list of database objects and their relations, but not their fields as it may take a long time to do that in case of large databases or slow connections. Fields are usually loaded when object is added to the query building area. To load fields during the RefreshMetadata method call you should set the WorkOffline (OfflineMode) property to True.

The WorkOffline (OfflineMode) property instructs the component to work in offline mode, i.e. it denies any hidden SQL query executions (mostly to load fields of some database objects). But in case of direct calls of metadata loading methods, it instructs the component to load metadata information in complete, i.e. with fields of each loaded database object.

If you already have the list of allowed objects, you don't need to call the RefreshMetadata method. Instead you can fill the Metadata Container programmatically:

For Each AllowedTableName As String In AllowedTables
   QueryBuilder.MetadataContainer.AddTable("", AllowedTableName, False, "")
Next

Fisrt parameter of the AddTable method is a schema name, fourth is a database name, third parameter is a flag for system objects.

2.4. Loading metadata programmatically

Warning:

This article is valid for Java, ActiveX and VCL editions.

The appropriate article for Active Query Builder 2 .NET Editon can be found here:

  1. Adding database objects to the Metadata Container.

    Metadata Container is collection of Metadata Objects. Each object holds the list of their fields and relations (foreign keys).

    public void FillMetadataContainer()
    {
    	// create table
    	MetadataObject tableOrders = 
    		queryBuilder.MetadataContainer.AddTable("MySchema", "Orders", false, "MyDB");
    	tableOrders.Fields.AddField("OrderID");
    	tableOrders.Fields.AddField("OrderDate");
    	tableOrders.Fields.AddField("CustomerID");
    	tableOrders.Fields.AddField("ResellerID");
    
    	// create another table
    	MetadataObject tableCustomers = 
    		queryBuilder.MetadataContainer.AddTable("MySchema", "Customers", false, "MyDB");
    	tableCustomers.Fields.AddField("CustomerID");
    	tableCustomers.Fields.AddField("CustomerName");
    	tableCustomers.Fields.AddField("CustomerAddress");
    
    	// add a relation between these two tables
    	MetadataRelation relation = tableCustomers.Relations.Add();
    	relation.ChildSchemaStr = "MySchema";
    	relation.ChildDatabaseStr = "MyDB";
    	relation.ChildNameStr = "Orders";
    	relation.KeyFields.Add("CustomerID");
    	relation.ChildFields.Add("CustomerID");
    
    	//create view
    	MetadataObject viewResellers = 
    		queryBuilder.MetadataContainer.AddView("MySchema", "Resellers", false, "MyDB");
    	viewResellers.Fields.AddField("ResellerID");
    	viewResellers.Fields.AddField("ResellerName");
    }
    

    To add case sensitive object, database, schema or field names you should quote them according to the quotation rules of your database server.

  2. Filling database object field lists by demand.

    Active Query Builder allows to add fields for each database object not right after it's addition to the Metadata Container, but fill them when they will be used in some query at the first time using the QueryBuilder.MetadataContainer.ObjectMetadataLoading event handler:

    void MetadataContainer_ObjectMetadataLoading(MetadataContainer sender, MetadataObject metadataObject)
    {
           if (metadataObject.NameStr == "Orders")
           {
                   MetadataFieldList fields = metadataObject.Fields;
                   fields.AddField("Field 1").FieldTypeName = "nvarchar";
                   fields.AddField("Field 2").FieldTypeName = "int";
                   fields.Loaded = true;
           }
    }
    
  3. Modifying field lists after loading from the database.

    You can remove unwanted fields after they were loaded from the database in the QueryBuilder.MetadataContainer.ObjectMetadataLoaded event handler:

    void MetadataContainer_ObjectMetadataLoaded(MetadataContainer sender, MetadataObject metadataObject)
    {
           if (metadataObject.NameStr == "Orders")
           {
                   metadataObject.Fields.Delete(metadataObject.Fields.FindFieldByName("Freight"));
                   metadataObject.Fields.Delete(metadataObject.Fields.FindFieldByName("ShipVia"));
           }
    }
    

2.5. Loading metadata from XML file

Warning:

This article is valid for Java, ActiveX and VCL editions.

The appropriate article for Active Query Builder 2 .NET Editon can be found here:

You can create the XML file that stores all necessary metadata information and loads it on start of the Query Builder. This might be helpful when database connection is not available at the client side (in web projects using ActiveX or Java Edition). This feature also could be helpful in the following cases.

  • Caching to speed up the metadata loading process.
  • Excluding unwanted objects from the metadata.
  • Adding missing information to the database schema (for example, some databases may miss the foreign key information, so joins will not be created automatically)
  • Adding descriptions or alternate names for database objects and fields.
  • Adding virtual tables and fields.

To build an XML file, you can do the following:

The sample code below is for the ActiveX Edition of Active Query Builder, but it is similar for the rest of editions.

// =========================
// Part one: creation of initial XML file
// ========================

// set this option to True BEFORE retrieving metadata from the database.
// setting this option will slower metadata retrieval procedure,
// but allows you to extract fields of all database objects at once.
ActiveQueryBuilderX1.WorkOffline = True; // This property is called "OfflineMode" in the .NET Edition

// setup database connection
ActiveQueryBuilderX1.ConnectionString = "your ADO connection string here";

// Retrieve metadata from the database and load it to the Metadata Container
ActiveQueryBuilderX1.Connect; //Other editions should use the RefreshMetadata method instead

// save metadata container content to the XML file
ActiveQueryBuilderX1.MetadataContainer.SaveToXMLFile("your filename.xml");

// or retrieve the same information as a string:
mystring = ActiveQueryBuilderX1.MetadataContainer.XML;

// =========================
// Part two: loading metadata from XML file
// ========================

// you should also set the offline mode when working without database connection
// to prevent retrieval of additional metadata information from the database
ActiveQueryBuilderX1.WorkOffline = True;

// load metadata container container from XML file or from URL
ActiveQueryBuilderX1.MetadataContainer.LoadFromXMLFile("your filename or URL.xml");

// or load the same information from string
ActiveQueryBuilderX1.MetadataContainer.XML = mystring; 

To cache metadata you should create an XML file that contain database objects, but not their fields (otherwise, creation of the XML file will be slower). An example of caching is available in the Full-featured MDI Demo (Active Query Builder demo project in VCL Edition). To do this you should call the RefreshMetadata method with the OfflineMode property set to False and then temporary set it to True on calling the SaveToXMLFile method. (See Working with Metadata Container to understand the OfflineMode property).

   queryBuilder.RefreshMetadata();
   queryBuilder.OfflineMode = true;
   queryBuilder.MetadataContainer.SaveToXMLFile(cacheFile);
   queryBuilder.OfflineMode = false;

2.6. Asyncronous metadata loading in VCL and ActiveX editions

Metadata loading process may take a long time in case of large database schema. Since version 1.23 metadata can be loaded asyncronously to let the end-user start working with the component instantly.

During the metadata loading process the end user can load previously saved queries and modify them visually, but the Database Schema Tree will be empty. The field lists will be filled correctly, but field data types will be determined by querying the appropriate tables, not by retrieving this information from system tables, thus they will be shown in a different way. For example, during the metadata loading process, a field of VARCHAR(30) type will be shown as "WideString" or "String", but after completion of this process, data type will be updated automatically and will be displayed as "VARCHAR(30)". Of course, this behavior will be true for those servers that allow for retrieval of information about data types (for example, such information is unavailable for MS Access and some other desktop databases).

In the ActiveX version, metadata is loaded asyncronously by default. To turn it back to syncronous mode, set the ActiveQueryBuilderX.AsyncMetadataLoading property to false.

The following methods work in syncronous mode: RefreshMetadata, LoadMetadata. The newly added RefreshMetadataAsync and LoadMetadataAsync methods work in asyncronous mode. The new CancelAsyncMetadataLoading method lets cancel asyncronous metadata loading. The new OnMetadataLoaded event allows for taking additional action after completion of the metadata loading process.

It is possible to take advantage of this functionality for your individual needs to make copies of database connections and to execute SQL queries in another threads, regardless of the data access components being used. You can read about this in a separate article: