HomeActive Query Builder - HowTos and TroubleshootingPrinter Friendly Version

Active Query Builder - HowTos and Troubleshooting

1. User Interface Customization

1.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;

1.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.ActiveQueryBuilder2;

public partial class Form1 : Form
{

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

	private void ValidateContextMenu(Control control, ControlOwner owner, ICustomContextMenu 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 QueryColumnListItem; control is QueryColumnListControl.
  • controlOwner is UnionSubQuery; control is DesignPane: design 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).

1.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;
}

1.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;
       }
}

1.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
            }
        }
    }
}));

1.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
            }
        }
    }
}));

2. How-tos for WinForms .NET Edition

2.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.

2.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.

2.3. How to preserve comments in the SQL query text?

It is not possible to preserve comments in the query in the current version. We hope to fix this problem in the near future. But it is possible to preserve comments 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 query text to the text editor first and then call this event handler.

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;
}

2.4. How to expand specific node of the Database Schema Tree?

First, please note that there's a way to automatically expand all nodes of the tree for certain number of levels. This is determined by the QueryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel property.

To expand specific namespace node, use the following code sample.

{ 
	ICustomTreeView databaseSchemaTree = queryBuilder1.DatabaseSchemaTree;
	FindAndExpandSchemaNode(databaseSchemaTree.RootNode, "HumanResources");
}

private void FindAndExpandSchemaNode(object parentTreeNode, string name)
{
	ICustomTreeView databaseSchemaTree = queryBuilder1.DatabaseSchemaTree;

	object child = databaseSchemaTree.GetNodeFirstChild(parentTreeNode);
	while (child != null)
	{
		var metadataStructureItem = databaseSchemaTree.GetNodeTag(child) as MetadataStructureItem;
		var metadataItem = metadataStructureItem.MetadataItem;
		if ((metadataItem.Type & MetadataType.Namespaces) > 0)
		{
			if (metadataItem.Type == MetadataType.Schema)
			{
				if (metadataItem.Name == name)
				{
					databaseSchemaTree.SetNodeExpanded(child, true);
					return;
				}
			}
			else
			{
				databaseSchemaTree.SetNodeExpanded(child, true);
				FindAndExpandSchemaNode(child, name);
			}
		}

		child = databaseSchemaTree.GetNodeNextSibling(child);
	}
}

2.5. 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();

2.6. 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>

2.7. 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, "");
    }
}

3. Active Query Builder ASP.NET Edition

3.1. JavaScript client API Reference

Contents


Web Application API

 

Basic methods

QB.Web.Application.refreshSql(callback);

The refreshSql method should be called to apply changes in the query that were made by the user in the SQL Text Editor. Calling of this method will lead to sending of the SQL query text to the server, it parsing and subsequent updating of the query visual representation. In the callback function of this method, the programmer can check the QB.Web.Application.SqlErrorEventArgs property. It will be null in case of successful parsing of the updated query, otherwise it will contain information about SQL parsing error.

Status Bar message displaying methods

QB.Web.Application.MessageInfo(message);
QB.Web.Application.MessageWarning(message); 
QB.Web.Application.MessageError(message); 

These methods display the specified message in the Status Bar with appropriate icon at the left (information, exclamation and red cross icons).

Query parameters handling methods

QB.Web.Application.getQueryParams(callback);
QB.Web.Application.setQueryParamValues(params, callback);

If you want to let the user to specify values of the query parameters, call the getQueryParams method first. In the callback function you will receive array of objects of the following type (or null if parameters aren't found):

QB.Web.Dto.QueryParamDto = function() {
   this.Symbol = null;
   this.Name = null;
   this.FullName = "";
   this.DataType = "String";
   this.DataTypeDB = 0;
   this.CompareOperator = null;
   this.ComparedField = null;
   this.ComparedObject = null;
   this.MetadataObjectGuid = null;
   this.MetadataFieldGuid = null;
   this.Value = null;
};

Show the dialog prompting for parameter values, apply them to the Value field and send this array back to the server using the setQueryParamValues method call. In the callback function of this method you can fire SQL query execution on the server.

There's the "Query Results" demo project that illustrates query parameters handling and SQL query execution. It is shipped with the component's installation package in two variants: one for the classic ASP.NET environment and another for MVC with Razor view engine. All demo projects are installed to the "%USERPROFILE%\Documents\Active Query Builder ASP.NET Examples" folder.

Criteria Builder handling methods

QB.Web.Application.CriteriaBuilder.isValid(); 
QB.Web.Application.CriteriaBuilder.sendDataToServer(callback);
QB.Web.Application.CriteriaBuilder.update(callback);

The isValid method allows for checking of the Criteria Builder's validity state. The control might have invalid state if some of it's conditions have incorrect or unspecified values.

The sendDataToServer method should be called to apply criteria to the query on the server. It fails to apply criteria and returns false if Criteria Builder is currently in the invalid state. In the callback function of this method you can fire SQL query execution on the server.

The update method requests the actual data from server and updates the control. One may need to call this method when criteria is changed in the QueryTransformer object programmatically on the server side, to update data in the control.

Basic properties

These properties allow to get SQL query text. The first gets always valid query text from the query builder, the second gets the content of SQL text editor that may contain arbitrary text entered by the user.

QB.Web.Application.SQL
QB.Web.Application.editor.val()

The following properties can be used to get information about component's state on the server.

QB.Web.Application.UserData 
QB.Web.Application.SqlErrorEventArgs 

Use the UserData property to get custom information from the server side about the query. To send this information from server, assign an object instance to the SessionStore.Current.Exchange.Data property in the QueryBuilderControl.SQLUpdated event handler. An example of such usage can be found in the "Alternate Names" demo project.

Use the SqlErrorEventArgs property to get information about SQL parsing errors after applying manual changes from text editor. The following fields are available for this property:

QB.Web.Dto.SqlErrorEventArgs = function() {
   this.SQL = null;
   this.Error = null;
   this.ErrorToken = null;
   this.ErrorTokenPos = {pos:0,col:0,line:0};
};

Client Core API

Client core API allows to interchange the query model objects between client and server. Using this API you can change the behavior of existing web controls, or create your own UI using alternate web controls.

QB.Web.Core.ExchangeObject; // Data container for exchange with the server

QB.Web.Core.sendDataToServer(callback);  // Sends QB.Web.Core.ExchangeObject to the server

The main object that allows to interchange between the server-based SQL parsing engine and the client web UI can be accessed via the QB.Web.Core.ExchangeObject property. The QB.Web.Core.sendDataToServer method sends the necessary updates stored in the ExchangeObject property to server.

QB.Web.Core.update(callback);
QB.Web.Core.fullUpdate(callback);
QB.Web.Core.reconnect(callback);

The update method updates the query objects state in case of changing the query on the server side. For example, this method can be called after loading of the previously saved query on the server. User controls get updated automatically after updating the query objects.

The fullUpdate method updates the query objects state and metadata in the database schema tree. This method should be called after re-establishing database connection on the server.

The reconnect method call initiates re-establishing of ASP.NET session and re-initialization of the server part of the component, then updates the visual query representation and reloads metadata in the database schema tree. During this procedure the Init event handler is called on the server. This handler must initialize the QueryBuilder component, establish connection to the database or populate the Metadata Container in some other way.

In other words

  • The update method gets updated query from the server;
  • The fullUpdate method does the same as "update", plus reloads the database schema tree;
  • The reconnect method re-initializes the ASP.NET session among with the component's server side, then performs "fullUpdate".

On receiving the data from server via these methods, the appropriate Core API events are fired to reflect changes in the UI.

Q: Where to find documentation for all of those Dto's referred in the code below?

A: You can find them in the "\web-parts\js\release\dto.generated.js" file included in the installation package.

Query objects model methods

The following methods are introduced to work with the query object model on the client:

/**
 * Add dataSource
 * @param dataSource {QB.Web.Dto.DataSourceDto}
 */
QB.Web.Core.addDataSource(dataSource);

/**
 * Remove dataSource
 * @param dataSource {QB.Web.Dto.DataSourceDto}
 */
QB.Web.Core.removeDataSource(dataSource);

/**
 * Update dataSource
 * @param dataSource {QB.Web.Dto.DataSourceDto}
 */
QB.Web.Core.updateDataSource(dataSource);


/**
 * Add datasouce link
 * @param link {QB.Web.Dto.DataSourceLinkDto}
 */
QB.Web.Core.addLink(link);

/**
 * Remove datasouce link
 * @param link {QB.Web.Dto.DataSourceLinkDto}
 */
QB.Web.Core.removeLink(link);

/**
 * Update datasouce link
 * @param link {QB.Web.Dto.DataSourceLinkDto}
 */
QB.Web.Core.updateLink(link);


/**
 * Add rows to grid
 * @param {QB.Web.Dto.GridRowDto[]} rows
 */
QB.Web.Core.addGridRows(rows);

/**
 * Update rows in grid
 * @param {QB.Web.Dto.GridRowDto[]} rows
 */
QB.Web.Core.updateGridRows(rows);

/**
 * Remove rows from grid
 * @param {QB.Web.Dto.GridRowDto[]} rows
 */
QB.Web.Core.removeGridRows(rows);


/**
 * Update SQL
 */
QB.Web.Core.updateSQL(sql);

After applying the necessary changes using the core API methods listed above, you should call the QB.Web.Core.sendDataToServer method to send data to the server.

Core API events

QB.Web.Core.Events = {
	
	DataSending,
	/** @param data {ExchangeObject} */
	DataReceived,
	/** @param data {object} */
	UserDataReceived,
	/** @param data {string} */
	SQLReceived,
	/** @param data {string} */
	SQLChanged,
	/** @param data {SqlErrorEventArgs} */
	SQLError,
	/** @param data {QB.Web.Dto.QueryStructureDto} */
	QueryStructureChanged,
	/** @param data {Items: string[], LastItemId} */
	MessagesReceived,
	/** @param data {QB.Web.Dto.ContextMenuDto} */
	ContextMenuReceived
};

/**
 * Bind to core event
 * @param event {QB.Web.Core.Events}
 * @param callback
 * @param context
 */
QB.Web.Core.bind(event, callback, context);

QB.Web.Core.NavBar.bind(event, callback, context);
QB.Web.Core.NavBar.Events: {
	/** @param data {QB.Web.Dto.UnionNavBar} */
	DataReceived
}

QB.Web.Core.Canvas.bind(event, callback, context);
QB.Web.Core.Canvas.Events: {
	/** @param data {QB.Web.Dto.CanvasDto} */
	DataReceived
}

QB.Web.Core.Grid.bind(event, callback, context);
QB.Web.Core.Grid.Events: {
	/** @param data {QB.Web.Dto.GridDto} */
	DataReceived
};

QB.Web.Core.CriteriaBuilder.bind(event, callback, context);
QB.Web.Core.CriteriaBuilder.Events: {
	/** @param data {QB.Web.Dto.CriteriaBuilderDto} */
	DataReceived
};

QB.Web.Core.MetadataTree.bind(event, callback, context);
QB.Web.Core.MetadataTree.Events: {
	Loaded,
	/** @param data {QB.Web.Dto.ExchangeTreeDto} */
	DataReceived
};

The QB.Web.Core.MetadataTree.Loaded event is fired when the necessary metadata information is fully loaded on the web page. After that the component is ready to interact with the end-user. You may want to hide or disable the component area on the web page until it's completely loading and ready for user actions.

The SQLChanged event is fired each time the sql is updated SQL query text is received from the server.

The SQLError event is fired when parsing error occurs on the server.

The DataSending and DataReceived events are fired before and after exchanging data packets with the server. The UserDataReceived event is fired after the data exchange if some user data is passed to the client. The data passed to the client is available via the QB.Web.Application.UserData property. The rest of "*Received" events are fired on receiving the appropriate types of data from the server: SQL text (SQLReceived), User messages (MessagesReceived), Context menu (ContextMenuReceived). The rest of the information is handled by appropriate core objects (NavBar, Canvas, Grid, CriteriaBuilder and MetadataTree).


API usage samples

How to add a new table/view to the design pane in javascript through the exposed metadata name?


    var ds = new QB.Web.Dto.DataSourceDto();
    ds.Name = "......"; // Set name
    ds.Alias = "....."; // Set alias
    QB.Web.Core.addDataSource(ds);
    QB.Web.Core.sendDataToServer();

How to add items to the query column list, as well as to change their properties (for example, the WHERE clause)?


    var row = new QB.Web.Dto.GridRowDto();
    row.Select = true;
    row.Expression = ".......";
    QB.Web.Core.addGridRows([row]);
    QB.Web.Core.sendDataToServer();

Events of UI controls

Database Schema Tree:

Binding:

    QB.Web.Application.Tree.bind(event, callback);

Events:

    QB.Web.Tree.Events.TreeSelectNode
    QB.Web.Tree.Events.TreeDoubleClickNode

Design Pane:

Binding:

    QB.Web.Application.Canvas.bind(event, callback);

Events:

    QB.Web.Canvas.Events.CanvasOnAddTable
    QB.Web.Canvas.Events.MetadataObjectAdded
    QB.Web.Canvas.Events.CanvasOnRemoveTable
    QB.Web.Canvas.Events.CanvasOnDropObject
    QB.Web.Canvas.Events.CanvasOnAddTableField
    QB.Web.Canvas.Events.CanvasContextMenuCommand
    QB.Web.Canvas.Link.Events.CanvasLinkOnChanged

Sub-query Navigation Bar:

Binding:

    QB.Web.Application.NavBar.bind(event, callback);

Events:

    QB.Web.NavBar.Events.NavBarBreadCrumbSelectNode
    QB.Web.NavBar.Events.NavBarAction

Datasource controls (objects on the Design Pane):

Binding:

Use the QB.Web.Canvas.Events.MetadataObjectAdded event to bind to datasources.

Events:

    QB.Web.TableObject.Events.TableObjectOnCheckField
    QB.Web.TableObject.Events.TableObjectOnCreate
    QB.Web.TableObject.Events.TableObjectOnClose
    QB.Web.TableObject.Events.TableObjectOnDestroy
    QB.Web.TableObject.Events.TableObjectOnMoved
    QB.Web.TableObject.Events.TableObjectOnUpdated
    QB.Web.TableObject.Events.TableObjectOnLinkCreate
    QB.Web.TableObject.Events.TableObjectOnLinkDelete
    QB.Web.TableObjectField.Events.TableObjectFieldOnCheckField

Query Columns Grid:

Binding:

    QB.Web.Application.Grid.bind(event, callback);

Events:

    QB.Web.Grid.Events.GridOnAddTable
    QB.Web.Grid.Events.GridOnAddTableField
    
    QB.Web.Application.Events.GridOnRowChanging
    QB.Web.Application.Events.GridOnRowChanged
    QB.Web.Application.Events.GridOnRowAdding
    QB.Web.Application.Events.GridOnRowAdded
    QB.Web.Application.Events.GridOnRowRemoved

SQL Text Editor:

Binding:

    QB.Web.Application.bind(event, callback);

Events:

    QB.Web.Application.Events.SQLTextChanged
    QB.Web.Application.Events.RefreshSQLClick

3.2. How to get and set SQL query text?

Getting and setting the SQL query text in Classic ASP.NET (ASPX Vieew Engine)

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

Getting and setting the SQL query text in MVC with Razor view engine

  • To retrieve the query text from the query builder use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
    	QueryBuilder queryBuilder = SessionStore.Current.QueryBuilder;
    	PlainTextSQLBuilder sqlBuilder = SessionStore.Current.PlainTextSQLBuilder;
    
    	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)
    {
    	string textSQL2 = SessionStore.Current.ClientSQL;
    }

    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
    {
    	SessionStore.Current.QueryBuilder.SQL = SessionStore.Current.ClientSQL;
    
    	// execute the query here
    }
    catch (Exception ex)
    {
    	MessageBox.Show(ex.Message, "Parsing error");
    }
    
  • To assign SQL query text to the control use the following code:

    protected void btnSetQuery_Click(object sender, System.EventArgs e)
    {
    	SessionStore.Current.QueryBuilder.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();

3.3. How to edit the query programmatically and update the visual query representation?

The following code samples let change the query programmatically on the server side and update the query builder control to reflect that changes.

Add the following code to your ASPX page:

<!-- Place button on the page -->
<asp:Button ID="btnAddCriteria" runat="server" OnClick="btnAddCriteria_Click" Text="Add criteria" />

<!-- Add button to the UpdatePanel trigger -->
<asp:UpdatePanel ID="UpdatePanel1" runat="server" EnableViewState="false">
	<Triggers>
		<asp:AsyncPostBackTrigger ControlID="btnAddCriteria" />
	</Triggers>
</asp:UpdatePanel>

<!-- Bind to the UpdatePanel update event and call 
     the QB.Web.Application.update() method if panel is updated -->
<script type="text/javascript">
	Sys.WebForms.PageRequestManager.getInstance().add_pageLoaded(PageLoaded);

	function PageLoaded(sender, args) {
		var panelId = "UpdatePanel1";
		if (args == null) return;
		if (args.get_panelsUpdated() == null) return;
		
		for (var i = 0; i < args.get_panelsUpdated().length; i++)
			if (args.get_panelsUpdated()[i].id == panelId) {
				QB.Web.Core.update();
			}
	}
</script>

Place the code to update the query in the button click event handler:

protected void btnAddCriteria_Click(object sender, EventArgs e)
{
	QueryBuilder queryBuilder = (QueryBuilderControl1).QueryBuilder;

	QueryColumnListItem row = queryBuilder.ActiveSubQuery.ActiveUnionSubquery.QueryColumnList.AddExpression("Schema.Table.Field");
	row.ConditionStrings[0] = " = 1";
}

You can find samples of updating the query programmatically in the demo projects included in the installation package and in the following chapter of the Knowledge Book: Programmatic modification of SQL queries.

If you want to update the Criteria Builder control programmatically, modify the QueryTransformer object on the server side and use the QB.Web.Application.CriteriaBuilder.update() method to update the control. Sample code to work with the QueryTransformer can be found in the Query Results demo and in this article: Changing query with the QueryTransformer.

3.4. How to prompt the end-user for query parameters?

This task is illustrated in the Query Results demo project that included in the installation package. Below are the step to take in order to cope with this task.

  1. Use the QB.Web.Application.getQueryParams(callback) asynchronous function to receive the list of parameters in callback function.
  2. Prompt the end user for parameter values.
  3. Use the QB.Web.Application.setQueryParamValues(newParams, callback) function to pass the list of parameter values to the server.
  4. Handle the QueryBuilderControl.OnQueryParamValuesChanged event to receive the list of parameter values.
  5. Execute the query and display results to the end-user.

3.5. How to make AQB controls resizable by the end-user?

Add the following CSS into the HEAD section to unlock fixed-size block and set default height of it's container:

<style>
    #qb-ui-tree-view { height: auto; }
    #qb-ui { height: 600px; }
</style>

And the following script add before the end of the BODY tag:

<script>
   $(function () {
      $('#qb-ui').resizable({ handles: "s" });
      $('#qb-ui-canvas').resizable({ handles: "s" });
      $('#qb-ui-tree-view').resizable({ handles: "e" });
   });
</script>

3.6. 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;
}

3.7. How to handle events on the client side using JavaScript?

There is the "Client event handle" demo project that included in the installation package to illustrate this functionality.

Below is the sample code from this demo:

	<script type="text/javascript" language="javascript">
		OnApplicationReady(function () {
			$(QB.Web.Application).bind(QB.Web.ApplicationEvents.beforeDataExchange, beforeDataExchange);
			$(QB.Web.Application).bind(QB.Web.ApplicationEvents.afterDataExchange, afterDataExchange);
		});

		function beforeDataExchange(sender, e) {
			alert("Before data exchange");
		}

		function afterDataExchange(sender, e) {
			console.log(sender, e);
			alert("After data exchange");
			if (e.SQL) alert("New SQL:" + e.SQL);
		}
	</script>

3.8. How to let 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"
/>

3.9. How to hide specific 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>

3.10. How to hide some parts of the Sub-query navigation bar?

You can set the "display: none" attribute to appropriate styles in CSS file to hide specific parts of the Sub-query navigation bar:

/* Hide CTE panel */
#qb-ui-canvas-navbar-cte-panel { display:none; }

/* Hide sub-queries panel */
#qb-ui-canvas-navbar-subquery-panel { display:none; }

/* Hide unions panel */
#qb-ui-canvas-navbar-union-panel { display:none; }

Use the following code to hide titles above the sub-query navigation bar buttons:

/* Hide titles in sub-query navigation bar */
#qb-ui-canvas-navbar .ui-dialog-titlebar  { display:none; }

3.11. How to turn on logging?

Add the following code to your "web.config" file:

	<configuration>
	 <configSections>
	   <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>

and

	<configuration>
	  <log4net>
	   <root>
	     <level value="ALL"/>
	     <appender-ref ref="Logger"/>
	   </root>
	   <appender name="Logger" type="log4net.Appender.RollingFileAppender">
	     <lockingmodel type="log4net.Appender.FileAppender+MinimalLock"/>
	     <file value="Log.txt"/>
	     <appendtofile value="true"/>
	     <rollingstyle value="Size"/>
	     <maxsizerollbackups value="10"/>
	     <maximumfilesize value="100MB"/>
	     <staticlogfilename value="true"/>
	     <filter type="log4net.Filter.LevelMatchFilter">
	       <!-- ALL DEBUG INFO WARN ERROR FATAL OFF -->
	       <level value="ALL"/>
	     </filter>
	     <layout type="log4net.Layout.PatternLayout">
	       <conversionPattern value="%-5p [%d] %m%n"/>
	     </layout>
	   </appender>
	 </log4net>

3.12. How to setup grouping type and initial selection of objects in the Database Schema Tree?

Grouping of objects within the Database Schema Tree is defined by the Metadata Structure object. It can be accessed via the QueryBuilder.MetadataStructure property.

To change objects grouping in the Database Schema Tree, set the QueryBuilder.MetadataStructureOptions.GroupBy* properties in the Init event handler.

Attention: The rest of the article describes usage of the old Database Schema List control (AQB:ObjectTree), it is not applicable to the new Database Schema Tree (AQB:ObjectTreeView).

To instruct the component to expand specific group on loading, use the ObjectTree.Path property. This property gets and sets a slash-separated string of group names according to the grouping defined by Metadata Structure "GroupBy*" properties. The trailing asterisk item in this string instructs to show all objects from this group. Usage of the asterisk item is allowed only if the ShowAllItemInGroupingSelectLists property is set to True. Non-existing group names assigned to the ObjectTree.Path property will be ignored.

Examples:

  • Instructuing to show tables the "Production" schema of "AdventureWorks" database:
    ObjectTree.Path = "/AdventureWorks/Production/Tables"
  • Instructuing to show all objects the "dbo" schema of "Northwind" database (regargless of absense or presence of further grouping by object type):
    ObjectTree.Path = "/Northwind/dbo/*"
  • Instructuing to show all objects from the Metadata Container:
    ObjectTree.Path = "*"

3.13. How to get the Query Columns Grid displayed correctly if it's placed on invisible part of the page?

Q: The Query Columns Grid gets displayed incorrectly being placed on initially invisible part of the page. Is there any solution for this problem.

A: Yes, there is. It was introduced in the version 2.10.

Add the "QB.Web.Application.resetLayout()" method call to the event that is fired right after displaying the component. This method resets the Query Columns Grid width and redraws it.

For example, placing the component to a tab of the Telerik RadTabStrip control, you should add this code to the OnClientTabSelected event handler of RadTabStrip control.


<script>
    function OnClientTabSelected(sender, eventArgs) {
        QB.Web.Application.resetLayout();
    }
</script>

<tel:RadTabStrip ID="tplTabMain" runat="server" MultiPageID="tplMultiPageMain" SelectedIndex="0" CausesValidation="false" SkinID="TabStrip" OnClientTabSelected="OnClientTabSelected" >

3.14. 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):

    <link href="/css/themes/jquery-ui.css" type="text/css" rel="stylesheet" />
    <link href="/css/qb-theme.css" type="text/css" rel="stylesheet" />

3.15. 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. To avoid it, please replace this assembly in your project with one from the "assemblies\third-party\Net35\" directory in the component's installation folder.

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.

 

4. Active Query Builder Java Edition

4.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.

4.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.

4.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>

4.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.

5. Active Query Builder VCL Edition

5.1. How to get fields from all objects that were used in the currently active sub-query?

Below is the code sample of getting list of fields from all objects that were used in the currently active sub-query. This list is identical to the content of drop-down in inline editor for Expression column in the Query Columns Grid.

procedure TForm1.SpeedButton4Click(Sender: TObject);
var 
	availableFields:TStringList;
	usq:TacUnionSubQuery;
	datasourceList:TObjectList;
	i,j:integer;
	datasource:TacDatasource;
	datasourceNameInQuery:WideString;
	fieldName:WideString;
	field:TacMetadataField;
begin
	usq:=queryBuilder.ActiveSubQuery.ActiveUnionSubquery;

	availableFields:=TStringList.Create;
	datasourceList:=TObjectList.Create(false);
	try
		// get list of all datasources
		usq.FromClause.GetDatasources(datasourceList);
		for i:=0 to datasourceList.Count-1 do
		begin
			datasource:=TacDatasource(datasourceList[i]);
			datasourceNameInQuery:=datasource.NameInQuery;

			for j:=0 to datasource.Fields.Count-1 do
			begin
				field:=datasource.Fields[j];

				fieldName:='';

				if queryBuilder.UseAltNames and (field.AltName<>'')
				then fieldName := field.AltNameId.SimpleSQL(queryBuilder.SQLContext.SQLBuilderExpression);

				if fieldName='' then fieldName:=field.Name.QualifiedName;

				availableFields.Add(datasourceNameInQuery+'.'+fieldName);
			end;
		end;

		ShowMessage(availableFields.Text);
	finally
		datasourceList.Free;
		availableFields.Free;
	end;
end;

5.2. How to get and set width of columns of the Query Columns grid control?

The TacQueryBuilder.SelectListOptions.*ColumnWidth set of properties can be used to set the default width of columns.

To get or set the actual width of columns in runtime (it can be changed by the end-user during the query building process), use the GetGeneralColumnsWidth, GetConditionsColumnsWidth, SetGeneralColumnsWidth and SetConditionsColumnsWidth methods of the TacQueryBuilderSelectListControl class representing the grid control. You can get access to this control for the currently active sub-query as follows:

grid := qb.ActiveSubQuery.ActiveUnionSubQuery.SelectList.SelectListControl 
    as TacQueryBuilderSelectListControl;

5.3. How to execute a query in another thread?

All metadata providers inherit the following methods to support asyncronous query execution:

// returns true if this metadata provider is capable to create copies of Connection objects
function SupportAsyncExecSql:boolean; virtual;
 
// methods to create and free a copy of Connection object: 
function CreateConnectionCopy(AOwner: TComponent): TCustomConnection; virtual; 
procedure FreeConnection(AConnection: TCustomConnection); virtual; 

// event is fired after creation of each copy of Connection object
property OnCustomizeConnectionCopy: TacConnectionEvent;

// asyncronous query execution methods: 
function SupportAsyncExecSql: boolean; virtual; 
function CreateSqlAsync(ATag:TObject):IacAsyncSql;

// creates and executes a query asyncronously and returns unidirectional readonly dataset
function ExecSQLDatasetURAsync(const ASQL:WideString; AParams:TParams=nil):IacAsyncSql; virtual;

The simplest way to create a copy of connection and asyncronously execute a query (unidirectional, readonly) against it, is to call the ExecSQLDatasetURAsync method. If you want to execute a query in another way, you should call the CreateSqlAsync method and tune the properties of the IacAsyncSql interface returned by this method.

A copy of connection that's created inside the ExecSQLDatasetURAsync and CreateSqlAsync methods has the same values of all published properties as the original connection object, but sometimes it's needed to perform additional tuning of connection for it's proper functioning. For example, this might be needed to specify database password that was not initially defined in connection properties. To fix this, you can define a handler for the OnCustomizeConnectionCopy event and tune the connection properties the way you need.

The IacAsyncSql interface provides the following properties and methods:

property Sql: WideString;
property Params: TParams;
property ReturnResults: boolean;
property Unidirectional: boolean;
property ReadOnly: boolean;

property MetadataProvider: TacBaseMetadataProvider; 
property Connection: TCustomConnection; 
property Dataset: TDataset;    

procedure Run; 
property State: TacTaskState; 
// TacTaskState=(actCreated, actRunning, actCompleted, actCanceled, actFaulted);
 
property CanCancel: boolean; 
function Cancel:boolean; // cancels the query
property Exception: Exception; // read it if the query state is actFaulted
function Wait(ATimeout:Cardinal=INFINITE):boolean;  

// The OnStarting and OnStopped events 
property OnStarting: TNotifyEvent; 
property OnCompleted: TNotifyEvent;
property OnFaulted: TNotifyEvent;
property OnCanceled: TNotifyEvent;
property OnStopped: TNotifyEvent;

5.4. 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"

6. Active Query Builder ActiveX Edition

6.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".

6.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);
}

6.3. List of possible SQLSyntax values

Below is the full list of values for the ActiveQueryBuilderX.SQLSyntax property ("xs" constants).

Long aqbSyntax_xsAuto = 0x00000002
Long aqbSyntax_xsUniversal = 0x00000004

Long aqbSyntax_xsSQL89 = 0x00000001
Long aqbSyntax_xsSQL92 = 0x00000000
Long aqbSyntax_xsSQL2003 = 0x00000003

Long aqbSyntax_xsMSSQL7 = 0x0000000A
Long aqbSyntax_xsMSSQL2000 = 0x0000000B
Long aqbSyntax_xsMSSQL2005 = 0x0000000C
Long aqbSyntax_xsMSSQL2008 = 0x0000000D
Long aqbSyntax_xsMSSQL2012 = 0x0000000E

Long aqbSyntax_xsMySQL3 = 0x00000014
Long aqbSyntax_xsMySQL40 = 0x00000015
Long aqbSyntax_xsMySQL41 = 0x00000016
Long aqbSyntax_xsMySQL5 = 0x00000017

Long aqbSyntax_xsPGSQL = 0x0000001E

Long aqbSyntax_xsSQLite = 0x00000028

Long aqbSyntax_xsOracle7 = 0x00000032
Long aqbSyntax_xsOracle8i = 0x00000033
Long aqbSyntax_xsOracle9i = 0x00000034
Long aqbSyntax_xsOracle10g = 0x00000035
Long aqbSyntax_xsOracle11g = 0x00000036

Long aqbSyntax_xsInformixDS8 = 0x0000003C
Long aqbSyntax_xsInformixDS9 = 0x0000003D
Long aqbSyntax_xsInformixDS10 = 0x0000003E
Long aqbSyntax_xsInformixDS11 = 0x0000003f

Long aqbSyntax_xsJet3 = 0x00000046
Long aqbSyntax_xsJet4 = 0x00000047
Long aqbSyntax_xsAccess97 = 0x00000048
Long aqbSyntax_xsAccess2000 = 0x00000049
Long aqbSyntax_xsAccessXP = 0x0000004A
Long aqbSyntax_xsAccess2003 = 0x0000004B

Long aqbSyntax_xsFirebird1 = 0x00000050
Long aqbSyntax_xsFirebird1_5 = 0x00000051
Long aqbSyntax_xsFirebird2 = 0x00000052

Long aqbSyntax_xsInterbase = 0x0000005A

Long aqbSyntax_xsSybaseASA = 0x00000064
Long aqbSyntax_xsSybaseASE = 0x00000065

Long aqbSyntax_xsDB2 = 0x0000006E

Long aqbSyntax_xsTeradata = 0x00000078