Active Query Builder support area

QueryTransformer

Avatar
Andrew Kennard
  • updated
  • Completed

Using the VB ActiveX example at the bottom of this page

http://www.activedbsoft.com/helpdesk/index.php?pg=kb.page&id=95

I'm trying to create a DF version and am struggling a bit with a couple of things

t.Filters.Clear

I can get the Filters property if the QT but which object is it FilterJunction etc ?

t.Where column0.Not_Equal(100)

The Where of the QT returns a variant in DF what if anything should I be doing with this ?

My first attempt hasn't changed anything after the EndUpdate but I'm sure i've not got things quite right

Thanks

Replies 7
Avatar
0
Sergey Kraikov

I've replaced the basic ActiveX API help file to the CHM format in the new version 1.26.12.

I've also added all Interfaces, Properties and Methods descriptions to the type library.

If you give OutputColColumnByName an invalid column name you get a COM error. Maybe this should never happen in terms of the code but it does seem a little drastic rather than simply returning a null handle that can be tested for rather than an exception ?

OutputColumn.ColumnByName throws an error if a column is not found. This is made to raise errors on trying to construct incorrect filters.

There is the OutputColumn.FindColumn property with the same functionality that does not throw an error, but returns NULL if a column is not found.

Avatar
0
Andrew Kennard

If I do this

Get ComLessEqual of (phoaqbxOutputColumn(Self)) "C" to v

Get qbxTransWhere v to v

Get ComGreaterEqual of (phoaqbxOutputColumn(Self)) "A" to v

Get qbxTransWhere v to v

These two conditions are OR'd

How would I AND them or do I just need to pass the full string to Condition ?

(Yes I appreciate there is a Between just a simple example of two thing I want ANDed)

Avatar
0
Andrew Kennard

Ah ! Thats what is was !

Separate from that

Getting the Filters property from QT seems to give me a valid handle but when I assign it to an FCJ object and call it's methods I just get COM errors

COM object method invocation error. Object does not support this method or property. Possibly incorrect IDispatch interface pointer attached to VDF proxy object.

Which suggests I've assign the handle to the wrong type of object or it's not the handle I should have got in the first place

Ignore above I found the error in my wrapper class

Also

If you give OutputColColumnByName an invalid column name you get a COM error. Maybe this should never happen in terms of the code but it does seem a little drastic rather than simply returning a null handle that can be tested for rather than an exception ?


Avatar
0
Sergey Kraikov

The QueryTransformer is the "separate part" of the component, it does not modify the loaded original query. The transformed query can be read from queryBuilder.QueryTransformer.SQL property (the original query + all transformations applied).

It is intended to provide helpers for sorting, filtering and paging of the original query data displayed in the grid control.

If you want to display modified query in the QueryBuilder GUI you should load transformed query to the SQL property:

string transformedQuery = queryBuilder.QueryTransformer.SQL;
queryBuilder.SQL = transformedQuery

Avatar
0
Andrew Kennard

The FormattedSQL property was unchanged

This bit of code is a mixture of DF and my wrapper but if you look at it as purely pseudo code are these thre right steps ?

Send qbxTransBeginUpdate

Get qbxTransColumns to v
Send Bind_Object (RefFunc(qbxTransColumns)) (phoaqbxOutputColumnsList(Self))
Get qbxOutputColColumnByName "MyColumn" to v
Set pvComObject of (phoaqbxOutputColumn(Self)) to v
Get qbxTransFilters to v
Set pvComObject of (phoaqbxFilterConditionJunction(Self)) to v
Send qbxFilterConClear
Get ComLessEqual of (phoaqbxOutputColumn(Self)) "C" to v
Get qbxFilterConAdd v to v
Get qbxTransWhere v to v
Send qbxTransEndUpdate

v always contains a handle so I think each of these work but I'm obviously not quite quite joining the right objects together in the right way to use this funcionality

In summary ...

Get the columns from the QT

assign them to OutputColumnsList object

Find the column i'm interested in

Assign that column to the OutputColumn object

get the filters object from the QT

assign it to the FilterConditionJunction object

send clear to the FCJ

get a less equal to of the OutputColumn

add that returned handle to the FCJ

add that returned handle from the FCJ to the QT where


Avatar
0
Sergey Kraikov

queryTransformer.Filters value type is IFilterConditionJunction - collection of child filter conditions

All methods of the Query Transformer returns same Query Transformer instance. This is useful to "fluent" transformation construction:

queryTransformer.Where(...).OrderBy(...).Skip(n).Take(k)
string transformedQuery = queryTransformer.SQL
// sending transformedQuery to the database server


Avatar
0
Andrey Zavyalov, PM

So, what did you read from the t.SQL property after your manipulations? Did you change anything in that sample? If yes, can you provide the modified source code in full? Note that there shouldn't be any changes in the UI, it is only suitable for programmatic SQL changes.