NewExpression IN condition and escaping outer quotes

Answered

Comments

13 comments

  • Avatar
    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.

    0
    Comment actions Permalink
  • Avatar
    Minieggs

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

    0
    Comment actions Permalink
  • Avatar
    Andrey Zavyalov

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

    0
    Comment actions Permalink
  • Avatar
    Minieggs

    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

    0
    Comment actions Permalink
  • Avatar
    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.

    0
    Comment actions Permalink
  • Avatar
    Minieggs

    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

    0
    Comment actions Permalink
  • Avatar
    Minieggs

    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

    0
    Comment actions Permalink
  • Avatar
    Minieggs

    and here is another

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

    0
    Comment actions Permalink
  • Avatar
    Sergey Smagin (Product manager)

    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.

    0
    Comment actions Permalink
  • Avatar
    Minieggs

    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

    0
    Comment actions Permalink
  • Avatar
    Minieggs

    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

    0
    Comment actions Permalink
  • Avatar
    Sergey Smagin (Product manager)

    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.

    0
    Comment actions Permalink
  • Avatar
    Minieggs

    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

    0
    Comment actions Permalink

Please sign in to leave a comment.