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

Hello,

I don't agree that the current behavior is not correct. Active Query Builder only tries to correct user errors, not to guess what they want. You may expect one behavior, other users may expect something else. The good news is that you always can manually format in the way you want.

Avatar
0
Andrew Kennard

I don't agree :)

If I have these two exact strings

'Rose Cottage'
34 High Street

and want to use them in an In clause

Yes it can be typed in at the moment but if (as I hope) the option to make these cells read-only in the future then the way NewExpression parses the string it is given means this operation would not be possible using In

I cannot pass a string to NewExpression that gives the correct result (try it yourself)

ie what string do I (the developer) pass to NewExpression to get this result in the cell

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

Avatar
0
Andrey Zavyalov

You should type exactly "In ('''Rose Cottage''','34 High Street')" in the cell. Active Query Builder doesn't touch valid SQL expressions.

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

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

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

and here is another

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

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

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