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
Andrew Kennard

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 ?

Avatar
0
Sergey Kraikov

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

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

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.

Avatar
0
Andrew Kennard

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

Avatar
0
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:

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


Avatar
0
Andrew Kennard

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

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

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

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

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