The Alternate Names feature for both database objects and their fields lets completely substitute real names of objects for clearer descriptive names (aliases). User-friendly names make database schema easier to understand by end-users. The "complete substitution" means the replacement of objects names in both user interface, visible SQL expressions as well as in the SQL query text which also can be edited by end-users. Active Query Builder lets assign an alternate name to every object and field in the Metadata Container. You can get the SQL query text either with real or alternate names at any time:
- with real names to execute against a database server,
- with alternate names to display and let edit it by end-users.
Some customers even use this feature for the database schema refactoring: they assign existing queries to the component and get queries with new names of fields and objects in the result!
Component Setup (.NET Edition)
The UseAltNames property exists in several objects.
- The QueryBuilder.BehaviourOptions.UseAltNames property prescribes displaying of alternate names in the component's UI: database schema view, design pane, query columns list, etc.
- The SQLGenerationOptions and its inheritor SQLFormattingOptions classes also have the UseAltNames property which determines the usage of alternate names in the generated SQL text.
Thus, the QueryBuilder.SQLGenerationOptions.UseAltNames property is applied to the QueryBuilder.SQL property and the QueryBuilder.SQLFormattingOptions.UseAltNames to the QueryBuilder.FormattedSQL property. We recommend setting the SQLGenerationOptions.UseAltNames to false to get the SQL for execution against the database server, and the SQLFormattingOptions.UseAltNames to true to display the query with friendly names to the user.
You can also define additional sets of SQL formatting and generation options for various cases.
Component Setup (VCL Edition)
Setting the VCL Edition up is similar to the setup of the .NET Edition, but component names are different: the analogue of SQLGenerationOptions and SQLFormattingOptions components is TacSQLBuilderPlainText. You must have two TacSQLBuilderPlainText components: one with the UseAltNames set to false to execute queries against the database server and another with the UseAltNames set to true to display query text to end-users.
Defining alternate names
Alternate names can be assigned in several ways.
Via the "alt_name" tag of fields and objects in the saved metadata XML file. You can use the Metadata Editor tool (as a standalone utility or by calling it from code) to create and edit metadata XML files.
Adding an object or field to the Metadata Container by code by assigning it to the AltName property.
When an object is added to the Metadata Container by handling the appropriate events. For example, you can use object descriptions loaded from the database as alternate names.
VCL and Java Editions: Use the QueryBuilder.MetadataContainer MetadataObjectLoaded (for objects) and ObjectMetadataLoaded (for fields) events during their load to the Metadata Container.
All .NET Editions: Use the QueryBuilder.MetadataContainer.ItemMetadataLoaded to change properties of newly added child items of the given item. See this event handler sample here: Adding fields and changing objects loaded in the Metadata Container.