Active Query Builder support area

ActiveX API questions

Andrew Kennard

8 years ago
updated 2 years ago
Completed

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

For i = ActiveQueryBuilderX1.MetadataContainer.Count - 1 To 0 Step -1 Dim o As ActiveQueryBuilderXControls.aqbxMetadataObject Set o = ActiveQueryBuilderX1.MetadataContainer.Items(i) If o.Name = "Orders" Then ActiveQueryBuilderX1.MetadataContainer.Delete (i) End If Next i

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%"

Replies 25

Oldest first
Newest first Oldest first

8 years ago

OK again it seems to be these ByRef booleans always being False when they should be True

If in Dataflex I don't set any filters but send RefreshMetaData

And then in the OnFilterView I crudely do this to return true if the name starts vw then the tree is empty apart from the Views that begin vw

Procedure OnFilterView String llViewName Boolean ByRef llVisible

String s

String[] sa

Move (SplitStringToArray(llViewName,'.')) to sa

Move (Left(sa[SizeOfArray(sa)-1],2)='vw') to llVisible

End_Procedure

So it appears when RefreshMetaData is sent all the events OnFilterView OnFilterTable etc all have their llVisible set to false and unless I augment that (by movine True to it) I will end up with an empty tree

Reply Link

Sergey Kraikov

Hello Minieggs,

you wrote:

Both i and j are 0

I realized the default values for ConnectionTimeout and CommandTimeout is 0 (this means no timeout). Changed them to 60 s and 30 s respectively. The fix will be included in the next release.

setting TableNodeVisible seems to reset/ignore the ShowUserTables

TablesNodeVisible/ViewsNodeVisible/ProceduresNodeVisible are obsolete properties from the very old version of component. You should use properties from TreeOptionsMetadata group. Actually, when you write to TablesNodeVisible the supplied value will be written to TreeOptionsMetadata.ShowUserTables.

Not much luck on the Dataflex side

The metadata filtration is applied only at metadata loading time - when you call RefreshMetadata. After metadata loading, any changes in MetadataFilter or filtering events does not affect objects visibility. So, the recommended sequence is 1) customize the filter, 2) call RefreshMetadata

Sorry, I have no glues about booleans.

There are still the last fallback method you could implement: programmatically scan loaded objects after RefreshMetadata and delete the objects you want to hide from the MetadataContainer:

Thanks very much. I shall read and get back to you

I am about to go to a meeting so you won't hear from me until Friday

The fix will be included in the next release.

Will 0 become a valid value ? i.e. no timeout is allowed ?

TablesNodeVisible/ViewsNodeVisible/ProceduresNodeVisible are obsolete properties

OK I will remove these from my wrapper. Obviously all properties/Methods of the ActiveX are exposed to Dataflex/VB so it is tricky to know which are current/depreciated

MetadataFilter - Did you see the same problem as me in VB in the sense you have to set the MetadataFilter AFTER opening the connection ?

The booleans thing is a bit odd but I will work round it unless we can find out what it is

Timeout = 0 means "no limits" (according to ADO docs it should be avoided)

If you customize the filter after connection, you should call RefreshMetadata which clears already loaded metadata and reload them from underlying database using customized filter. Filter works in this scenario, but AQB loads metadata two times.

Could you please confirm your views you want to hide have prefix "vw", not "VW"? Most of the DBMS converts unquoted identifiers to upper case (this is the ANSI SQL standard behaviour), so when you write "create view vw_View ..." you actually get "VW_VIEW" in system catalog.

ObjectMask property is case-sensitive by default.

This code works to EXCLUDE all EXCEPT vw (lowercase)

AxActiveQueryBuilderX1.ConnectionString = "Provider=SQLOLEDB; Data Source=CM-101220-PC\SQL2014AK;Trusted_Connection=yes;Initial Catalog=donorflex11-0"

AxActiveQueryBuilderX1.Connected = True

Dim fi As ActiveQueryBuilderXControls.aqbxMetadataFilterItem

fi = AxActiveQueryBuilderX1.MetadataFilter.Add

fi.Exclude = False

fi.ApplyForViews = True

fi.ObjectMask = "vw%"

AxActiveQueryBuilderX1.RefreshMetadata()

But if I move it BEFORE ConnectionString and remove RefreshMetadata it does not work so I don;t think its a case sensitivity issue ?

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

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

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?

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.

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

fi.ApplyForTables = False ' turn off for tables

fi.ApplyForProcedures = False ' turn off for procedures

fi.ApplyForSynonyms = False ' turn off for synonyms

'AxActiveQueryBuilderX1.RefreshMetadata()

AxActiveQueryBuilderX1.ConnectionString = "...."

End Sub

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