1.1. Quick Start Guide for .NET WinForms edition 1.x

Important:

This article might be obsolete!

It is valid for previous version of Active Query Builder WinForms .NET Edition (1.x).

The appropriate article for the new version can be found here: Quick Start Guide for WinForms .NET Edition.

1. Place the QueryBuilder component on the form.

   using ActiveDatabaseSoftware.ActiveQueryBuilder;
   ...
   QueryBuilder queryBuilder1 = new QueryBuilder();

2. Place required metadata and syntax provider components on the form. Define a proper database connection object for the metadata provider.

   OleDbConnection connection = new OleDbConnection();
   connection.ConnectionString = "<your connection string here>";
   
   OLEDBMetadataProvider metadataProvider = new OLEDBMetadataProvider();
   metadataProvider.Connection = connection;
   
   UniversalSyntaxProvider syntaxProvider = new UniversalSyntaxProvider();

3. Link the components above to the QueryBuilder by setting MetadataProvider and SyntaxProvider properties

   queryBuilder1.MetadataProvider = metadataProvider;
   queryBuilder1.SyntaxProvider = syntaxProvider;

4. Place an SQLBuilder component on the form to get SQL code generated by the QueryBuilder component with formatting. Link it to the QueryBuilder object by setting the QueryBuilder property.

   PlainTextSQLBuilder sqlBuilder = new PlainTextSQLBuilder();
   sqlBuilder.QueryBuilder = queryBuilder1;

5. Add the TextBox or any other text editing component to a form.

6. Now you should establish connection between SQLBuilder and the TextBox components.
   Enter the following code to Validate event of TextBox component:

   private void textBox1_Validating(object sender, System.ComponentModel.CancelEventArgs e)
   {
   	try
   	{
   		// Update the query builder with manually edited query text:
   		queryBuilder.SQL = textBox1.Text;
   	}
   	catch (SQLParsingException ex)
   	{
   		e.Cancel = true;
   		
   		// Set caret to error position
   		textBox1.SelectionStart = ex.ErrorPos.pos;
   		// Report error
   		MessageBox.Show(ex.Message, "Parsing error");
   	}
   }

    

   Enter the following code to SQLUpdated event of SQLBuilder component:

   textBox1.Text = sqlBuilder.SQL;
   

    

7. Execute the following code to load metadata and activate Active Query Builder:
   

   queryBuilder1.RefreshMetadata();
   

    

8. That's all! Now you can run your application.

1.2. Quick Start Guide for ASP.NET edition

1. Create a new solution and add a new project – ASP.NET Web Application.

2. Add references to the following assemblies:

   • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder.dll
   • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Control.dll
   • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.dll
   • \assemblies\third-party\Newtonsoft.Json.dll
   • \assemblies\third-party\log4net.dll

   Active Query Builder requires Newtonsoft Json assembly version not earlier than 4.5. Apache log4net.dll is required only in debug configuration. Active Query Builder is binded to the log4net binary version labeled as "oldkey" for compatibility with earlier versions of this library.

   You may also need to add the assemblies for specific metadata providers to retrieve metadata from a database, e.g.:

   • \assemblies\.NET 2.0\ActiveDatabaseSoftware.OLEDBMetadataProvider.dll

3. Add the necessary HTTP handlers and modules to the "web.config" file according to your web server version.

   • For IIS 6, add the following handlers in the "configuration/system.web/httpHandlers" section:

     
        <add path="handlers/exchange.axd" verb="*" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.Exchange,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server"
        />
        <add path="handlers/sessonKeeper.axd" verb="*" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.SessionKeeper,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server"
        />
        <add path="handlers/ResourceHandler.axd" verb="*"
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.ResourceHandler,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server"
        />
     

     and the following module to the "configuration/system.web/modules" section:

     
        <add name="HttpModule" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server"
        />
     

   • For IIS 7, add the following handlers in the "configuration/system.webServer/handlers" section:

     
        <add name="handlers/exchange.axd_*" preCondition="integratedMode"  
           path="handlers/exchange.axd" verb="*" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.Exchange,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server" 
        />
        <add name="handlers/sessonKeeper.axd_*" preCondition="integratedMode"  
           path="handlers/sessonKeeper.axd" verb="*" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.SessionKeeper,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server" 
        />
        <add name="handlers/ResourceHandler.axd_*" preCondition="integratedMode" 
           path="handlers/ResourceHandler.axd" verb="*" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.ResourceHandler,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server" />
        />
     

     and the following module to the "configuration/system.webServer/modules" section:

     
        <add name="HttpModule" preCondition="integratedMode"
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server" />
     

   For MVC project, create the following empty files in the root of your web site:

   
   /handlers/exchange.axd
   /handlers/sessonKeeper.axd
   /handlers/ResourceHandler.axd
   

4. Get content from the "web_parts" directory ("css", "img" and "js" sub-folders) and put it in the directory of the web page or the parent control. Alternatively you can set paths to specific locations of these directories using the QueryBuilderControl.DirectoryJS and the QueryBuilderControl.DirectoryCSS properties.

   Active Query Builder ASP.NET Edition requires jQuery 1.9 or higher and jQueryUI 1.10 or higher.

   The necessary JavaScript libraries are loaded automatically unless a different behavior is not determined by the QueryBuilderControl.LoadJScript property. Setting this property to false might be needed in case of using the Script Manager or some other dynamic JavaScript loading tool.

   To load the necessary libraries using Script Manager having set the LoadJScript to false, usе this code:

   
   <asp:ScriptManager runat="server">
      <Scripts>
         <asp:ScriptReference Name="jquery" />
         <asp:ScriptReference Name="jquery.ui.combined" />
         <asp:ScriptReference Path="~/js/release/usr_v0_x_x.js" />
   

   To load the necessary libraries manually having set the LoadJScript to false, add the following code to the HEAD section of your web page:

   
   <script src="js/release/jquery.js" type="text/javascript"></script>
   <script src="js/release/jquery-ui.js" type="text/javascript"></script>
   <script src="js/release/usr_v0_x_x.js" type="text/javascript"></script>
   

5. Typical HTML code can be found in the "web_parts\!(C# or VB)" folder, in "Default.aspx" and "QueryBuilder.ascx" files, or you can simply add the following content to your web-page.

   <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Client.Default" %>
   <%@ Register Assembly="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Control" Namespace="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Control" TagPrefix="AQB" %>
   <html>
       <head runat="server">
           <title></title>
       </head>
       <body>
           <form id="Form1" runat="server">
               <AQB:QueryBuilderControl ID="QueryBuilderControl1" runat="server" OnInit="QueryBuilderControl1_Init" />
               <div id="all">
                   <div id="content-container">
                       <div id="qb-ui">
                           <AQB:ObjectTree ID="ObjectTree1" runat="server" ShowFields="true" />
                           <div id="center">
                               <AQB:Canvas ID="Canvas1" runat="server" />
                               <AQB:Grid ID="Grid1" runat="server" />
                               <AQB:StatusBar ID="StatusBar1" runat="server" />
                           </div>
                           <div class="clear"></div>
                       </div>
                   </div>
                   <AQB:SqlEditor ID="SQLEditor1" runat="server"></AQB:SqlEditor>
               </div>
           </form>
   
       </body>
   </html>

6. Place your initialization code in the QueryBuilderControl.Init event handler. There you should create Metadata and Syntax provider components and link these providers to the QueryBuilder object by setting the MetadataProvider and SyntaxProvider properties. Define a proper database connection object as a source for the Metadata provider or load metadata from the XML file.

   The server side of the component is based on the WinForms .NET Edition of Active Query Builder. There are two object references available to get access to their methods and properties: QueryBuilderControl.QueryBuilder and QueryBuilderControl.PlainTextSQLBuilder.

   private void QueryBuilderControl1_OnInit(object sender, EventArgs e)
   {
       // Get instance of the QueryBuilder object
       QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;
   
       // create an instance of the proper syntax provider for your database server. 
       // - use AutoSyntaxProvider if you want to detect your database server automatically 
       //   (autodetection works in case of live database connection only);
       // - use ANSI or Universal syntax provider only if you can't find the right syntax 
       //   provider for your database server.
       MSSQLSyntaxProvider syntaxProvider = new MSSQLSyntaxProvider(); 
   
       queryBuilder.SyntaxProvider = syntaxProvider;
   
       // ===========================================================
       // a) you can load metadata from the database connection using live database connection
       
       OleDbConnection connection = new OleDbConnection();
       connection.ConnectionString = "<your connection string here>";
   
       OLEDBMetadataProvider metadataProvider = new OLEDBMetadataProvider();
       metadataProvider.Connection = connection;
   
       queryBuilder.MetadataProvider = metadataProvider;
   
       // call the RefreshMetadata to load metadata from a database connection 
       queryBuilder.RefreshMetadata();
   
       // ===========================================================
       // b) or you can load metadata from the pre-generated XML file
       
       XmlDocument doc = new XmlDocument();
       doc.Load(Server.MapPath(ConfigurationManager.AppSettings["XmlMetaData"]));
   
       queryBuilder.OfflineMode = true;
       queryBuilder.MetadataContainer.LoadFromXML(doc.OuterXml);
   }

7. To retrieve the query text from the query builder control use the following code:

   protected void btnGetQuery_Click(object sender, System.EventArgs e)
   {
       // First variant:
       // a) if your code is at the page that contains the QueryBuilderUC User Control:
       QueryBuilderControl control = (QueryBuilderControl) QueryBuilderUC1.FindControl("QueryBuilderControl1");
       QueryBuilder queryBuilder = control.QueryBuilder;
       PlainTextSQLBuilder sqlBuilder = control.PlainTextSQLBuilder;
   
       // b) if your code is inside the QueryBuilderUC User Control:
       QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;
       PlainTextSQLBuilder sqlBuilder = QueryBuilderControl1.PlainTextSQLBuilder;
   
       // Second variant: globally available static helpers
       QueryBuilder queryBuilder = SessionStore.Current.QueryBuilder;
       PlainTextSQLBuilder sqlBuilder = SessionStore.Current.PlainTextSQLBuilder;
   
       // Common part:
       string plainSQL = queryBuilder.SQL; // unformatted text
       string formattedSQL = sqlBuilder.SQL; // formatted text
   }

   To retrieve the query text from the text editor, use the following code:

   protected void btnGetQuery_Click(object sender, System.EventArgs e)
   {
       // if your code is at the page that contains the QueryBuilderUC User Control:
       SQLEditor editor = (SQLEditor) QueryBuilderUC1.FindControl("SQLEditor1");
       string textSQL1 = editor.SQL;
   
       // if your code is inside the QueryBuilderUC User Control:
       string textSQL2 = SQLEditor1.SQL;
   }

   If you want to parse the final modifications made to SQL text by the end-user before the query execution, additionaly use the following code:

   
       try
       {
           queryBuilder.SQL = editor.SQL;
   
           // execute the query here
       }
       catch (Exception ex)
       {
           MessageBox.Show(ex.Message, "Parsing error");
       }
   

8. To assign SQL query text to the control use the following code:

   protected void btnSetQuery_Click(object sender, System.EventArgs e)
   {
       // if your code is at the page that contains the QueryBuilderUC User Control:
       SQLEditor editor = (SQLEditor) QueryBuilderUC1.FindControl("SQLEditor1");
       editor.SQL = "SELECT * FROM ...";
   
       // if your code is inside the QueryBuilderUC User Control:
       SQLEditor1.SQL = "SELECT * FROM ...";
   }

   If you want to do this without reloading a web page, additionaly use the following JavaScript code:

   QB.Web.Application.fullUpdate();

   To update the SQL text on the client use the following JavaScript code:

   QB.Web.Application.importSQL('Select * From ...');
   QB.Web.Application.refreshSql();

9. That's all! Now you can run your application.

1.3. Quick Start Guide for Java edition

1. Drop com.adbs.querybuilder.QueryBuilder on a form using a visual form designer or create an instance programmatically.

   
   import com.adbs.querybuilder.*;
   ...
   QueryBuilder queryBuilder1 = new QueryBuilder();

2. Add any text control on your form (for example, JTextPane). Handle its Focus event and put changed query text to the query builder.

   
   jTextPane1.addFocusListener(new FocusAdapter()
   {
      public void focusLost(FocusEvent evt)
      {
         try
         {
            queryBuilder1.setSQL(jTextPane1.getText());
         }
         catch (QueryBuilderException ex)
         {
            JOptionPane.showMessageDialog(this, ex.getMessage());
         }
      }
   });

3. Create PlainTextSQLBuilder instance and bind it to the query builder instance.

   
   PlainTextSQLBuilder sqlBuilder = new PlainTextSQLBuilder();
   sqlBuilder.setQueryBuilder(queryBuilder1);

4. Handle SQLUpdated event of the PlainTextSQLBuilder to receive notification about query text changes.

   
   sqlBuilder.addSQLUpdatedEventListener(new SQLUpdatedEventListener()
   {
      public void sqlUpdatedEventOccurred(SQLUpdatedEvent event)
      {
         try
         {
            // set the query text to a text control
            jTextPane1.setText(sqlBuilder.getSQL());
         }
         catch (QueryBuilderException ex)
         {
            JOptionPane.showMessageDialog(this, ex.getMessage());
         }
      }
   });

5. Create metadata provider and supply it with SQL server connection. Set corresponding syntax provider and call the RefreshMetadata method to load objects from a database.

   
   try
   {
      JdbcMetadataProvider metadataProvider = new JdbcMetadataProvider();
      Class.forName("com.mysql.jdbc.Driver");
      metadataProvider.setConnection(DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password"));
   
      MySQLSyntaxProvider syntaxProvider = new MySQLSyntaxProvider();
   
      queryBuilder1.setSyntaxProvider(syntaxProvider);
      queryBuilder1.setMetadataProvider(metadataProvider);
   
      queryBuilder1.refreshMetadata();
   }
   catch (ClassNotFoundException ex)
   {
      JOptionPane.showMessageDialog(this, "Failed to load JDBC driver: \n" + ex.getMessage());
   }
   catch (Exception ex)
   {
      JOptionPane.showMessageDialog(this, ex.getMessage());
   }

6. That's all! Now you can run your application.

1.4. Quick Start Guide for VCL edition (Delphi)

1. Place the TacQueryBuilder component on the form.

2. Place required metadata and syntax provider components on the form. Define database connection for the metadata provider.

3. Link the components above to the TacQueryBuilder by setting MetadataProvider and SyntaxProvider properties

4. Place the TacSQLBuilderPlainText component on the form to get SQL code generated by the Query Builder with formatting. Link it to the TacQueryBuilder component by setting it's QueryBuilder property.

5. Add the TMemo or any other text editing component (for example, TSynEdit) to a form.

6. Now you should establish connection between the TacSQLBuilderPlainText and the TMemo components.
   Enter the following code to OnExit event of TMemo component:

   acQueryBuilder1.SQL := Memo1.Text;
   

    

   Enter the following code to OnSQLUpdated event of TacSQLBuilder component:

   Memo1.Text := acSQLBuilderPlainText1.SQL;
   

    

7. Execute the following code to load metadata and activate Active Query Builder:
   

   acQueryBuilder1.RefreshMetadata;
   

    

8. That's all! Now you can run your application.
   
   Don't forget to activate your database connection component.

1.5. Quick Start Guide for ActiveX edition

1. Place the ActiveQueryBuilderX control on the form.

2. Initialize the ActiveQueryBuilderX.ConnectionString property with the right ADO (OLE DB) or ODBC connection string and to turn the Connected property to True.

   You can find samples of connection strings for many popular database servers at ConnectionStrings.com. Please refer to the "OLE DB providers" sections for the samples of ADO (OLE DB) strings and the "ODBC drivers" sections for ODBC strings.

3. If you want to let your end-users to combine SQL query text editing with visual query building, add the TextBox or any other text editor control to a form. In case of using the TextBox control you should set its MultiLine property to True.

   Now you should establish connection between the ActiveQueryBuilderX and the TextBox controls.
   Enter the following code to Leave event of TextBox control:

   ActiveQueryBuilderX1.SQL = TextBox1.Text
   

   Enter the following code to OnSQLChanged event of ActiveQueryBuilderX control:

   TextBox.Text := ActiveQueryBuilderX1.FormattedSQL
   

4. That's all! Now you can run your application.

2.1. How to setup SQL text formatter?

To get the formatted SQL text, you should add the PlainTextSQLBuilder object and assign a QueryBuilder object to the PlainTextSQLBuilder.QueryBuilder property. After that you can handle the SQLUpdated event to get the formatted SQL text. The links below point to the lists of available properties to setup the formatting:

PlainTextSQLBuilder inherits BaseSQLBuilder which has separate sets of properties for Main query, expression sub-queries, derived tables and Common Table Expressions.

A set of formatting options for a single query is reprtesented by the SQLBuilderSelectFormat class.

2.2. What are the Syntax and Metadata providers for?

The QueryBuilder component has SyntaxProvider and MetadataProvider properties. They must be set to the instances of appropriate objects in order to work properly. This article describes the purpose of these objects.

Syntax Providers define the rules of SQL query parsing and generation for a specific database server. It also determines the queries to retrieve metadata from a specific database server using system tables. The component cannot work without a syntax provider. There are few generic syntax providers for different ANSI SQL standards that do not relate to any specific database server, thus they might be used with any database server that's compatible with the appropriate standard.

The AutoSyntaxProvider may be used if you don't know which database server the user will be connected to. This syntax provider will send a series of server specific queries to detect the underlying database server. After successful detection it will act as appropriate database server syntax provider; otherwise it will act as Universal syntax provider.

The UniversalSyntaxProvider is recommended to work with those database servers only for which Active Query Builder does not provide a special syntax provider. The Universal syntax provider supports only ANSI SQL/92 syntax and additionaly implements the way to retrieve metadata from all the supported database servers. It was originally made for the Free Edition of Active Query Builder which lacks support of specifiс database server SQL syntax extensions.

Metadata Providers are intended to link Active Query Builder to specific data access components. Their main task is to execute the metadata retrieval queries sent by the component. The Metadata provider does not create necessary database connectivity objects, but an instance of appropriate database connection object should be assigned to the Connection property of the a metadata provider.

The OLE DB, ODBC and some other metadata providers also can retrieve metadata information using the GetSchema or other methods of appropriate data access components. If Active Query Builder fails to load metadata using the server-specific queries, it tries to load this information by means of metadata provider.

The component can work without a metadata provider. In this case it will not be able to execute the metadata retrieval queries, thus this information must be loaded in some other ways and the QueryBuilder.OfflineMode property should be set to True to avoid run-time exceptions.

2.3. How to retrieve result SQL query text?

Since the retrieval of the resulting SQL query text is the main task of the component, it can be performed by different ways subject to the current situation and needs.

1. QueryBuilder component properties

   The QueryBuilder.SQL property determines the retrieval of the ordinary unformatted query text. To track any changes of this property you should use the QueryBuilder.SQLUpdated event. To disable the firing of this event during the programmatic change of the query, use BeginUpdate and EndUpdate methods.

   The SQL property also lets you load the query into the component. The component needs the SQL text only to load the query. Just assign the SQL text to the QueryBuilder.SQL property and the user will get its visual presentation on the screen.

   Still there's another QueryBuilder.LayoutSQL property in the component. It allows to save and to load the location of objects within the diagram along with the query text to save the layout between work sessions.

   If you need to analyze the query immediately after its assignment, for example, while loading it from the file, use the QueryBuilder.SyncSQL property. In this case the update of internal query representation objects and query statistics will occur immediately after the assignment.

   Some notes:

   • After the assignment of the query text to SQL and LayoutSQL properties, the update of the internal query representation objects won't occur immediately but asynchronously in the next message processing cycle. The SQLUpdated event will be fired after the update. This was made to allow the user work with both the SQL text and graphic query presentation simultaneously using the text editor visible at the same time on a form to avoid impacts during possible simultaneous programming and visual change of the query, but now these problems are gone. In the next major version all SQL assignment properties will work synchronously.
   • The SQLUpdated event won't occur if you try to load the query text identical to the query that is loaded into the component already. While assigning the query text to any properties described above, the component is trying to parse the query text. During the parsing process all elements irrelevant for the parser (spaces, line breaks and even comments) are ignored. And the so-called "abstract syntax tree" (AST) is being built from the query text. In general, AST is a set of tokens. After that this set is compared with the one loaded into the component before, and if they equal, the substitution of the old tree for the new one doesn't occur. Therefore the multiple assignment of the same syntactically correct query won't lead to the multiple firing of the SQLUpdated event, but the multiple assignment of the incorrect query will lead to the multiple firing of the SQLError event.

    

2. PlainTextSQLBuilder component properties

   The main task of the PlainTextSQLBuilder component is acquisition of the query text formatted in accordance with user-specified settings, though it has some other useful properties in the last versions of the component.

   To work with PlainTextSQLBuilder you need assign an instance of the query building component to the PlainTextSQLBuilder.QueryBuilder property. After that you'll be able to get the formatted query text from the PlainTextSQLBuilder.SQL property.

   In the general case you can use two and more PlainTextSQLBuilder components connected to one QueryBuilder component. It might be useful when you want to show the user the query text that is easier to read and understand, and when you want to use the unmodified variant of the same query for execution on the server. To ease understanding of queries by an end user you can use Alternate names and Virtual objects and fields. While using these features the control over the query text generation is performed with the help of UseAltNames, ExpandVirtualObjects and ExpandVirtualFields properties.

   The last version of the component lets change the sub-queries text independently of the main query. Surely, when we use visual builder, we may talk about altering of the active (currently visible) sub-query text. For this purpose the PlainTextSQLBuilder component has the PlainTextSQLBuilder.SQLUpdated event which is similar to the QueryBuilder.SQLUpdated event, but it is additionally fired on changing of the currently active sub-query when PlainTextSQLBuilder.TargetQueryPart property is set to SubQuery or UnionSubQuery value. In such a mode the assignment to the PlainTextSQLBuilder.SQL property will lead to the change of the currently active sub-query text only but not of the whole query. You may read about this feature in detail here.

2.4. What is the Sleep mode and how it helps to deal with unsupported SQL statements?

The Sleep mode is made to allow the end-users to write SQL statements that are not supported by Active Query Builder (DML, DDL) in the text editor.

This mode is enabled by default, but might be disabled by setting the QueryBuilder.AllowSleepMode property to False. Switching to this mode is done automatically on assigning a not supported SQL query to the query builder. During this assignment no parsing exceptions are thrown, but the QueryBuilder.SleepModeChanged event is fired.

Being in the Sleep Mode the component shows empty diagram pane and query columns grid. It may be activated by assigning the SELECT SQL statement to it or by starting to build a new query visually. On starting to build a new query visually being in the sleep mode, the QueryBuilder.QueryAwake event is fired. By handling this event you may ensure that the user really wants to start build a new query (this action will replace the previous non-supported SQL statement with a new one) and allow or deny switching to the normal mode.

2.5. Working with parameters

It is possible to include parameters in the following notations to the query:

= :param (named notation)

= ? (unnamed notation)

= @Param (for MS SQL only)

Active Query Builder does not execute SQL queries, but it provides all necessary information about parameters that were used in the query. You should examine the QueryBuilder.Parameters collection before query execution and display a dialog prompt for parameter values based on the information from this collection. An example of such usage is provided in the sample projects that included in the installation package.

Please note that some data access components may not support all of the mentioned parameter notations; please refer to the documentation to your data access components for details.

Please note that the Parameters collection is not available in the Free version. Use the Trial or Standard version to work with parameters.

2.6. Determination of unknown objects in the query

Important:

This article might be obsolete!

It is valid for Java, ActiveX, VCL editions, and for previous version of Active Query Builder .NET Edition (1.x).

The appropriate article for Active Query Builder 2 .NET Editon can be found here: How to detect incorrect object and field names in the query?.

Active Query Builder can parse queries with objects not loaded in the Metadata Container. To determine if query contains unknown objects, you may examine the QueryBuilder.QueryStatistics.UsedDatabaseObjects collection. Those items of this collection that have a null reference in the MetadataObject property were failed to find a corresponding object in the Metadata Container. The same way you may check for unknown fields using the QueryBuilder.QueryStatistics.UsedDatabaseObjectColumns collection and checking for a null reference in the MetadataField property.

For some reasons you may deny parsing of such queries by setting the QueryBuilder.ParsingErrorOnUnknownObjects property to True.

2.7. Validating SQL query (syntax analysis only)

To validate a query against specific SQL syntax with Active Query Builder .NET Edition, use the the ValidateQuery() method of appropriate syntax provider:

MSSQLSyntaxProvider syntax = new MSSQLSyntaxProvider();
string errorInfo = "";

if (!syntax.ValidateQuery("select * from Orders", ref errorInfo))
{
   MessageBox.Show(errorInfo);
}

Having VCL or ActiveX Edition of Active Query Builder, you may assign SQL query text to the QueryBuilder.SyncSQL property in a try-except (try-catch) block.

try
  acQueryBuilder.SQL:='select * from Orders';
except
  on e:EacSQLError do
    ShowMessage('Unexpected token "'+e.ErrorTokenValue+'" in line '+IntToStr(e.ErrorPos.line);
end;

Please note that Active Query Builder does not perform validation against specific database context, i.e. presence of table fields, function parameters, constants, etc. We believe that the best and only possible validator is the database server itself.

The fastest way to do that is to execute the query without data retrieval by calling the following method:

SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly).

Of course, you can check if such database objects and fields are present in the database (using the QueryBuilder.QueryStatistics set of collections), but still there are many things we can recognize if a query is correct in terms of specific database or not.

You may instruct the component to generate parsing error for queries with unknown database objects using the QueryBuilder.ParsingErrorOnUnknownObjects property

Active Query Builder can parse SELECT statements only. For the rest of statements, it switches to the Sleep Mode. You may deny switching to this mode by setting the QueryBuilder.AllowSleepMode to false. Also you may set the event handler for the SleepModeChanged to perform necessary actions.

2.8. Database server autodetection (SQL syntax)

Use the AutoSyntaxProvider to detect database syntax automatically.

By setting this object as syntax provider for a query builder object, database syntax will be detected on retrieving metadata from your database (using RefreshMetadata method).

You do not have to change syntax provider after detection. AutoSyntaxProvider will act as detected syntax provider. You may know the result of database server detection by examining the DetectedSyntaxProvider property.

To detect database syntax without metadata retrieval, use the following code:

autoSyntaxProvider1.DetectSyntaxProvider(queryBuilder1.SQLContext);

Auto syntax provider detects database server using live database connection, by sending server-specific queries and analyzing result. It can not detect database syntax by analyzing arbitrary SQL queries.

For the ActiveX Edition, use the "SQLSyntax = xsAuto" setting and examine the UsedSQLSyntax property.

3.1. Metadata pre-filtration

Important:

This article might be obsolete!

It is valid for ASP.NET, Java, ActiveX, VCL editions, and for previous version of Active Query Builder WinForms .NET Edition (1.x).

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

Using metadata filters you may 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 may 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.

3.2. Loading metadata from several databases

Important:

This article might be obsolete!

It is valid for ASP.NET, Java, ActiveX, VCL editions, and for previous version of Active Query Builder WinForms .NET Edition (1.x).

The appropriate article for Active Query Builder 2 .NET Editon can be found here: Instructing to show metadata from several databases or servers.

Active Query Builder is intended to build, parse and analyze SQL queries, but not to execute them in any way. This means that you may 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 may save all metadata to the XML file and use it afterwards.

---

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

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

3.3. Working with Metadata Container

Important:

This article might be obsolete!

It is valid for ASP.NET, Java, ActiveX, VCL editions, and for previous version of Active Query Builder WinForms .NET Edition (1.x).

The appropriate article for Active Query Builder 2 .NET Editon can be found here: Working with the Metadata Container.

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 may 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 may 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 may not call the RefreshMetadata method at all, but fill the Metadata Container by youself:

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.

3.4. Loading metadata programmatically

Important:

This article might be obsolete!

It is valid for ASP.NET, Java, ActiveX, VCL editions, and for previous version of Active Query Builder WinForms .NET Edition (1.x).

The appropriate article for Active Query Builder 2 .NET Editon can be found here: Adding objects to the Metadata Container programmatically.

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 may 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"));
          }
   }
   

3.5. Loading metadata from XML file

Important:

This article might be obsolete!

It is valid for ASP.NET, Java, ActiveX, VCL editions, and for previous version of Active Query Builder WinForms .NET Edition (1.x).

The appropriate article for Active Query Builder 2 .NET Editon can be found here: Basic principles of working with metadata.

You may pre-create the XML file that stores all necessary metadata information and load it on start of the Query Builder. This could be the unique solution when database connection is not available at the client side (in web projects using ActiveX or Java Edition). This feature may also 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 may:

• Use the standalone Metadata Editor tool at http://www.activequerybuilder.com/files/metadata_editor.zip
• Use the same tool included in the component by calling the MetadataContainer.Edit method.
• Create the file manually by following the rules of XSD Schema available at http://www.activequerybuilder.com/schemas/metadata.xsd
• Use the MetadataContainer.SaveToXMLFile method to create this file programmatically.

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;

4.1. Alternate database object and field names

The Alternate Names feature for both database objects and their fields lets completely substitute real names of objects for clearer descriptive names.  So it's possible to simplify understanding of database structure and consequently building of queries to end users. 'Complete substitution' means substitution of objects names in both user interface, SQL expressions and in the query text which can be edited manually by end user along with visual query building. Active Query Builder lets assign an alternate name to every object and field of the database object loaded to the metadata container. On parsing a query, the component is able to recognize both real and alternate names of fields. Therefore this component is able to build visual presentation of a query with both types of names. This allows represent to an end user clearer presentation of existing queries.

Generation of the query text may be made using both real and alternative names; the UseAltNames property of PlainTextSQLBuilder component determines what names to use. Whereas several PlainTextSQLBuilder components may be connected to one QueryBuilder component, you may get two variants of query text simultaneously: one for an end user and another for executing query on the server. The same named property of the QueryBuilder component is responsible for displaying of real or alternate names in user interface.

Some customers even use this peculiarity for refactoring in database structure running the suite of existing queries through the component and getting as a result queries with new names of fields and objects.

It is possible to assign alternate names in several ways.

1. Save metadata into XML file, add alternate names into it (manually using the "alt_name" tag for fields and objects or with the help of the metadata editor) and then load metadata from this file instead of retrieving metadata from the connection with database.

2. Use the QueryBuilder.MetadataContainer MetadataObjectLoaded and ObjectMetadataLoaded events to assign alternate names to objects and their fields (respectively) during their loading to the metadata container. For example, you may use descriptions of the objects being loaded by the component from database as alternate names.

4.2. Virtual objects and fields

Virtual fields and objects are the feature letting simplify building of queries with the use of frequently used SQL expressions, derived tables and correlated sub queries. The idea is to substitute derived tables for virtual objects which won't differ from ordinary database objects for the end user. Substitution of such objects for derived tables is performed seamlessly for the end user same way as alternate names.

For example, if you have a possibility to save queries for further use in your software, you may add them into the metadata container as virtual objects therefore having provided their repeated use in other queries.

In the same way complex SQL expressions and correlated sub queries may be shown to the end-user as usual fields names. For example, we may add virtual field "Customer" into the Orders table having assigned the expression "(SELECT c.Customer_Name FROM Customers c WHERE c.Customer_Id = Orders.Customer_Id)" for it. Then this expression will be correctly converted and substituted into the final query for execution on the database server.

To get the query text for execution on the database server it's necessary to make an additional PlainTextSQLBuilder component, having set its properties ExpandVirtualFields and ExpandVirtualObjects to True. As opposed to alternate names, where real names in the query may be converted into alternate ones and inversely, inverse conversion of SQL expressions into virtual objects and fields is impossible.

You may add virtual objects and fields to the metadata container in the same way as usual objects and fields, additionally having assigned SQL expression (being substituted by the given field or object) to the Expression property.

4.3. Modification of sub-queries independently of the main query

Now it is possible to modify the sub-query SQL text independently of the main query. The UnionSubQuery.SQL and SubQuery.SQL properties can be used to read and write the sub-query SQL text.

In addition to this feature we've made the option to filter the sub-query tabs in visual user interface in order to show only the sub-queries tabs that were used in the currently active sub-query. Another option is to let the PlainTextSQLBuilder retrieve the formatted text of the currently active sub-query. These options can be used independently, but supplement each other very well. Using both options the user will be able to view and edit the currently active sub-query as an independent query and easily get back to the main query when the editing is finished. Working in the sub-query editing mode, the programmer should assign the user edited query text to the PlainTextSQLBuilder.SQL property as it's always mapped to the active sub-query in this mode. And use the PlainTextSQLBuilder.SQLUpdated event to update the text editor as this event is fired not only when the query is changed in the Query Builder but also when the active sub-query is changed.

There are two variants of sub-query editing mode: editing single union sub-queries or editing sets of sub-queries with unions. I.e. if a sub-query contains unions, the user will edit each union sub-query independently in the first case and the whole set of union sub-queries in the second case. Thus the possible values of the PlainTextSQLBuilder.TargetQueryPart property that turn on this mode are the following:

• Query (default) – instructs to generate SQL text for the whole query;
• SubQuery – instructs to generate SQL text for the currently active sub-query with unions;
• UnionSubQuery – instructs to generate SQL text for the currently active union sub-query.

The QueryBuilder.FilterTabsByActiveQueryPart property with the same possible values instructs the component to filter sub-query tabs accordingly.

The "Show Query By Parts" demo project illustrates this functionality.

5.1. Retrieving common information about the query

Database objects usage and other information about the currently loaded SQL query is instantly available through the QueryBuilder.QueryStatistics set of collections.

1. Used database objects: QueryBuilder.QueryStatistics.UsedDatabaseObjects collection.

   Each item of this collection has link to the MetadataObject object to retrieve detailed information about this database object. Please note that this link might be set to null if such object can not be found in the Metadata Container.

2. Used database object fields: QueryBuilder.QueryStatistics.UsedDatabaseObjectColumns collection.

   Each item of this collection has name, type of the field. It also has links to the MetadataField and MetadataObject objects to retrieve detailed information about this field and the database object to which it belongs to. The Selected property indicates if this field is used in some of the output columns in the query.

   Please note that this collection does not contain fields that were used in the query without table alias prefix. This because it is impossible to uniquely determine correspondence of this field to a table.

3. Output query columns: QueryBuilder.QueryStatistics.OutputColumns collection.

   This collection lists output columns of the query. Each item of this collection has the Expression and ExpressionAlias properties to retrieve SQL expression and it's alias (if defined). If SQL expression is a simple database object field, additional information for it is available: field type, object name, links to MetadataField and MetadataObject objects, etc.

5.2. Understanding complex SQL query structure representation

Each query and sub-query in the query is represented with the SubQuery object in Active Query Builder; The main query representation class Query is descendant of the SubQuery class. The main query can be accessed via the QueryBuilder.Query property. To get access to the list of sub-queries, use the QueryBuilder.SubQueries collection.

Each sub-query may not be a single "SELECT ... FROM ..." statement, but a set of statements joined with the UNION, EXCEPT, INTERSECT and other keywords. Each single "SELECT ... FROM ..." statement is represented via the UnionSubQuery object.

There are few useful helpers you may use in your project:

• The QueryBuilder.Query.FisrtSelect() method returns the link to an object representing the first single SELECT statement in the query.

• The QueryBuilder.ActiveSubQuery.ActiveUnionSubQuery property gets the single SELECT statement currently visible to the end-user. The QueryBuilder.ActiveUnionSubQueryChanged event is fired when user switches to another statement.

The UnionSubQuery object holds datasources, their links (UnionSubQuery.FromClause), expressions and criteria (UnionSubQuery.CriteriaList) of the single SELECT statement. To get list of all datasources, use the UnionSubQuery.FromClause.GetDatasources() method. The CriteriaList collection is organized the same way as you see it in the Query Columns Grid: each item of this collection (CriteriaItem) represents a single row of this grid; columns are accessible via the corresponding properties.

5.3. Enumeration of sub-queries in a complex query

Below are the fragments of the QueryStructureDemo project that included in the installation package. You may review it for more details.

1. Enumeration of sub-queries in a query.

Use the QueryBuilder.SubQueries and the QueryBuilder.SubQueryCount properties to list all sub-queries in the query.

public void DumpSubQueriesInfo(StringBuilder stringBuilder, QueryBuilder queryBuilder)
{
	for (int i = 0; i < queryBuilder.SubQueryCount; i++)
	{
		if (stringBuilder.Length > 0)
		{
			stringBuilder.AppendLine();
		}

		DumpSubQueryInfo(stringBuilder, i, queryBuilder.SubQueries[i]);
	}
}

private void DumpSubQueryInfo(StringBuilder stringBuilder, int index, SubQuery subQuery)
{
    string sql = subQuery.GetResultSQL();

    stringBuilder.AppendLine(index.ToString() + ": " + sql);
}

2. Enmeration of union queries in a sub-query.

Union sub-query groups might be enlosed in parentheses, thus it's not a simple union sub-query list, but a list of single union sub-queries and union sub-query groups organized in a tree-like structure.

public void DumpQueryStructureInfo(StringBuilder stringBuilder, SubQuery subQuery)
{
    DumpUnionGroupInfo(stringBuilder, "", subQuery);
}
        
private void DumpUnionGroupInfo(StringBuilder stringBuilder, string indent, UnionGroup unionGroup)
{
	QueryBase[] children = GetUnionChildren(unionGroup);

	foreach (QueryBase child in children)
	{
		if (stringBuilder.Length > 0)
		{
			stringBuilder.AppendLine();
		}

		if (child is UnionSubQuery)
		{
			// UnionSubQuery is a leaf node of query structure.
			// It represent a single SELECT statement in the tree of unions
			DumpUnionSubQueryInfo(stringBuilder, indent, (UnionSubQuery) child);
		}
		else
		{
			// UnionGroup is a tree node containing one or more child nodes.
			// It represents a root of the subquery in the union tree or 
			// a group of sub-queries enclosed in parentheses in the union tree.
			unionGroup = (UnionGroup) child;

			stringBuilder.AppendLine(indent + unionGroup.UnionOperatorFull + "group: [");
			DumpUnionGroupInfo(stringBuilder, indent + "    ", unionGroup);
			stringBuilder.AppendLine(indent + "]");
		}
	}
}

private void DumpUnionSubQueryInfo(StringBuilder stringBuilder, string indent, UnionSubQuery unionSubQuery)
{
	string sql = unionSubQuery.GetResultSQL();

	stringBuilder.AppendLine(indent + unionSubQuery.UnionOperatorFull + ": " + sql);
}

private QueryBase[] GetUnionChildren(UnionGroup unionGroup)
{
	ArrayList result = new ArrayList();

	for (int i = 0; i < unionGroup.Count; i++)
	{
		result.Add(unionGroup[i]);
	}

	return (QueryBase[]) result.ToArray(typeof(QueryBase));
}

5.4. Enumeration of data sources and joins in a sub-query

The UnionSubQuery.FromClause object holds the list of SELECT statement datasources and their links.

Below are the fragments of the QueryStructureDemo project that included in the installation package. You may review it for more details.

1. Enumeration of all datasources in a single SELECT statement.

You may get plain list of datasources using the GetDatasourceByClass() method. There are DataSourceObject and DataSourceQuery classes representing database objects and derived tables correspondingly. The DataSourceQuery has the Query property to get access to SubQuery object representing this derived table. The DataSorceObject has MetadataObject property to get access to the database object details.

public void DumpDataSourcesInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
    DumpDataSourcesInfo(stringBuilder, GetDataSourceList(unionSubQuery));
}

private void DumpDataSourcesInfo(StringBuilder stringBuilder, ArrayList dataSources)
{
    for (int i = 0; i < dataSources.Count; i++)
    {
        if (stringBuilder.Length > 0)
        {
            stringBuilder.AppendLine();
        }

        DumpDataSourceInfo(stringBuilder, (DataSource)dataSources[i]);
    }
}

private void DumpDataSourceInfo(StringBuilder stringBuilder, DataSource dataSource)
{
	// write full sql fragment
	stringBuilder.AppendLine(dataSource.GetResultSQL());

	// write alias
	stringBuilder.AppendLine("  alias: " + dataSource.Alias);

	// write referenced MetadataObject (if any)
	if (dataSource.MetadataObject != null)
	{
		stringBuilder.AppendLine("  ref: " + dataSource.MetadataObject.FullNameStr);
	}

	// write subquery (if datasource is actually a derived table)
	if (dataSource is DataSourceQuery)
	{
		stringBuilder.AppendLine("  subquery sql: " + ((DataSourceQuery) dataSource).Query.GetResultSQL());
	}

	// write fields
	string fields = String.Empty;

	for (int i = 0; i < dataSource.Fields.Count; i++)
	{
		if (fields.Length > 0)
		{
			fields += ", ";
		}

		fields += dataSource.Fields[i].NameStr;
	}

	stringBuilder.AppendLine("  fields (" + dataSource.Fields.Count.ToString() + "): " + fields);
}

private ArrayList GetDataSourceList(UnionSubQuery unionSubQuery)
{
    ArrayList list = new ArrayList();

    unionSubQuery.FromClause.GetDatasourceByClass(typeof(DataSource), list);

    return list;
}

2. Enumeration of all links in a single SELECT statement.

You may get the list of links using the GetLinksRecursive() method. Each Link object has references to it's left and right datasources (LeftDatasource, RightDatasource), linked fields (LeftField, RightField) and has properties to read and change it's join type (LeftType, RightType). Link expression is available trough the LinkExpression property.

public void DumpLinksInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
    DumpLinksInfo(stringBuilder, GetLinkList(unionSubQuery));
}
        
private void DumpLinksInfo(StringBuilder stringBuilder, ArrayList links)
{
	for (int i = 0; i < links.Count; i++)
	{
		if (stringBuilder.Length > 0)
		{
			stringBuilder.AppendLine();
		}

		DumpLinkInfo(stringBuilder, (Link) links[i]);
	}
}

private void DumpLinkInfo(StringBuilder stringBuilder, Link link)
{
    // write full sql fragment of link expression
    stringBuilder.AppendLine(link.LinkExpression.SimpleSQL(link.SQLContext.SQLBuilderExpression));

    // write information about left side of link
    stringBuilder.AppendLine("  left datasource: " + link.LeftDatasource.GetResultSQL());

    if (link.LeftType == LinkSideType.Inner)
    {
        stringBuilder.AppendLine("  left type: Inner");
    }
    else
    {
        stringBuilder.AppendLine("  left type: Outer");
    }

    // write information about right side of link
    stringBuilder.AppendLine("  right datasource: " + link.RightDatasource.GetResultSQL());

    if (link.RightType == LinkSideType.Inner)
    {
        stringBuilder.AppendLine("  lerightft type: Inner");
    }
    else
    {
        stringBuilder.AppendLine("  right type: Outer");
    }
}

private ArrayList GetLinkList(UnionSubQuery unionSubQuery)
{
    ArrayList links = new ArrayList();

    unionSubQuery.FromClause.GetLinksRecursive(links);

    return links;
}

5.5. Enumeration of output columns in a sub-query

The UnionSubQuery.CriteriaList collection contains information about SQL query expressions, their properties and criteria. Each element of this collection has all the necessary properties to read and modify text of expression, it's alias, ordering, grouping, aggregate function and criteria for this expression. The CriteriaItem.Select property determines if expression is listed in the SELECT list of output expressions.

The ExpressionField and ExpressionDatasource properties refer to the MetadataField and Datasource objects if expression is a single database object field.

Below are the fragments of the QueryStructureDemo project that included in the installation package. You may review it for more details.

public void DumpSelectedExpressionsInfoFromUnionSubQuery(StringBuilder stringBuilder, UnionSubQuery unionSubQuery)
{
    // get list of CriteriaItems
    CriteriaList criteriaList = unionSubQuery.CriteriaList;

    // dump all items
    for (int i = 0; i < criteriaList.Count; i++)
    {
        CriteriaItem criteriaItem = criteriaList[i];

        // only items with the Select property set to True go to SELECT list
        if (!criteriaItem.Select)
        {
            continue;
        }

        DumpSelectedExpressionInfo(stringBuilder, criteriaItem);
    }
}
        
private void DumpSelectedExpressionInfo(StringBuilder stringBuilder, CriteriaItem selectedExpression)
{
	// write full sql fragment of selected expression
	stringBuilder.AppendLine(selectedExpression.ExpressionString);

	// write alias
	if (!String.IsNullOrEmpty(selectedExpression.AliasString))
	{
		stringBuilder.AppendLine("  alias: " + selectedExpression.AliasString);
	}

	// write datasource reference (if any)
	if (selectedExpression.ExpressionDatasource != null)
	{
		stringBuilder.AppendLine("  datasource: " + selectedExpression.ExpressionDatasource.GetResultSQL());
	}

	// write metadata information (if any)
	if (selectedExpression.ExpressionField != null)
	{
		MetadataField field = selectedExpression.ExpressionField;
		stringBuilder.AppendLine("  field name: " + field.NameStr);

		string s = Enum.GetName(typeof(DbType), field.FieldType);
		stringBuilder.AppendLine("  field type: " + s);
	}
}

6.1. How to hide the "Main" tab?

Using the .NET Edition with standard WinForms controls, you may hide the tabs with the following code:

TabControl tc = (TabControl) queryBuilder1.Tabs.GetControl();
tc.ItemSize = new Size(1,1);
tc.Top = tc.Top - tc.ItemSize.Height;
tc.Height = tc.Height + tc.ItemSize.Height;
tc.Region = new Region(new RectangleF(tc.TabPages[0].Left, tc.TabPages[0].Top, tc.TabPages[0].Width, tc.TabPages[0].Height + tc.ItemSize.Height));

Another way is to make a tab very wide and set its height to 1:

tc.Multiline = false;
 tc.SizeMode = TabSizeMode.Fixed;
 tc.ItemSize = new Size(2000, 1);

It works but not looking perfect. That's all we can do. Unfortunately the WinForms TabControl has no standard way to hide tabs.

Using the .NET Edition with DevExpress controls, use the following code:

XtraTabControl tc = (XtraTabControl) queryBuilder1.Tabs.GetControl();
tc.ShowTabHeader = DefaultBoolean.False;

6.2. How to modify context menus?

To modify the component's context popup menus you need to handle the ValidateContextMenu event. The following example demonstrates adding of a custom menu item in the context menu of data source object.

using ActiveDatabaseSoftware.ActiveQueryBuilder;

public partial class Form1 : Form
{

	public Form1()
	{
		InitializeComponent();
		this.queryBuilder1.ValidateContextMenu += new PopupMenuEventHandler(this.ValidateContextMenu);
	}

	private void ValidateContextMenu(Control control, ControlOwner owner, ContextMenu menu)
	{
		// check the menu is from the datasource object
		if (control is DataSourceControl && owner is DataSourceObject)
		{
			// Here you can freely modify the menu adding or removing menu items.
			MenuItem mi = menu.MenuItems.Add("Custom Menu Item", new EventHandler(MenuItem_Click));
			mi.Tag = owner;
		}
	}

	void MenuItem_Click(object sender, EventArgs e)
	{
		DataSourceObject dso = (DataSourceObject)((MenuItem)sender).Tag;
		if (dso.MetadataObject != null)
		{
			MessageBox.Show(dso.MetadataObject.FullNameStr);
		}
	}
}

The controlOwner parameter refers to the query object for which the context menu is called. The control parameter refers to the control representing this query object. The following combinations are possible:

• controlOwner is Link; control is LinkControl.
• controlOwner is DataSourceObject or DataSourceQuery; control is DataSourceControl.
• controlOwner is CriteriaItem; control is CriteriaListControl.
• controlOwner is UnionSubQuery; control is DiagramPane: diagram pane context menu.
• controlOwner is UnionSubQuery; control is UnionButton: context menu of the union button ("Q" button on the navigation bar).
• controlOwner is UnionSubQuery; control is UnionOperator: context menu of the union operator (union type symbol on the navigation bar).
• controlOwner is UnionGroup; control is LeftBracket: context menu of the left bracket ("(" symbol on the navigation bar).
• controlOwner is UnionGroup; control is RightBracket: context menu of the right bracket (")" symbol on the navigation bar).

6.3. How to implement custom drawing of the fields inside datasources?

The following code displays field description if it's not empty; otherwise it displays field name.

void queryBuilder1_FieldListDrawItem(Graphics g, Rectangle rect, DrawItemState state, MetadataField field, ref bool handled)
{
       Brush bgBrush;
       Color textColor;
       String s = String.Empty;

       if ((state & DrawItemState.Selected) == DrawItemState.Selected)
       {
               bgBrush = SystemBrushes.Highlight;
               textColor = SystemColors.HighlightText;
       }
       else
       {
               bgBrush = new SolidBrush(Color.LightYellow);
               textColor = Color.MediumSeaGreen;
       }

       rect.X -= rect.Height;
       rect.Width += rect.Height;

       g.FillRectangle(bgBrush, rect);

       if (field != null)
       {
               if (!String.IsNullOrEmpty(field.AltName) && queryBuilder1.UseAltNames)
               {
                       s = field.AltName;
               }
               else
               {
                       s = field.Name.QualifiedNameWithoutQuotes;
               }
       }
       else
       {
               s = "*";
       }

       TextRenderer.DrawText(g, s, new Font("Arial", 8, FontStyle.Bold), rect.Location, textColor);

       if ((state & DrawItemState.Focus) == DrawItemState.Focus)
       {
               ControlPaint.DrawFocusRectangle(g, rect);
       }
       handled = true;
}

6.4. How to implement custom sorting of the fields inside datasources?

The following code sorts fields by description, but places fields with empty descriptions to the bottom.

private void queryBuilder1_CustomizeDatasourceFieldList(QueryBuilder queryBuilder, DataSource datasource, MetadataFieldList fieldList)
{
       fieldList.Sort(new SortFieldsByDescriptionComparer());
}

private class SortFieldsByDescriptionComparer: IComparer<MetadataCollectionItemBase>
{
       int IComparer<MetadataCollectionItemBase>.Compare(MetadataCollectionItemBase qn1, MetadataCollectionItemBase qn2)
       {
               MetadataField f1 = (MetadataField) qn1;
               MetadataField f2 = (MetadataField) qn2;

               int result = String.Compare(f1.ShortDescription, f2.ShortDescription, true);

               if (result == 0)
               {
                       result = String.Compare(f1.AltName, f2.AltName, true);

                       if (result == 0)
                       {
                               result = String.Compare(f1.NameStr, f2.NameStr, true);
                       }
               }
               return result;
       }
}

6.5. How to drag an object from the metadata tree outside the component?

Delphi (VCL Edition):

procedure TForm2.Memo1DragDrop(Sender, Source: TObject; X, Y: Integer);
var n:TacTreeNode;
        mo:TacMetadataObject;
begin
    Assert(Source=qb.MetadataTree);
    Assert(qb.MetadataTree.Selected<>nil);

    n:=qb.MetadataTree.Selected;
    mo:=TacMetadataObject(n.Data);
    Memo1.Lines.Add(mo.FullNameStr);
end;


procedure TForm2.Memo1DragOver(Sender, Source: TObject; X, Y: Integer;
    State: TDragState; var Accept: Boolean);
begin
    Accept:=false;
    if (Source=qb.MetadataTree)and
        (qb.MetadataTree.Selected<>nil)
    then Accept:=true;
end;

C# (.NET Edition):

private void listBox1_DragOver(object sender, DragEventArgs e)
{
    if (e.Data.GetDataPresent(typeof(DataSourceFieldList)))
    {
        e.Effect = DragDropEffects.Link;
    }
}

private void listBox1_DragDrop(object sender, DragEventArgs e)
{
    if (e.Data.GetDataPresent(typeof(DataSourceFieldList)))
    {
        e.Effect = DragDropEffects.Link;

	DataSourceFieldList fieldListControl = (DataSourceFieldList) e.Data.GetData(typeof(DataSourceFieldList));

        if (fieldListControl.SelectedItem != null)
        {
            if (((TextAndObject) fieldListControl.SelectedItem).obj != null)
            {
                MetadataField field = (MetadataField) ((TextAndObject) fieldListControl.SelectedItem).obj;

                int x = listBox1.Items.Add(field.NameStr + " [" + field.FieldTypeName + "]");

                listBox1.SelectedIndex = x;
            }
        }
    }
}

Java:

jTextArea1.setDropTarget(new DropTarget(this, new DropTargetAdapter()
{
    @Override
    public void dragOver(DropTargetDragEvent event)
    {
        if (!event.getTransferable().isDataFlavorSupported(DataFlavor.stringFlavor))
        {
            event.rejectDrag();
        }
    }

    @Override
    public void drop(DropTargetDropEvent event)
    {
        event.acceptDrop(DnDConstants.ACTION_LINK);

        Transferable transferable = event.getTransferable();

        if (transferable.isDataFlavorSupported(DataFlavor.stringFlavor))
        {
            try
            {
                String data = (String) transferable.getTransferData(DataFlavor.stringFlavor);
                jTextArea1.setText(data);
            }
            catch (UnsupportedFlavorException ex)
            {
                // handle exception
            }
            catch (IOException ex)
            {
                // handle exception
            }
        }
    }
}));

6.6. How add an object to the query building area by dragging from external list?

Delphi (VCL Edition):

procedure TfMain.acQueryBuilder1DragDrop(Sender, Source: TObject; 
    X, Y: Integer);
    var selectedNodeName: string;
begin
    Assert((Source = CustomMetadataTree) and (CustomMetadataTree.Selected <> nil));
    selectedNodeName := CustomMetadataTree.Selected.Text;
    acQueryBuilder1.ActiveSubQuery.ActiveUnionSubquery.AddObjectWithFKAt(
        selectedNodeName,
        Point(X, Y)
    );
end;

procedure TfMain.acQueryBuilder1DragOver(Sender, Source: TObject;
    X, Y: Integer; State: TDragState; var Accept: Boolean);
begin
    Accept := (Source = CustomMetadataTree) and (CustomMetadataTree.Selected <> nil);
end;

C# (.NET Edition):

private void queryBuilder1_DragOver(object sender, DragEventArgs e)
{
    e.Effect = DragDropEffects.Copy;
}


private void queryBuilder1_DragDrop(object sender, DragEventArgs e)
{
    if (e.Data != null && e.Data.GetDataPresent(typeof(String)))
    {
        String objectName = (String) e.Data.GetData(typeof(String));
        queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.AddObjectWithFKAt(objectName, new Point(e.X, e.Y), "");
    }
}

Java:

queryBuilder1.setDropTarget(new DropTarget(this, new DropTargetAdapter()
{
    @Override
    public void dragOver(DropTargetDragEvent event)
    {
        if (!event.getTransferable().isDataFlavorSupported(DataFlavor.stringFlavor))
        {
            event.rejectDrag();
        }
    }

    @Override
    public void drop(DropTargetDropEvent event)
    {
        event.acceptDrop(DnDConstants.ACTION_LINK);

        Transferable transferable = event.getTransferable();

        if (transferable.isDataFlavorSupported(DataFlavor.stringFlavor))
        {
            try
            {
                String objectName = (String) transferable.getTransferData(DataFlavor.stringFlavor);
                queryBuilder1.getActiveSubquery().getActiveUnionSubquery().addObjectWithFKAt(objectName, event.getLocation(), "");
            }
            catch (UnsupportedFlavorException ex)
            {
                // handle exception
            }
            catch (IOException ex)
            {
                // handle exception
            }
        }
    }
}));

7.1. How to add object to the query programmatically

There are four methods that can help you with this:

• QueryBuilder.ActiveSubQuery.ActiveUnionSubQuery.AddObject
• QueryBuilder.ActiveSubQuery.ActiveUnionSubQuery.AddObjectAt
• QueryBuilder.ActiveSubQuery.ActiveUnionSubQuery.AddObjectWithFK
• QueryBuilder.ActiveSubQuery.ActiveUnionSubQuery.AddObjectWithFKAt

Methods with the "At" should be used when dragging objects to the Design Pane as they allow to specify position to add object at. Methods with the "WithFK" suffix instruct the component to add joins to the newly added object from other objects that may exist in the active sub-query (according to the foreign keys or by identical field names (CreateLinksByIdenticalFieldNames, CreateLinksFromForeignKeys).

7.2. How to change sorting, add filters, limits and aggregations to the query?

There's a special component intended to change SQL queries according to the end-user data browsing needs: QueryTransformer. It was made to meet the needs of programmers who want to let the end-users to transfigure data while browsing query result set in the grid. The component lets change data sorting and filtration while browsing the query result data in the grid, apply limits to the result set, and calcluate totals by columns.

It needs to be linked to the QueryBuilder and SQLBuilder objects to get the initial query columns and produce the modified query text in result.

Follow the simple steps below to change the query the way you need:

1. Find the column you need in the QueryTransformer.Columns collection:

       column = queryTransformer.Columns.ColumnByName("OrderDate")

2. Create an object that defines the necessary modification:

       sorting = column.Ascending()
       filter = column.NotLess("31/12/2012")
       aggregate = column.Max("DateMax")

3. Apply these objects to the query:

       queryTransformer.OrderBy(sorting)
       queryTransformer.Where(filter)
       queryTransformer.Select(aggregate)

4. Apply limits to the query:

       If queryTransformer.IsSupportLimitCount() Then
           queryTransformer.Take("50")
       End If

5. Get the modified query from the QueryTransformer.SQL property:

       MessageBox.Show(queryTransformer.SQL)

Read the QueryTransformer and accompanying classes reference for details.

Below is the object relationship diagram and code samples for different languages.

• Delphi code sample (VCL Edition)
• C# code sample (.NET Edition)
• VB.NET code sample (.NET Edition)
• Visual Basic (ActiveX Edition)
• C# code sample (ActiveX Edition)

Delphi, VCL Edition

queryTransform.BeginUpdate;
try

       // filter
       queryTransform.Filters.Clear; // gets back the WHERE clause to initial state 

       queryTransform //adds conditions to the WHERE clause, conjunctioning it with conditions from initial query
	       .Where(queryTransform.Columns[1].Not_Equal('100'))
	       .Where(queryTransform.Columns[2].In_('(''Value1'',''Value2'',''Value3'')'))
	       .Where(queryTransform.Columns.ColumnByName('OrderId').Condition('> 100'))
	       .Where('OrderDate is not null')
	       .Where(TacFilterFactory.Or_()
		       .Add(queryTransform.Columns[1].Not_Equal('100'))
		       .Add(queryTransform.Columns[1].Not_Equal('200')));

       // ordering
       queryTransform.Sortings.Clear; // gets back the ORDER BY clause to initial state 

       queryTransform // removes the ORDER BY clause of initial query and defines alternate ordering for a query
	       .OrderBy(queryTransform.Columns[1])
	       .OrderBy(queryTransform.Columns[2], false)
	       .OrderBy(queryTransform.Columns.ColumnByName('OrderId'))
	       .OrderBy('OrderDate');

       // offset
       queryTransform // applies the offset clauses to the query according to specific database server SQL syntax
	       .Skip('100')
	       .Take('50');

	// aggregations
	queryTransform.Aggregations.Clear;  // removes wrapping of the query in a sub-query

	queryTransform  // wraps the query in a sub-query and defines output columns for the outer query
	.SelectRecordsCount('recordsCount')
	.Select(queryTransform.Columns[1].Count, 'column1Count')
	.Select(queryTransform.Columns[1].Sum, 'column1Sum')
	.Select(queryTransform.Columns[1].Avg, 'column1Avg')
	.Select(queryTransform.Columns[1].Min, 'column1Min')
	.Select(queryTransform.Columns[1].Max, 'column1Max')
	;

finally
       queryTransform.EndUpdate;
end;

C#, .NET Edition

using (QueryTransformer queryTransformer = new QueryTransformer())
{
    queryTransformer.QueryBuilder = queryBuilder1;
    queryTransformer.SqlBuilder = plainTextSQLBuilder1;
 
    queryTransformer.BeginUpdate();
 
    try
    {
        // filter
        queryTransformer.Filters.Clear();
 
        queryTransformer
            .Where(queryTransformer.Columns[1].Not_Equal("100"))
            .Where(queryTransformer.Columns[2].In("('Value 1','Value 2','Value 3')"))
            .Where(queryTransformer.Columns.GetColumnByName("OrderId").Condition("> 100"))
            .Where("OrderDate is not null")
            .Where(FilterFactory.Or()
                .Add(queryTransformer.Columns[1].Not_Equal("100"))
                .Add(queryTransformer.Columns[1].Not_Equal("200")));
 
        // ordering
        queryTransformer.Sortings.Clear();
 
        queryTransformer
            .OrderBy(queryTransformer.Columns[1])
            .OrderBy(queryTransformer.Columns[2], false)
            .OrderBy(queryTransformer.Columns.GetColumnByName("OrderId"))
            .OrderBy("OrderDate");
 
        // limit, offset
        queryTransformer
            .Skip("100")
            .Take("50");
 
        // aggregations
        queryTransformer.Aggregations.Clear();
 
        queryTransformer
            .SelectRecordsCount("recordsCount")
            .Select(queryTransformer.Columns[1].Count(), "column1Count")
            .Select(queryTransformer.Columns[1].Sum(), "column1Sum")
            .Select(queryTransformer.Columns[1].Avg(), "column1Avg")
            .Select(queryTransformer.Columns[1].Min(), "column1Min")
            .Select(queryTransformer.Columns[1].Max(), "column1Max");
    }
    finally
    {
        queryTransformer.EndUpdate();
    }
 
    MessageBox.Show("Modified SQL", queryTransformer.Sql);
}

VB.NET, .NET Edition

Using queryTransformer As New QueryTransformer()
    queryTransformer.QueryBuilder = queryBuilder1
    queryTransformer.SqlBuilder = plainTextSQLBuilder1
 
    queryTransformer.BeginUpdate()
 
    Try
        ' filter
        queryTransformer.Filters.Clear()
 
        queryTransformer.Where(queryTransformer.Columns(1).Not_Equal("100")). _
         Where(queryTransformer.Columns(2).[In]("('Value 1','Value 2','Value 3')")). _
         Where(queryTransformer.Columns.GetColumnByName("OrderId").Condition("> 100")). _
         Where("OrderDate is not null"). _
         Where(FilterFactory.[Or](). _
          Add(queryTransformer.Columns(1).Not_Equal("100")). _
           Add(queryTransformer.Columns(1).Not_Equal("200")))
 
        ' ordering
        queryTransformer.Sortings.Clear()
 
        queryTransformer. _
         OrderBy(queryTransformer.Columns(1)). _
         OrderBy(queryTransformer.Columns(2), False). _
         OrderBy(queryTransformer.Columns.GetColumnByName("OrderId")). _
         OrderBy("OrderDate")
 
        ' offset
        queryTransformer. _
         Skip("100"). _
         Take("50")
 
        ' aggregations
        queryTransformer.Aggregations.Clear()
 
        queryTransformer. _
         SelectRecordsCount("recordsCount"). _
         Select(queryTransformer.Columns(1).Count(), "column1Count"). _
         Select(queryTransformer.Columns(1).Sum(), "column1Sum"). _
         Select(queryTransformer.Columns(1).Avg(), "column1Avg"). _
         Select(queryTransformer.Columns(1).Min(), "column1Min"). _
         Select(queryTransformer.Columns(1).Max(), "column1Max")
 
    Finally
        queryTransformer.EndUpdate()
    End Try
 
    MessageBox.Show("Modified SQL", queryTransformer.Sql)
End Using

Visual Basic, ActiveX Edition

Private Sub miTransform_Click()
Dim t As ActiveQueryBuilderXControls.aqbxQueryTransformer
Set t = ActiveQueryBuilderX1.QueryTransformer

t.BeginUpdate
On Error GoTo EndUpdate

' prepare
Dim column0 As ActiveQueryBuilderXControls.aqbxOutputColumn
Set column0 = t.Columns.Items(0)
Dim column1 As ActiveQueryBuilderXControls.aqbxOutputColumn
Set column1 = t.Columns.ColumnByName("OrderDate")


' Filtering
t.Filters.Clear

t.Where column0.Not_Equal(100)
t.Where column1.In("('Value 1','Value 2','Value 3')")
t.WhereExpr "CustomerName Like 'John%'"


' Sorting
t.Sortings.Clear

t.OrderBy column0.Ascending
t.OrderByColumn column1, False
t.OrderByExpr "CustomerName"


' limits
t.Take 50
t.Skip 100


' aggregations
t.Aggregations.Clear

t.SelectRecordsCount "recordsCount"
t.Select column0.Count, "column0Count"

EndUpdate:
t.EndUpdate

MsgBox t.SQL
End Sub

C#, ActiveX Edition

7.3. How to check all fields on adding an object to the query

for .NET Edition use the following DiagramObjectAdded event handler:

private void queryBuilder_DiagramObjectAdded(UnionSubQuery query, DataSource addedObject)
{
    // Select all fields

    CriteriaList cl = query.CriteriaList;
    CriteriaItem ci;
     
    cl.BeginUpdate();
     
    try
    {
        for (int i = 0; i < addedObject.Fields.Count; i++)
        {
            ci = cl.FindField(addedObject, addedObject.Fields[i].Name);
             
            if (ci == null)
            {
                ci = cl.AddField(addedObject, addedObject.Fields[i].Name);
            }
             
            ci.Select = true;
        }
    }
    finally
    {
        cl.EndUpdate();
    }
}

7.4. How to add a relation programmatically

Suppose that we need a query always having the Orders and Customers tables and a join between these tables. The following example demonstrates programmatic query modification of a query to suit our requirement.

private void button1_Click(object sender, EventArgs e)
{
    string q;
    UnionSubQuery usq;
    DatasourceGroup fc;
    ArrayList ordersTables, customersTables;
    int iOrders, iCustomers;
    SQLQualifiedName OrdersName, CustomersName;
    SQLQualifiedName joinFieldName;
    DataSource o, c;

    // initial query
    q = "select * from (Orders o inner join [Order details] od on o.OrderId=od.OrderId) 
         inner join Products p On p.ProductID=od.ProductID";

    // create parsed names of tables we need to work with
    OrdersName = queryBuilder1.SyntaxProvider.ParseQualifiedName("Orders");
    CustomersName = queryBuilder1.SyntaxProvider.ParseQualifiedName("Customers");
    joinFieldName = queryBuilder1.SyntaxProvider.ParseQualifiedName("CustomerID");

    // synchronously load the query in the Query Builder
    queryBuilder1.SyncSQL = q;

    // get reference to the UnionSubQuery
    usq = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery;

    // get reference to the FromClause
    fc = usq.FromClause;

    ordersTables = new ArrayList();

    // try to find Orders table
    fc.FindTablesByDBName(OrdersName, ordersTables);

    if (ordersTables.Count == 0)
    {
        // Orders table not found - add it
        ordersTables.Add(usq.AddObject(OrdersName, null));
    }

    customersTables = new ArrayList();

    // try to find Customers table
    fc.FindTablesByDBName(CustomersName, customersTables);

    if (customersTables.Count == 0)
    {
        // Customers table not found - add it
        customersTables.Add(usq.AddObject(CustomersName, null));
    }

    // check existence of link between each Orders and each Customers
    for (iOrders = 0; iOrders < ordersTables.Count; iOrders++)
    {
        o = (DataSource) ordersTables[iOrders];

        for (iCustomers = 0; iCustomers < customersTables.Count; iCustomers++)
        {
            c = (DataSource) customersTables[iCustomers];

            if (fc.FindLink(o, joinFieldName, c, joinFieldName) == null &&
                fc.FindLink(c, joinFieldName, o, joinFieldName) == null)
            {
                usq.AddLink(o, joinFieldName, c, joinFieldName);
            }
        }
    }
}

7.5. How to define aliases programmatically

The following example adds aliases for the fields of the main query in case an alternate name is defined for a field and alias is empty.

UnionSubQuery unionSubQuery = queryBuilder1.Query.FirstSelect();

unionSubQuery.BeginUpdate();

try
{
    for (int i = 0; i < unionSubQuery.CriteriaList.Count; i++)
    {
        CriteriaItem ci = unionSubQuery.CriteriaList[i];

        if (ci.ExpressionField != null) // the criteria item is bound to a metadata field
        {
            if (ci.AliasString.Length == 0 && ci.ExpressionField.AltName.Length > 0)
            {
                ci.AliasString = ci.ExpressionField.AltName;
            }
        }
    }
}
finally
{
    unionSubQuery.EndUpdate();
}

7.6. How to modify WHERE clause programmatically

There are two or more ways to cope with this task depending on the type of modification.

1. You may want to add specific criteria if the query contains specific tables and if such criteria it's not already added to the query, i.e. to analyze the existing query first.

In general, you should find the necessary datasource in the list of datasources in the query; if found, find or add the necessary field name to the list of expressions, then add or modify the condition for this expression. Read the Understanding complex SQL query structure representation, Enumeration of data sources and joins in a sub-query and Enumeration of output columns in a sub-query articles to learn the basics. Use the code sample from the QueryModificationDemo project to cope with this task.

2. You may want to append or replace the criteria string to the WHERE clause regardless of it's current content.

It is not possible just to join the new and old criteria strings with AND operator and put the result string back to the query, but it's possible to operate with internal query representation objects to do almost the same. Use the code sample from the AppendCriteriaStringDemo project to cope with this task.

7.7. How to rename a table in the query programmatically

To rename a table in the query you may simply iterate through the datasources (learn basics in the Understanding complex SQL query structure representation and Enumeration of data sources and joins in a sub-query articles) and rename them, but the fine point is that you should call the QueryBuilder.Query.NotifySQLUpdatedRecursive() method to apply your changes to the query text.

Dictionary renamedDataSources = new Dictionary();
renamedDataSources.Add("old","new");

queryBuilder1.BeginUpdate();
try
{
    UnionSubQuery firstSelect = queryBuilder1.Query.FirstSelect();
    System.Collections.ArrayList dataSourcesArrayList = new System.Collections.ArrayList();

    firstSelect.FromClause.GetDatasources(dataSourcesArrayList);
    foreach (DataSourceObject dataSourceObject in dataSourcesArrayList)
    {
        SQLDatabaseObject databaseObject = dataSourceObject.DatabaseObject;
        string originalDataSourceName = databaseObject.QualifiedNameWithoutQuotes;

        if (renamedDataSources.ContainsKey(originalDataSourceName))
        {
            databaseObject.RemoveLastName();
            databaseObject.AddName(renamedDataSources[originalDataSourceName]);
        }
    }
    queryBuilder1.Query.NotifySQLUpdatedRecursive();
}
finally
{
    queryBuilder1.EndUpdate();
}

7.8. How to insert DISTINCT keyword into a query

Use the following code to insert DISTINCT keyword into a query:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
selexpr.SelectMode = new SQLSubQuerySelectModeDistinct(queryBuilder1.SQLContext);
queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

7.9. How to add TOP clause to MS SQL Server query

To get access to the TOP clause of MS SQL Server query, you should use the MSSQLSubQuerySelectExpressionExtender object that could be obtained in the following way:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
MSSQLSubQuerySelectExpressionExtender extender = (MSSQLSubQuerySelectExpressionExtender) selexpr.Extender;

Use the following code to add the top clause programmatically:

// add "TOP n" clause
SQLSubQueryTopRows top = new SQLSubQueryTopRows(queryBuilder1.SQLContext);

// or "TOP n PERCENT" clause
// SQLSubQueryTopPercent top = new SQLSubQueryTopPercent(queryBuilder1.SQLContext);

// add "... WITH TIES" if needed
top.WithTies = new SQLSubQueryTopWithTies(queryBuilder1.SQLContext);

top.Count = queryBuilder1.SQLContext.ParseExpression("10", null);

extender.Top = top;

queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

7.10. How to add LIMIT clause to MySQL query

To get access to the LIMIT clause of MySQL Server query, you should use the MySQLSubQuerySelectExpressionExtender object that could be obtained in the following way:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
MySQLSubQuerySelectExpressionExtender extender = (MySQLSubQuerySelectExpressionExtender) selexpr.Extender;

Use one of the following code samples to add the LIMIT clause programmatically:

// add "LIMIT n" clause
extender.Limit = new SQLLimit(queryBuilder1.SQLContext);
extender.Limit.Limit = 100;

// or add "LIMIT n OFFSET n"
extender.Limit = new SQLLimitOffset(queryBuilder1.SQLContext);
extender.Limit.Limit = 100;
((SQLLimitOffset) extender.Limit).Offset = 20;

queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

7.11. How to add FETCH FIRST to DB2 query

To get access to the FETCH FIRST clause of a DB2 query, you should use the DB2SubQuerySelectExpressionExtender object that could be obtained in the following way:

SQLSubQuerySelectExpression selexpr = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.QueryAST;
DB2SubQuerySelectExpressionExtender extender = (DB2SubQuerySelectExpressionExtender) selexpr.Extender;

Use one of the following code samples to add the LIMIT clause programmatically:

extender.FetchFirst = new SQLFetchFirst(queryBuilder1.SQLContext);
extender.FetchFirst.Count = new AstTokenNumber(queryBuilder1.SQLContext, "10");

queryBuilder1.ActiveSubQuery.ActiveUnionSubquery.NotifySQLUpdated();

7.12. How to add/check ROWNUM condition in Oracle query

Use the following function to add new or to modify existing ROWNUM condition for the Oracle query:

public void SetOracelRownum(int rowCount)
{
	// set ROWNUM for the currently selected sub-query
	UnionSubQuery usq = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery;

	// or set ROWNUM for the main query
	UnionSubQuery usq = queryBuilder1.Query.FirstSelect;

	ArrayList ciList = new ArrayList();
		
	CriteriaItem rownumItem = null;

	// Check the query already contains ROWNUM criteria item
	for (int i = 0; i < usq.CriteriaList.Count; i++)
	{
		if (usq.CriteriaList[i].ExpressionString.Equals("ROWNUM", StringComparison.CurrentCultureIgnoreCase))
		{
			rownumItem = usq.CriteriaList[i];
			break;
		}
	}

	if (rownumItem == null)
	{
		// Add new ROWNUM criteria item
		rownumItem = usq.CriteriaList.AddExpression("ROWNUM");
	}

	// Clear previously set rownum conditions
	for (int i = 0; i < usq.CriteriaList.GetMaxConditionCount(); i++)
	{
		rownumItem.ConditionStrings[i] = "";
	}

	if (usq.CriteriaList.GetMaxConditionCount() == 0)
	{
		// Query contains no other conditions.
		// Just set the new rownum condition.
		rownumItem.ConditionStrings[0] = "< " + rowCount;
	}
	else
	{
		// Query contains other conditions.
		// Add rownum condition to every OR condition in the query.

		for (int i = 0; i < usq.CriteriaList.GetMaxConditionCount(); i++)
		{
			// CollectCriteriaItemsWithWhereCondition:
			// This function returns the list of conditions that were found in
			// the i-th criteria column, applied to specific clause (WHERE or HAVING).
			// Thus, this function collects all conditions joined with AND
			// within one OR operator.
			CollectCriteriaItemsWithWhereCondition(usq.CriteriaList, i, ciList);

			ciList.Remove(rownumItem);
		
			if (ciList.Count > 0)
			{
				rownumItem.ConditionStrings[i] = "< " + rowCount;
			}
		}
	}
}

private void CollectCriteriaItemsWithWhereCondition(CriteriaList criteriaList, int columnIndex, ArrayList result)
{
	result.Clear();

	for (int i = 0; i < criteriaList.Count; i++)
	{
		if (criteriaList[i].ConditionType == ConditionType.Where &&
			criteriaList[i].ConditionCount > columnIndex &&
			criteriaList[i].GetASTCondition(columnIndex) != null)
		{
			result.Add(criteriaList[i]);
		}
	}
}

8.1. Available languages

Available languages for .NET and Java Editions:

• Arabic (Saudi Arabia)
• English
• Spanish
• French
• German
• Italian
• Portuguese (Brazil)
• Polish
• Swedish
• Turkish
• Russian

Available languages for VCL and ActiveX Editions:

• English
• Spanish
• French
• German
• Italian
• Chinese
• Japanese
• Portuguese (Brazil)
• Czech
• Swedish
• Bulgarian
• Korean
• Russian
• Turkish

8.2. Selecting language in run-time

There are two ways to select language. First is to select it from the list of available languages. If you can not find your language in this list, you may localize the component by yourself and load your language from the language file.

• VCL Edition
• ActiveX Edition
• WinForms .NET Edition
• ASP.NET Edition
• Java Edition

• Selecting language in VCL Edition (Delphi):

   
       // use this unit to get access to acQBLanguageManager global object
       uses acQBLocalizer;
   
       // show languages
       for i:=0 to acQBLanguageManager.LanguagesCount-1 do
       begin
           ShowMessage(acQBLanguageManager.Language[i].LanguageName);
       end;
   
       // set built-in default language
       acQBLanguageManager.CurrentLanguageIndex := -1;
   
       // set language #0
       acQBLanguageManager.CurrentLanguageIndex := 0;
   
       // load and apply custom language from file (lng is integer)
       lng := acQBLanguageManager.AddLanguageFromFile('MyLanguage', 'MyLang.lng');
       acQBLanguageManager.CurrentLanguageIndex := lng;
   
       // load and apply language from resource 
       // (lng is integer, MyLang is resource name, LANGUAGES is resource type)
       lng := acQBLanguageManager.AddLanguageFromResource('MyLanguage', 'MyLang', 'LANGUAGES');
       acQBLanguageManager.CurrentLanguageIndex := lng;
   
       // load and apply language from string (lng is integer, sLanguage is string)
       lng := acQBLanguageManager.AddLanguageFromString('MyLanguage', sLanguage);
       acQBLanguageManager.CurrentLanguageIndex := lng;
   
       // load and apply language from TStrings (lng is integer, ssLanguage is TStrings)
       lng := acQBLanguageManager.AddLanguageFromStrings('MyLanguage', ssLanguage);
       acQBLanguageManager.CurrentLanguageIndex := lng;
  

• Selecting language in ActiveX Edition (VB.NET):

   
      ' show languages
      For i = 0 To ActiveQueryBuilderX1.Localizer.Count - 1
          MsgBox "Language #" + Str(i) + ": " + ActiveQueryBuilderX1.Localizer.LanguageName(i)
      Next i
   
      ' set built-in default language
      ActiveQueryBuilderX1.Localizer.CurrentLanguage = -1
   
      ' set language #0
      ActiveQueryBuilderX1.Localizer.CurrentLanguage = 0
   
      ' load and apply custom language from file
      lng = ActiveQueryBuilderX1.Localizer.AddLanguageFromFile("MyLanguage", "MyLang.lng")
      ActiveQueryBuilderX1.Localizer.CurrentLanguage = lng
   
      ' load and apply custom language from string (sLanguage is string)
      lng = ActiveQueryBuilderX1.Localizer.AddLanguageFromString("MyLanguage", sLanguage)
      ActiveQueryBuilderX1.Localizer.CurrentLanguage = lng
  

• Selecting language in ActiveX Edition (C#):

  
      // show languages
      for (int i = 0; i < axActiveQueryBuilderX1.Localizer.Count; ++i)
      {
          MessageBox.Show("Language #" + i.ToString() + ": " + 
          axActiveQueryBuilderX1.Localizer.get_LanguageName(i));
      }
   
      // set built-in default language
      axActiveQueryBuilderX1.Localizer.CurrentLanguage = -1;
   
      // set language #0
      axActiveQueryBuilderX1.Localizer.CurrentLanguage = 0;
   
      // load and apply custom language from file
      int lng = axActiveQueryBuilderX1.Localizer.AddLanguageFromFile("MyLanguage", "MyLang.lng");
      axActiveQueryBuilderX1.Localizer.CurrentLanguage = lng;
   
      // load and apply custom language from string (sLanguage is string)
      int lng = axActiveQueryBuilderX1.Localizer.AddLanguageFromString("MyLanguage", sLanguage);
      axActiveQueryBuilderX1.Localizer.CurrentLanguage = lng;
  

• Selecting language in .NET Edition (C#):

   
      // How to load a language from an external source:
   
      // load language from specified file
      ActiveDatabaseSoftware.ActiveQueryBuilder.Helpers.Localizer.LoadLanguageFromFile(string languageFile);
      // load language from a XML string
      ActiveDatabaseSoftware.ActiveQueryBuilder.Helpers.Localizer.LoadLanguageFromString(string xmlString);
      // load language from a stream containing an XML string
      ActiveDatabaseSoftware.ActiveQueryBuilder.Helpers.Localizer.LoadLanguageFromStream(Stream languageStream);
   
      // How to get a list of available languages:
      foreach (string lng in ActiveDatabaseSoftware.ActiveQueryBuilder.Helpers.Localizer.Languages)
      {
          Trace.WriteLine(lng);
      }
   
      // The language list always contains "Auto" and "Default" strings.
      // - "Auto" means the language will be detected automatically according to CultureInfo specified for 
      //   UI culture of the current thread (System.Threading.Thread.CurrentThread.CurrentUICulture).
      // - "Default" means usage of default strings containing in embedded resources.
   
      // How to set current language:
      queryBuilder1.Language = "es-MX";
      // If specified language doesn't exist, Localizer will try to fallback to the base language ("es")
      // If it doesn't exist too, the Localizer will use the "Default" language.
  

• Selecting language in ASP.NET Edition (C#):

  To get list of available languages, use the code above for WinForms .NET Edition.

  Use the QueryBuilderControl.Language property to specify required language for the control. Language will be detected automatically according to the "Accept-Language" HTTP request header field if this property is set to "Auto".

  To load language from external language file, place it to the "Language Files" at the root of your web site and set the QueryBuilderControl.UseCustomLanguageFiles. Language file will be determined according to the Language property value.

• Selecting language in Java Edition:

   
      // How to load a language from an external source:
   
      // load language from specified file
      com.adbs.utils.Helpers.localizer.loadLanguageFromFile(String languageFile);
      // load language from a XML string
      com.adbs.utils.Helpers.localizer.loadLanguageFromString(String xmlString);
      // load language from a stream containing an XML string
      com.adbs.utils.Helpers.localizer.loadLanguageFromStream(InputStream languageStream);
   
      // How to get a list of available languages:
      for (String lng : com.adbs.utils.Helpers.localizer.languages)
      {
          System.out.println(lng);
      }
   
      // The language list always contains "Auto" and "Default" strings.
      // - "Auto" means the language will be detected automatically according to information
      // from java.util.Locale.getDefault()
      // - "Default" means usage of default (English) strings containing in embedded resources.
   
      // How to set current language:
      queryBuilder1.setLanguage("es-MX");
      // If specified language doesn't exist, Localizer will try to fallback to the base language ("es")
      // If it doesn't exist too, the Localizer will use the "Default" language.
  

8.3. Localizing Active Query Builder .NET and Java Edition

If you didn't find your language in the list of supported languages, you may create your own localization.

Please find the 'en.xml' file in the installation directory and translate it to your language using any XML editor you like.

The <language id=""> attribute value should be set according to your language code. You may find the list of codes here.

8.4. Localizing Active Query Builder VCL and ActiveX Edition

We recommend the following way to localize Active Query Builder VCL and ActiveX Edition:

• Download and install the component and find the 'English.lng' file in 'Languages' directory.

• Download and install IniTranslator application for fast and easy translation.

• Open 'English.lng' file as Original. We also recommend you to set highlighted IniTranslator options as shown on the screenshot:
  

• Now you may start to translate. Below are the form screenshots intended to ease translation process. Each section of .lng file represents a form (window), except the [Active Query Builder] section that contains common strings used in Active Query Builder (popup menus, grid captions, error messages, etc.).

• If you find that some of translated strings doesn't fit the form, try to cut the string, or tell us about this case so we can allocate more space on the form for it.

• There are two strings in the [General Settings] section that should be set in exact matching to your language: 'CharSet' and 'BiDiMode'. First item specifies required character set of the font and second specifies the bi-directional mode of controls. All possible values are listed in the comments in 'English.lng' file, so all you need is to uncomment settings that fit your language and copy them to your translation file.

Sections of .lng file

[TacDatasourcePropsForm] - This form represents properties of any data source object in the query.

[TacLinkPropsForm] - This form represents options of JOIN clause.

[TacQueryPropsFbForm], [TacQueryPropsIbForm] - These forms represent common properties of InterBase and Firebird SQL queries.

[TacQueryPropsSbForm] - This form represents common properties of Sybase SQL queries.

[TacQueryPropsMSForm] - This form represents common properties of MS SQL Server queries.

[TacQueryPropsMYForm] - This form represents common properties of MySQL queries.

[TacAddObjectForm] - This form is used to add objects to the query.

[TacProcedureParams] - This form required to get input parameter values of stored procedures and functions.

Actually, this will be enough to localize end-user interface of Active Query Builder, so you may stop here, if you don't want to show any of setup windows listed below to end-users. Just copy the rest of the original file to your translation, and that's all.

[TfMetadataContainerEditor] - This form is used to manage objects stored in Metadata Container.

[TfacQBMetadataContainerLoad] - This wizard is used to load metadata to Metadata Container. It consists of the following steps:

Step 1. Welcome message.

Step 2. Selecting SQL Syntax.

Step 3. Selecing Connection Type.

Step 4. Specifying connection options.

Step 5. Selecting databases.

Step 6. Specifying additional fintration.

Step 7. Loading Metadata.

[TfrAcQBBDEConnectionEdit] - BDE Connection Options. This frame is used internally in the Load Metadata Wizard on the fourth step.

[TfrAcQBADOConnectionEdit] - ADO Connection Options. This frame is used internally in the Load Metadata Wizard on the fourth step.

[TfrAcQBdbExConnectionEdit] - dbExpress Connection Options. This frame is used internally in the Load Metadata Wizard on the fourth step.

[TfrAcQBIBExConnectionEdit] - IbExpress Connection Options. This frame is used internally in the Load Metadata Wizard on the fourth step.

[TfrFilterEditor], [TfEditMetadataFilter] - This form is intended to define metadata filters. You should call the EditMetadataFilter method to open this form.

[TfmAQBOptions] - This form is the second page of the Properties window. You should call the EditProperties method to open this form.

[TSQLFormatFrame], [TfmSQLBuilderOptions] - This form and its frame are the third page of the Properties window.

[TfmAQBOptions2] - This form is the first page of the Properties window.

9.1. How to configure assembly binding redirection?

> I want to connect to VistaDB.
> Do I have to use VistaDB-Version 3.2 Build 38?
> I would like to use a new version!

To use any new version of the VistaDB.NET20 assembly you need to override the version binding using the following 'app.config' file (add it into your project):

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="VistaDB.NET20"
          publicKeyToken="dfc935afe2125461"
          culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-65535.65535.65535.65535" newVersion="3.3.1.52" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

Don't forget to change the 'newVersion' attribute to the required version of the VistaDB.NET20 assembly.

More information can be found here.

9.2. How to use DevExpress controls in .NET Edition?

To use DevExpress controls, you should add a reference to the ActiveQueryBuilder.DevExpressControlFactory.dll file to your project or you may simply put the .dll near by your executable file. In the Main() function of your application, before any form creation, set up the DevExpress usage and turn on the QueryBuilder.UseDevExpressControls static property:

      DevExpress.Skins.SkinManager.EnableFormSkins();
      ActiveDatabaseSoftware.ActiveQueryBuilder.QueryBuilder.UseDevExpressControls = true;

Additionaly you can set the QueryBuilder.OverrideDevExpressThemeColors static property to True to use colors settings of the component instead of using colors from the current DevExpress theme.

Working with Visual Basic, you should use the My.Application.Startup event.

Your project must refer to DevExpress.Data.*, DevExpress.Utils.*, DevExpress.XtraEditors.*, DevExpress.XtraGrid.* and DevExpress.XtraTreeList.* assemblies and the ActiveDatabaseSoftware.DevExpressControlFactory2.dll.

If you have the "Failed to call GetCustomTreeView() method from DevExpressControlFactory" error, please check the following:

1. Make sure that you have the same DevExpress controls version as referred by the DevExpressControlFactory assembly. We are strive to refer to the latest DevExpress version, but there could be a small gap between the latest and the referred version. If you have a newer version of DevExpress controls, you can override version binding as described in the previous article.

2. If you refer to the same version of DevExpress assemblies, but still have this problem, please set the "Copy Local" property of referred DevExpress and Active Query Builder assemblies to True.

Note that the Professional .NET Subscription is shipped with the source code of DevExpress controls factory assembly, even if you didn't purchase the Full Source Code option, so you can recompile it with any DevExpress controls version that you have, considering that no changes were made in the DevExpress controls API since that time.

9.3. How to preserve the user formatting of the SQL query text?

It is not possible to preserve comments and formatting in the query that was generated by a query builder, and this behavior can't be changed in the near future. But it is possible to preserve the user formatting until the query is not modified visually. To do this, place the following code to the Leave event of the text editor. To load a query to the query builder, assign the text to the text editor first and call this event handler after that.

private void sqlTextEditor1_Leave(object sender, EventArgs e)
{
    try
    {
        queryBuilder1.SQL = sqlTextEditor1.Text;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    try
    {
        string generatedSql;
        string userSql = sqlTextEditor1.Text;

        SaveEditState();

        SQLSelectStatement selectStatement = null;

        try
        {
            selectStatement = queryBuilder1.SQLContext.ParseSelect(userSql);
            ArrayList lCTE = new ArrayList();
            ArrayList lFromObj = new ArrayList();
            selectStatement.PrepareAndFixupRecursive(lCTE, lFromObj);

            generatedSql = selectStatement.GetSQL(plainTextSQLBuilder1.SQLBuilder);

            if (plainTextSQLBuilder1.SQL != generatedSql)
            {
                queryBuilder1.BeginUpdate();

                try
                {
                    switch (plainTextSQLBuilder1.TargetQueryPart)
                    {
                        case QueryPart.Query:
                            queryBuilder1.Query.Clear();
                            queryBuilder1.Query.LoadFromAST(selectStatement);
                            break;
                        case QueryPart.SubQuery:
                            queryBuilder1.ActiveSubQuery.Clear();
                            queryBuilder1.ActiveSubQuery.LoadFromAST(selectStatement);
                            break;
                        case QueryPart.UnionSubQuery:
                            plainTextSQLBuilder1.SQL = generatedSql;
                            break;
                        default:
                            throw new ArgumentOutOfRangeException();
                    }
                }
                finally
                {
                    queryBuilder1.EndUpdate();
                }
            }
            else
            {
                selectStatement.Dispose();
            }
        }
        catch (Exception)
        {
            selectStatement.Dispose();
            plainTextSQLBuilder1.SQL = userSql;
            generatedSql = plainTextSQLBuilder1.SQL;
        }

        if (plainTextSQLBuilder1.SQL == generatedSql)
        {
            sqlTextEditor1.Text = userSql;

            RestoreEditState();
        }
    }
    catch (Exception exception)
    {
        MessageBox.Show(exception.Message);
    }
}

private int _caretPos = -1;
private int scrollPosX = -1;
private int scrollPosY = -1;

private void SaveEditState()
{
    _caretPos = sqlTextEditor1.SelectionStart;
    scrollPosX = sqlTextEditor1.HorizontalScroll.Value;
    scrollPosY = sqlTextEditor1.VerticalScroll.Value;
}

private void RestoreEditState()
{
    sqlTextEditor1.SetSelection(_caretPos, 0);
    sqlTextEditor1.HorizontalScroll.Value = scrollPosX;
    sqlTextEditor1.VerticalScroll.Value = scrollPosY;
}

9.4. How to work with the .NET Edition using DevArt dotConnect (Core Lab ADO.Net providers)?

Using DevArt dotConnect, you should use the Universal metadata provider to work with Active Query Builder .NET Edition. This metadata provider takes any Connection object to get access to your database.

See the code example below:

// Create connection using Devart component
String connectionString = "Data Source=192.168.1.4;Persist Security Info=True;User ID=hr;Password=1";
Devart.Data.Oracle.OracleConnection connection = new Devart.Data.Oracle.OracleConnection(connectionString);

// Create UniversalMetadataProvider
UniversalMetadataProvider mp = new UniversalMetadataProvider();
mp.Connection = connection;

// Set up the QueryBuilder
queryBuilder1.MetadataProvider = mp;
queryBuilder1.SyntaxProvider = new OracleSyntaxProvider();

// Load metadata
queryBuilder1.RefreshMetadata();

9.5. I have a delay on creating QueryBuilder when internet connection is enabled. How to avoid it?

This can be caused by verification of the Authenticode signature over the Internet. In case of problem with connecting to the verification server. Microsoft recommends to avoid the unnecessary startup cost associated with verifying the publisher signature unless your application executes on a computer with custom CAS policy, or is intending to satisfy demands for PublisherIdentityPermission in a partial-trust environment.

To avoid this verification, add the following code to the "app.config" file:

<configuration> 
     <runtime> 
           <generatepublisherevidence enabled="false" /> 
     </runtime> 
</configuration>

9.6. How to use ErrorProvider with the SQL Text Editor?

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();

        queryBuilder1.SyntaxProvider = new MSSQLSyntaxProvider();

        queryBuilder1.MetadataContainer.LoadFromXMLFile("Northwind.xml");
    }

    private void plainTextSQLBuilder1_SQLUpdated(object sender, EventArgs e)
    {
        sqlTextEditor1.Text = plainTextSQLBuilder1.SQL;
    }

    private void sqlTextEditor1_Validating(object sender, System.ComponentModel.CancelEventArgs e)
    {
        try
        {
            queryBuilder1.SQL = sqlTextEditor1.Text;
        }
        catch (SQLError exception)
        {
            e.Cancel = true;
            sqlTextEditor1.SetSelection(exception.ErrorPos.pos, exception.ErrorTokenValue.Length);
            errorProvider1.SetError(sqlTextEditor1, exception.Message);
        }
    }

    private void sqlTextEditor1_Validated(object sender, EventArgs e)
    {
        errorProvider1.SetError(sqlTextEditor1, "");
    }
}

10.1. How to change sizing of the component parts?

Redefine the following styles in your css:

.qb-ui-tree-item-block {
   height: 416px;
}

#qb-ui-tree {
   width: 190px;
}

#center {
   margin-left: 190px;
}

#qb-ui-canvas {
   height: 321px;
}

#qb-ui-grid {
   height: 157px;
}

#qb-ui-editor textarea {
   height: 150px;
}

10.2. Allowing the end-user to build different queries in multiple windows

There's a QueryBuilderControl.MultipleQueriesPerSession property that instructs to allow this being set to true.

Note that in this case the queries built by the end-user will not be saved to a session automatically.

This property must be set in HTML code, in the attributes of the control; setting it programmatically is useless:

<AQB:QueryBuilderControl
   ID="QueryBuilderControl1"
   runat="server"
   OnInit="QueryBuilderControl1_Init"
   MultipleQueriesPerSession="True"
/>

10.3. Hiding columns of the Query Columns Grid

You can set the "display: none" attribute to appropriate styles in CSS file:

.ui-qb-grid-row-expression
.ui-qb-grid-row-aggregate
.ui-qb-grid-row-alias
.ui-qb-grid-row-sorting
.ui-qb-grid-row-sortingOrder
.ui-qb-grid-row-grouping
.ui-qb-grid-row-groupingCriterion
.ui-qb-grid-row-condition
.ui-qb-grid-row-conditionOr

For example:

<style type="text/css">
.ui-qb-grid-row-alias { display:none; }
</style>

Or you can do the same in JavaScript:

<script type="text/javascript">
$('.ui-qb-grid-row-alias').hide();
</script>

10.4. Using Active Query Builder inside RAD AJAX panel

Execute this code on client side when radAjaxPanel content is loaded:

QB.Web.Application = new Application();
QB.Web.Application.init();
OnApplicationReadyTrigger();
QB.Web.Application.fullSync();

And add links to the following resources to the page header (or use any on-demand dynamic resource loading mechanism):

<script id="AQB_NET_script_usr1" src="/js/release/usr_v0_x_xx.js" type="text/javascript"></script>
<script id="AQB_NET_script_usr0" src="handlers/ResourceHandler.axd?type=lng" type="text/javascript"></script>
<link id="AQB_NET_css_lib3" href="/css/themes/smoothness/jquery-ui-1.10.x.custom.css" type="text/css" rel="stylesheet" />
<link id="AQB_NET_css_lib2" href="/css/jquery.treeview.css" type="text/css" rel="stylesheet" />
<link id="AQB_NET_css_lib1" href="/css/jquery.contextMenu.css" type="text/css" rel="stylesheet" />
<link id="AQB_NET_css_lib0" href="/css/jquery.editable-select.css" type="text/css" rel="stylesheet" />
<link id="AQB_NET_css_usr2" href="/css/qb/qb.ui.table.css" type="text/css" rel="stylesheet" />
<link id="AQB_NET_css_usr1" href="/css/qb/qb.ui.grid.css" type="text/css" rel="stylesheet" />
<link id="AQB_NET_css_usr0" href="/css/base.css" type="text/css" rel="stylesheet" />

10.5. Problems with Extensions in .NET Framework 3.5 and higher

Using the component in .NET Framework 3.5 you may face with the problem of multiple definition of ExtensionAttribute defined in Newtonsoft.Json.dll if you use them in your project. To avoid this problem, please replace this assembly in your project with one from the "assemblies\third-party\Net35\" directory in the component's installation folder.

11.1. How to build the Applet demo in Eclipse?

1. Select "New" – "Java Project" in the File menu.
   Enter the project name and click Next.

   

2. Click the Libraries tab and press the "Add External JARs..." button.
   Select "ActiveQueryBuilder.jar" file from the "lib_signed" folder of the distribution package.
   Click Finish.

   

3. Right-click the "src" folder in the Package Explorer and select "samples\AppletDemo\src" folder of the distribution package.
   Click Finish.

   

4. Now you can run or debug the project. The applet will be automatically executed in the standard Java Applet Viewer application (available in JDK).

5. To get the compiled JAR file, right click on the java file and select "Export". In the Export dialog choose "JAR file" and specify the export destination folder.

   

Note: Applets always require signed JARs to be able to run in a browser. The signing instruction is available in this article.

11.2. How to sign Java applet?

How To Sign an Applet?

To run an applet in a web browser you should sign it with digital certificate beforehand because applets are untrusted in security architecture of all browsers by default.

Certificate authorities typically charge a fee for the service of validating their clients' credentials. However, for testing and demo purposes, you may create a self-signed certificate. The information given in a self-signed certificate has not been validated by a trusted third party. If you plan to widely distribute the applet you should obtain a certificate that is validated by a trusted certificate authority. The procedure for this is beyond the scope of this tutorial.

1. Make sure your applet is compiled to JAR file. The applet must be in a JAR file before a certificate can be attached to it. If the applet was previously referenced with the help of a 'codebase' attribute in the <applet> tag of the HTML file, replace the 'codebase' attribute with the 'archive' attribute. The value of the 'archive' attribute is the URL of a JAR file.

2. Create a public/private key pair. The command for this is

keytool -genkey

The keytool is SDK utility. It will prompt you for a password to your keystore and  for the remaining parameters, one of which is alias, whose value is the name of the key. The keystore is a file that contains your public/private key-pairs, and the public-keys  of others with whom you exchange information.

3. Create a certificate for the key you created in the previous step.

keytool -selfcert

Again, the keytool will prompt you for a keystore password and remaining parameters. This certificate is now self-signed by you, meaning that it has not been validated by any third party. 

4. Run jarsigner to associate this certificate with the JAR file that contains your applet.

jarsigner AppletDemo.jar mykey

mykey is the name of the public key of the certificate you just created. This creates a digest for each file in your JAR and signs them with your private key. These digests or hashes, the public key, and the certificate will all be included in the "META-INF" directory of the JAR file.

11.3. My Java applet doesn't work in a browser!

1. If you are getting the "java.security.AccessControlException", this means you should sign the applet before using it in a browser. See "How to sign Java applet" tutorial.

2. If you are getting "java.lang.NullPointerException at sun.plugin2.applet.Plugin2Manager.findAppletJDKLevel (Unknown Source)", this means that the Java VM cannot find some of dependent classes (Class-Path problem).

For an applet using Active Query Builder Java Edition, you should specify a reference to the ActiveQueryBuilder.jar in the jar file's manifest. For example:

Class-Path: lib/ActiveQueryBuilder.jar

Also you may specify it in the "archive" attribute of the <applet> tag:

<APPLET code="appletdemo/QueryBuilderApplet.class" archive="AppletDemo.jar,ActiveQueryBuilder.jar" width=800 height=600></APPLET>

11.4. Using Java applet in ColdFusion

We used to manage Java applet in ColdFusion 9 on Windows2008 R2 x64 and IIS 7.

We placed the component ActiveQueryBuilder.jar and the applet LiveDemoApplet.jar into the \test\ folder of default IIS site (full path is C:\inetpub\wwwroot\test\).

Below are the steps to get it working:

1. Open the ColdFusion Admininstrator console.

2. Go to Extensions / Java Applets. Here you have to register the applet before using it on a page.

See the screenshot for working parameters.

There are:

• "Applet Name" is identificator you will use to refer the applet from <cfapplet> tag.
• "Code" is the classpath of main applet class inside of JAR file without ".class" in the end.
• "Code Base" is the path to jar file relative to the site root directory. Both "/" symbols are required.
• "Archive" is the name of the applet JAR file.

Other params are optional, but you may specify them here to omit them later in the <cfapplet> tag.

3. Create a sample "demo.cfm" file in the /test/ folder. It's only content is:

<cfapplet appletSource="AQB" name="applet1_var">

The "appletSource" attribute value is the ID that you entered during the applet registration and "name" is variable you can use to access the applet from the page code.

That's all. You may type the valid URL in your browser, to run your applet.

Please note that you should to sign the applet's jar file with developer or commercial certificate, otherwise the browser will fail to load the applet becuase of Java machine security settings.

12.1. Unit acQB***MetaProvider was compiled with a different version of ***

>I get the following error on compile.
>[Fatal Error] main.pas(8): Unit acQBMOPgDACMetaProvider was compiled with a different >version of PSQLDbTables.TPSQLDatabase

> I am moving to to Delphi 2007. AQB used to work in Delphi 7, but now I get the following error message.
> [DCC Error] frmQueryAQB.pas(9): F2051 Unit acQBFIBPlusMetaProvider was compiled with a different version of pFIBDataSet.TpFIBDataSet

This error means that you have compiled the ac***MetaProvider unit with previous version of your data access components package, when you were installing the Active Query Builder.

Third-party metadata providers are always compiled during the Active Query Builder installation process (as we never know the exact version of third-party components installed on your PC), and if you upgrade the third-party component pack, you must also recompile the appropriate metadata provider package.

To do this, start the Active Query Builder setup program (click on the 
"Start"->"Programs"->"ActiveDBSoft"->"Active Query Builder VCL Edition"->"Uninstall Active Query Builder" item) and choose "Add or Remove IDE Installations", click next, click on the Delphi 7 checkbox (the text "Action: Reinstall" will appear below the Delphi 7 checkbox) and click Next. You can tune the reinstall process at the next page. Click Next, and the installer will recompile the choosed packages using the currently installed third-party components.

If this won't help you, you should search the entire hard drive for *.dcp files from your data access components package and remove all of them. After that, recompile DAC and AQB packages.

---

> BDS 2006 compiler error message:
> [Pascal Fatal Error] Unit1.pas(7): F2051 Unit acQBIBExMetaProvider was compiled with a different version of IBQuery.TIBQuery

Please check you have all latest BDS 2006 update packs installed. Click Help -> About Borland Delphi menu item, and verify your version. It must be "Borland® Delphi® for Microsoft® Windows™ Version 10.0.2558.35231 Update 2".

Even if your IBExpress versions differs, you can recompile IBEx connectivity packages manually using your version.

1. Add C:\Program Files\ActiveDBSoft\Active Query Builder\Sources to the library path in your IDE
2. Open and compile the runtime package "C:\Program Files\ActiveDBSoft\Active Query Builder\Packages\Delphi2006\acQueryBuilderIbExD10.dpk"
3. Open, compile and install the design-time package "C:\Program Files\ActiveDBSoft\Active Query Builder\Packages\Delphi2006\acQueryBuilderDsgnIbExD10.dpk"

13.1. How to distribute ActiveX control automatically through the Internet Explorer?

ActiveX controls should not be installed on each client PC using Active Query Builder installation package. You should place the CAB and LPK files to your web site and link to them from your web page:

<OBJECT id="qb" classid="clsid:C4C9F1E5-2E72-4B58-BA61-6D63730FB7C8" CODEBASE="http://www.yourWebSite.com/path/aqbx.cab#version=1,14,0,0" " VIEWASTEXT>
   <PARAM ...>
</OBJECT>
<OBJECT CLASSID="clsid:5220CB21-C88D-11cf-B347-00AA00A28331">
   <PARAM NAME="LPKPath" VALUE="aqbx.lpk">
</OBJECT>

The cab and lpk files are available in HTMLDemo folder among with HTML code example.

If the customer's PC already had the free version installed and it doesn't replace with the new version from your web page, you may need to unregister it first using the following command: "regsvr32.exe /u C:\Path_to_ocx\ActiveQueryBuilderXControls.ocx".

13.2. How to check if user cancelled the connection on login prompt?

Below is the code sample in JavaScript.

// connect using Login Prompt
qb.LoginPrompt = true;
qb.ConnectionString = "your OLE DB connection string here";

try {
    qb.Connect();

    if (qb.Connected)
    {
        alert("Connected");
    }
    else
    {
        // no errors, but not connected 
        // user pressed "Cancel" in login prompt dialog
        alert("Connection is cancelled");
    }
}
catch (e)
{
    // error occured: wrong password, network error, etc.
    alert(e.message);
}