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
Sergey Kraikov

QueryTransformer.StateXml property allows you to save state of the QueryTransformer: filters, sortings, aggregates and limits.

It returns a string in XML format.

When the state is restored, filters and sortings which columns are not present in the current query are automatically deleted.

For example, you have the query

select OrderId, OrderDate from Orders

and add 2 filters:

OrderId > 1000
OrderDate > '2000-01-01'

and then save state, 2 filters will be saved.

Next, you changed the query (for example, unchecked OrderDate in the GUI):

select OrderID from Orders

and then restore StateXml, OrderDate filter will be ignored and only OrderID > 1000 will be applied.

Avatar
0
Andrew Kennard

Thanks. This fix seems to work

ALso I notice a new property has appeared in this version of the activex StateXml ?

Avatar
0
Sergey Kraikov
If you wish to set ConnectionTimeout or CommandTimeout values i'm not sure if it should be done before or after connecting but setting them after causes any table dragged onto the design view to have an empty field list

This issue is fixed in v1.26.11, please update

Avatar
0
Andrew Kennard

I think so with some quirks on the DF side

It seems to work both before and after in VB

BTW you don't appear to need to send refeshmetadata even if it is after ?

In DF it works before

It works after so long as the Boolean issue on the other thread is dealt with ie moving true to llVisible. But as I say on the other thread hopefully they are looking into this

Avatar
0
Sergey Kraikov

So the filtering issue is resolved now?

Avatar
0
Andrew Kennard

I can confirm this works BEFORE the connection in V1.26.10.0 even without the ApplyForTables etc

In both VB and DF

Thanks by the way for the //Obsolete comments that now get passed to the DF wrapper class generator. Very helpful

Avatar
0
Andrew Kennard

Thanks I will my Expand options

Currently this code still shows ALL views (ie not just vw) with the current (old) version I am using. Yes I will update to latest soon but just want you to check to see if you get the same behavior in your latest version first

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 = False
fi.ApplyForViews = True
fi.ApplyForTables = False ' turn off for tables
fi.ApplyForProcedures = False ' turn off for procedures
fi.ApplyForSynonyms = False ' turn off for synonyms
fi.ObjectMask = "vw%"
'AxActiveQueryBuilderX1.RefreshMetadata()

AxActiveQueryBuilderX1.ConnectionString = "...."
AxActiveQueryBuilderX1.Connected = True
End Sub
Avatar
0
Sergey Kraikov
I can see the equivalent for these in the TreeOptionsMetadata but how about TableNodeExpanded etc ? I can see there is a DefaultExpandLevel I assume this applies to all three and you can no longer set them individually or are TableNodeExpanded still "current"

Sorry, but now there are no working options to expand only "Tables" folder, but stay "Views" and "Procedures" collapsed. Currently *NodeExpanded properties do nothing.

I recommend you to use TreeOptionsMetadata.DefaultExpandLevel instead, but it expands all nodes till the specified depth.

Avatar
0
Sergey Kraikov

I just realized: the default value of the filterItem.ApplyFor* properties is True; to filter out only views with prefix "vw" you should write:

Dim fi As ActiveQueryBuilderXControls.aqbx
Set MetadataFilterItemfi = AxActiveQueryBuilderX1.MetadataFilter.Add
fi.Exclude = True
fi.ApplyForViews = True
fi.ApplyForTables = False  ' turn off for tables
fi.ApplyForProcedures = False ' turn off for procedures
fi.ApplyForSynonyms = False  ' turn off for synonyms
fi.ObjectMask = "vw%"

I also note you have fi.Exclude = False in your last code sample.

I checked filtering one more time - it works on metadata loading time. If I customize filters before connection string - it works on connect, if after connect - it requires additional RefreshMetadata.

If you encountered a different behaviour, could you please provide me a small sample project (C# or Visual Basic) where this issue can be reproduced?

Avatar
0
Andrew Kennard

TablesNodeVisible/ViewsNodeVisible/ProceduresNodeVisible are obsolete properties from the very old version of component. You should use properties from TreeOptionsMetadata group

I can see the equivalent for these in the TreeOptionsMetadata but how about TableNodeExpanded etc ? I can see there is a DefaultExpandLevel I assume this applies to all three and you can no longer set them individually or are TableNodeExpanded still "current"

Thanks