Active Query Builder support area

NewExpression IN condition and escaping outer quotes

Avatar
Andrew Kennard
  • updated
  • Completed

No rush for this, just logging

When setting NewExpression I can't successfully set an In array that INCLUDES START and END single quotes

Yes I appreciate 34 High Street should be enclosed in single quotes after the comma but i'm just using it to emphasize the 'Rose Cottage' part

ie If I set NewExpression to

In (Rose Cottage,34 High Street)

I get

In ('Rose Cottage','34 High Street') Correct


In ('Rose Cottage',34 High Street)

I get

In ('Rose Cottage','34 High Street') Not Correct


In (''Rose Cottage'',34 High Street)

I get

In ('''''Rose Cottage''''','34 High Street') Correct but not what I want


I want to give it this (or what should I give it)

In ('Rose Cottage',34 High Street)

and get this

In ('''Rose Cottage''',34 High Street)


Replies 13
Avatar
0
Andrew Kennard

No problem. It sounded like you were offering to expose something that already existed. If it were me I probably wouldn't invest too much time in second guessing what someone means. After all aqb is a technical tool and some level of technical ability is needed to use it so it's up to the user to enter valid expressions if they are given the opportunity. As I say I think the developer will need a flag they can set to say something is a 'custom expression' so when they get it back in their own dialog they simply load it into a text box and not try and process it in to combo froms and check boxes etc etc

Avatar
0
Andrey Zavyalov, PM

Hello,

We are moving to this, but not very fast. We have already made calculation of SOME types of expressions for Oracle, but still there are a lot of cases that cannot be calculated for this server. Doing reliable type calculation for each database server will take plenty of time.

Avatar
0
Andrew Kennard

Thinking about it, in the future were the developer to offer a free type textbox in their custom criteria dialog a method to check the expression the user types in is valid would be useful

I guess the same would apply to the Expression column too

Avatar
0
Andrew Kennard

OK it looks like the first time I used an IN clause the heuristic analysis caught me out in that in behaved slightly differently to the other conditions.

However I can now see that if I pass ALL the strings in quotes and any quotes ion them escaped what i give to NewExpression remains unchanged

I'll do some more tests but I think I'm not now being fooled by this subtlety

Avatar
0
Andrey Zavyalov, PM

Hello.

The IN clause what the only clause in which we decided to implement heuristic analysis to correct the user's input. In any other cases we simply quote any user's input that is not a valid SQL experssion. I recommend you to pass correct SQL expressions to the NewExpression property to avoid quotation. If you don't know if expression is valid, we can provide a method to check this.

Avatar
0
Andrew Kennard

and here is another

e.aParams.NewExpression = "LIKE '%O'Connor%' OR LIKE '%Thomas%'" ' result Like '%O''Connor%'' OR LIKE ''%Thomas%'

Avatar
0
Andrew Kennard

OK here is one that is not correct

Private Sub AxActiveQueryBuilderX1_OnCustomExpressionBuilder(sender As System.Object, e As AxActiveQueryBuilderXControls.IActiveQueryBuilderXEvents_OnCustomExpressionBuilderEvent) Handles AxActiveQueryBuilderX1.OnCustomExpressionBuilder

e.aParams.NewExpression = "BETWEEN 'O'Connor' AND 'Thomas'" 'result = 'BETWEEN ''O''Connor'' AND ''Thomas''' in the cell


End Sub

Avatar
0
Andrew Kennard

It appears part of my problem was I was doing = In() as opposed to simply In(). I will look into it a bit more later

Thanks for taking the time to look into it

Avatar
0
ActiveDBSoft support team

I cannot reproduce the behaviour you described on the latest v1.26.15.

When I set "In ('Rose Cottage', 34 High Street)" I get "In ('''Rose Cottage''', '34 High Street')" in the condition cell. Seems this is the behaviour you required.

Avatar
0
Andrew Kennard

Yes, but my point is if you give this exact same string to NewExpression you will NOT end up with this in the cell ... which it should, should it not ?

Otherwise you are saying the user can enter expressions in the grid that the developer cannot set when they use a custom expression dialog to generate the string

Just simply give the string to NewExpression and you will see what I mean