Active Query Builder support area

ActiveX API questions

Avatar
Andrew Kennard
  • updated
  • Completed

Hi

I am now most of the way through writing my wrapper class and have a few more questions (some of which I have patrially asked before)
LoginPrompt - Setting this to true I was expecting a SQL login box to appear but it does not seem to work like that ?
ConnectionTimeout and CommandTimeout - getting these values after connecting to the database seem to return 0, what should it be as setting them to 0 seems to cause problems (obviously!)
SyncSQL - how should this be used ? There is also the SQL propety how do these two differ ?

You mentioned on another thread OnCustomExpressionBuilder returns a handle to ExpressionBuilderParams but when is this event fired ? I can see there is a UseCustomExpressionBuilder property but not quite sure how this works as I can't see anything in the samples ?

Getting CurrentLanguage after connection to the db returns -1 which i'm guessing is unknown ? trying to set it to this causes an error. What are the valid values ?
What is the difference between the TreeOptions object and the MetadataTreeOptions some of which appear to be the same eg TreeColor TreeWidth (do these two access the same unterlying values?)
Here are my guesstimates on what each of the objects in the ActiveX are for. Please can you verify if I am correct.
ActiveQueryBuilderX - the main parent object
TreeOptions - for controlling the appearance of the query tree pane on the left
SelectFormat - affects the formatt8ing of the SQL property when it is collected from ActiveQueryBuilderX
ParameterInterface - ??
MetadataFilter - collection of MetadataFilterItem
MetadataFilterItem - Metadata for an individual filter item. How should ObjectMask and SchemaMask be set ?
MetadataContainer - container for the metadata aboutr the currently connected database ??
MetadataObject - I assume these are tied to the above container not quite sure how ?
MetadataFieldsList - I assume these are tied to the above container not quite sure how ?
MetadataField - I assume these are tied to the above container not quite sure how ?
MetadataDatabases - I assume these are tied to the above container not quite sure how ?
QueryStatistics - basic stats about number of columns etc in current SQL statment
StatsFromObjectsList - collection of StatsFromObject
StatsFromObject - the individual object with type and access to its metadata object
StatsUsedColumnsList - collection of StatsUsedColumn
StatsUsedColumn - as StatsFromObject but only for columns in the SQL query ??
MetadataRelationsList - collection of MetadataRelation
MetadataRelation - the relationship (join) info for this field ??
MetadataFieldNamesList - collection of string representing the fieldnames used in the SQL query ?
MetadataTreeOptions - More options for uery tree pane on the left, not sure how this differs from TreeOptions
Localizer - database language details
StatsSelectedColumn - not quite sure what this is for
StatsSelectedColumnsList - collection of StatsSelectedColumn
ExpressionBuilderParams - For the custom expression builder, again as above not quite sure how it works
SelectListOptions - for selecting defaluts for the Query Building Area grid ?
SupportedSyntaxes - not sure ? sql syntaxes supported by the connected database ??
BeforeAddDatasourceParams - to be used with OnBeforeAddDatasource but not sure what for ?
AfterAddDatasourceParams - to be used with OnAfterAddDatasourcebut not sure what for ?
BeforeDeleteDatasourceParams - to be used with OnBeforeDeleteDatasource but not sure what for ?
MetadataDefaultSchemas - collection of strings representing the default schema names for the currently connected database
FieldsListOptions - controls the appreance of the tabels/fields pane on the right ?
LinkOptions - controls options for auto creating links between tables ?
FieldsListMarkColumnOptions - controls the appreance of the tabels/fields pane on the right ?
FieldsListNameColumnOptions - controls the appreance of the tabels/fields pane on the right ?
FieldsListTypeColumnOptions - controls the appreance of the tabels/fields pane on the right ?
FieldsListDescriptionColumnOptions - controls the appreance of the tabels/fields pane on the right ?
Strings - user def collection of strings, not quite sure of use
QueryTransformer - ??
FilterCondition - a string representing an individual filter condition
FilterConditionJunction - collection of FilterCondition
SortedColumn - Info from the Columns pane
SortedColumnsList - collection of SortedColumn
OutputColumn - Info from the Columns pane
OutputColumnsList - collection of OutputColumn
SelectedColumn - Info from the Columns pane
SelectedColumnsList - collection of SelectedColumn


Replies 25
Avatar
0
Andrey Zavyalov, PM

Hello,

Although there is a lack of documentation for the ActiveX version, there are many articles that explain the component in general. The ActiveX Edition is very close to the VCL edition, it just lacks the means of editing of internal SQL query objects. So, I hope I can shed some light on this by pointing you to read some of the articles.

I. Metadata Container.

MetadataConainer, MetadataDatabases, MetadataDefaultSchemas, MetadataObject, MetadataFieldList, MetadataField, MetadataRelation, MetadataRelationList, MetadataFieldNamesList - are the objects to store metadata extracted from the current database connection.

Brief description of involved classes:

MetadataContainer - collection of MetadataObjects (tables, views, etc). Also holds a connection of MetadataDatabases and MetadataDefaultSchemas.

MetadataObject holds MetadataFieldList (collection of MetadataField objects) and MetadataRelationList (collection of MetadataRelation objects, i.e. foreign keys)

MetadataRelation has two collections of fields: for holding field lists of referred and referring tables (two objects of MetadataFieldNamesList class).

A series of articles about loading metadata in Active Query Builder.

MetadataFilter, MetadataFilterItem - used for partial loading of Metadata. The article about metadata filtration.

II. Query Transformer

QueryTransformer, FilterCondition, FilterConditionJunction, SortedColumn, SortedColumnsList, OutputColumn, OutputColumnsList, SelectedColumn, SelectedColumnsList: read the article about Query Transformer with examples for ActiveX version.

III. ActiveQueryBuilderX options

FieldsListOptions - controls the appearance of the field lists in datasources on the design pane.

FieldsListMarkColumnOptions, FieldsListNameColumnOptions, FieldsListTypeColumnOptions, FieldsListDescriptionColumnOptions - options of the columns of the field lists in datasources on design pane.

LinkOptions - controls the appearance of links between tables

SelectListOptions - options of the Query Building Area grid

TreeOptions - options of the Query Structure Tree on the left

MetadataTreeOptions - options of the Metadata Tree on the right.

Regarding the description of individual properties in *Options property groups: you can find the description for most of them in the documentation for .NET version: these options have much in common in all editions of Active Query Builder.

IV. Query Statistics

QueryStatistics - basic stats about the number of columns, etc in the current SQL statement

StatsFromObjectsList - collection of StatsFromObject
StatsFromObject - the individual object with type and access to its metadata object
StatsUsedColumnsList - collection of StatsUsedColumn
StatsUsedColumn - as StatsFromObject but only for columns in the SQL query ??

StatsSelectedColumn - not quite sure what this is for

StatsSelectedColumnsList - collection of StatsSelectedColumn

The article about Query Statistics.

V. Custom Expression Builder.

When the UseCustomExpressionBuilder property is turned on, the ellipsis button appears in this column while editing its content. By clicking this button, the component fires the OnCustomExpressionBuilder event. The handler for this event should provide the way to edit this column's content and return the result, getting all the information about this event and setting the result SQL expression text using the ExpressionBuilderParams object.

Will you be implementing this functionality in your project? I mean that you'll have to create a dialog to edit SQL expressions with some additional functionality that the user doesn't have using the inline editor.

VI. Other properties

Localizer, Strings: article about localizing Active Query Builder.

SupportedSyntaxes - list of all SQL syntaxes supported by the component (for example, if you want to make a list of them). Values can be applied to the SQLSyntax property.

LoginPrompt - it instructs to show login prompt on connecting to the database, not immediately after setting it to True.

ConnectionTimeout and CommandTimeout - we will check why they are set to zero and let you know.

SyncSQL - this property is obsolete, please don't use it.

VII. Events.

BeforeAddDatasourceParams - to be used with OnBeforeAddDatasource - an event that fired before adding a datasource on the design pane.

AfterAddDatasourceParams - to be used with OnAfterAddDatasource - an event that fired after adding a datasource on the design pane.
BeforeDeleteDatasourceParams - to be used with OnBeforeDeleteDatasource an event that fired before deleting a datasource on the design pane.

I don't really think that you will be in need of these events, so I'm wondering, why you have to implement these events in your wrapper?

Let me know if you need any additional information.

 

Avatar
0
Andrew Kennard

Thanks VERY VERY much for your comprehensive reply I shall digest and get back to you if I have any more questions and I look forward to hearing about the few outstanding items like ConnectionTimeout etc Thanks again

Avatar
0
Andrew Kennard

Hi


I'm getting on quite well but a few questions so far ....


Localizer

I can collect a list of the languages OK

This works - ActiveQueryBuilderX1.Localizer.CurrentLanguage = 0

This raises an error List index out of bounds (-1) - ActiveQueryBuilderX1.Localizer.CurrentLanguage = -1
I probably wont ever need to load a custom language but I assue the file format for the .lng file can be found somewhere ?

I dont see any mention of the Strings object in the Localizer article ? but it's probably not too important as it seems to be a simple aray type object wit simnple methods for Count,Item,Remove,Clear,Add


Custom Expression Builder

I have successfully trapped the OnCustomExpressionBuilder event and linked it to the ExpressionBuilderParams object. Getting the OldExpression simply retuns the column name so I assume any dialog I create to collect information when I set it back in the NewExpression i'm simply replacing the column name part of the SQL select statement. eg Instead of Table.AmountPaid it could be SUM(Table.AmountPaid) etc etc ?


Filtering

I cant seem to get this to work and i've had several tries

In my language my code looks like this

Set ComShowUserTables of (phoaqbxMetadataTreeOptions(Self)) to False// this works
Set ComShowUserProcedures of (phoaqbxMetadataTreeOptions(Self)) to False // this works
Get qbxMDFAdd to v// gets a valid looking handle
Set pvComObject of (phoaqbxMetadataFilterItem(Self)) to v
Set ComExclude of (phoaqbxMetadataFilterItem(Self)) to True // no errors so pvComObject must be good
Set ComApplyForViews of (phoaqbxMetadataFilterItem(Self)) to True
Set ComObjectMask of (phoaqbxMetadataFilterItem(Self)) to "vw%"

At this point I am expecting to see just a tree of views but without any that start "vw%" .... but they are still in there

If at the end I send Send ComRefreshMetadata then I just end up with an empty tree. Send ComRefreshMetadataAsync seems to have no effect

I see there is an event OnComFilterView String llViewName Boolean ByRef llVisible Which I thought might get called as the metadata loads but that appears not to be the case

Not quite sure what i'm not getting quite right here


Avatar
0
Andrey Zavyalov, PM

Localizer: the bug with assigning -1 will be fixed in the next build. This will undo any localization and revert to original texts. The samples of .lng files can be found in the Languages folder.

Strings: this object doesn't relate to localizer. It's a read-only collection of strings for completion lists, some of the ExpressionBuilderParams properties are of the Strings class.

Metadata Filters: below is the Visual Basic code that works just fine: 

     ActiveQueryBuilderX1.TreeOptionsMetadata.ShowUserTables = False
    ActiveQueryBuilderX1.TreeOptionsMetadata.ShowUserProcedures = False
    
    Dim fi As ActiveQueryBuilderXControls.aqbxMetadataFilterItem
    Set fi = ActiveQueryBuilderX1.MetadataFilter.Add
    fi.Exclude = True
    fi.ApplyForViews = True
    fi.ObjectMask = "O%"
    
    ActiveQueryBuilderX1.RefreshMetadata

After executing of this code you will see only views, that doesn't start with 'O'.

May be you've mixed up with case? Case sensitivity is set by the MetadataFilterItem.ObjectMaskCaseSensitive property, try to set it to False.

Also please note that it's good to create metadata filters before connecting to the database. This way you can speed up the metadata loading process. Calling the RefreshMetadata after connecting to database leads to loading metadata one more time.

ParameterInterface: This is possibly one of the improper property names. This is the class that represents information about single parameter in the query. To get information about parameters you should read the ParametersCount property and then read the Parameter indexed property: you will get all information about parameter in an object of ParameterInferfce class.

Avatar
0
Andrey Zavyalov, PM

Hello,

The last question was about timeouts. We can not reproduce the problem. Can you send us a piece of code after execution of which the properties become zero?

Avatar
0
Andrew Kennard
Hi OK I tried a few more things in Dataflex on the but no luck I'm not a VB programmer but I have managed to setup a VS2010 project and import your control onto a form my sample code is this 
Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        AxActiveQueryBuilderX1.TreeOptionsMetadata.ShowUserTables = False
        AxActiveQueryBuilderX1.TreeOptionsMetadata.ShowUserProcedures = False

        Dim fi As ActiveQueryBuilderXControls.aqbxMetadataFilterItem
        fi = AxActiveQueryBuilderX1.MetadataFilter.Add
        fi.Exclude = True
        fi.ApplyForViews = True
        fi.ObjectMask = "vw%"

        AxActiveQueryBuilderX1.RefreshMetadata()

        AxActiveQueryBuilderX1.ConnectionString = "Provider=SQLOLEDB; Data Source=MSSQL2014;Trusted_Connection=yes;Initial Catalog=MyDb"
        AxActiveQueryBuilderX1.Connected = True

        Dim i As Int32
        Dim j As Int32

        i = AxActiveQueryBuilderX1.ConnectionTimeout
        j = AxActiveQueryBuilderX1.CommandTimeout
    End Sub

    Private Sub AxActiveQueryBuilderX1_OnBeforeDeleteDatasource(sender As Object, e As AxActiveQueryBuilderXControls.IActiveQueryBuilderXEvents_OnBeforeDeleteDatasourceEvent) Handles AxActiveQueryBuilderX1.OnBeforeDeleteDatasource
    End Sub
End Class
The first two properties work in the sense those things are excluded The filter does not work i.e. the views beginning vw are still in the tree Both i and j are 0 Interestingly the aALlow param of the e object in the DeleteDatasource is True Hopefully this helps Thanks
Avatar
0
Andrew Kennard

Also I have just been struggling a bit with ShowUserTables ShowUserProcedures but I have just worked out that these must be set AFTER their respective TableNodeVisible etc otherwise setting TableNodeVisible seems to reset/ignore the ShowUserTables etc

Avatar
0
Andrew Kennard

With regards the crash problem I was having with BeforeAddDatasourceParams it appears to the same problem as the one with BeforeDeleteDatasource in that llaAllow is false. If I move true to it it works fine

Avatar
0
Andrew Kennard

OK in VB the above filter code only seems to work if it is place AFTER the connection has been opened. I will do some more test in dataflex to see if other properties are affecting it as simply placing it after there does not help

Avatar
0
Andrew Kennard

Not much luck on the Dataflex side so far I have tried removing the line AxActiveQueryBuilderX1.RefreshMetadata() from VB and that cause the filter not to work - understandable

However if I send this in Dataflex I get a blank tree even if I don't set any filters at all. This is not the case in VB if I don't set any filters and send RefreshMetadata the tree still loads as normal