Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

  • edulid
    replied
    Hi Blama ,

    thanks, yes, this is a very similar question, indeed. So this is a third approach.

    Approach 3:
    If I modify approach 1 to:
    Code:
    <field name="myField" customSQL="true" customSelectExpression="null">
    and leave the rest:
    Code:
    <operationBinding operationType="fetch" operationId="myOperationId" customFields="myField">
    <whereClause>
    RTRIM(LTRIM($advancedCriteria.myField))) = 'abc' AND ($defaultWhereClause)
    </whereClause>
    ...
    </operationBinding>
    I get:
    Code:
    select * from myTable where RTRIM(LTRIM('def'))) = 'abc' AND (null = '1' AND null IS NOT NULL)
    which clearly returns 0 columns. So approach 3 is not working for me, only approach 2 until now. This approach, though, gives me a useless AND ('1'='1') and seems to be contra Isomorphic 's recommendation in your post and in my post. So I am quite confused.
    Last edited by edulid; 4th Apr 2018, 00:14.

    Leave a comment:


  • Blama
    replied
    Hi edulid,

    I have a similar situation here and solve it with a field and customSelectExpression="null" in order to prevent any SELECT errors like the one you have and/or updates/adds to that field.

    Best regards
    Blama

    Leave a comment:


  • edulid
    replied
    Hi Isomorphic

    you mean the latter approach, i.e.using :
    Approach 1:
    Code:
    <field name="myField" customSQL="true">
    and in the operationBinding:

    Code:
    <operationBinding operationType="fetch" operationId="myOperationId" customFields="myField">
    <whereClause>
    RTRIM(LTRIM($advancedCriteria.myField))) = 'abc' AND ($defaultWhereClause)
    </whereClause>
    ...
    </operationBinding>
    Using this I get:
    Code:
    :Invalid column name 'myField'.
    [ERROR] [builtinds] - 09:08:02.077:XRP2:WARN:RPCManager:Invalid column name 'myField'. - response: {operationId: "custom", clientContext: undef, internalClientContext: undef, context: Obj, transactionNum: 0, httpResponseCode: 200, httpResponseText: "//isc_RPCResponseStart-->[{affectedRows:..."[168], xmlHttpRequest: [object XMLHttpRequest], transport: "xmlHttpRequest", status: -1, clientOnly: undef, httpHeaders: Obj, isStructured: true, callbackArgs: null, results: Obj, affectedRows: 0, data: "Invalid column name 'myField'.", invalidateCache: false, isDSResponse: true, queueStatus: -1}
    which is true since the field "myField" is non-existent in the database.

    Approach 2: If I return to my original first approach, i.e.. *without*:
    Code:
    <field name="myField" customSQL="true">
    and *with*

    Code:
    <operationBinding operationType="fetch" operationId="myOperationId">
    <whereClause>
    RTRIM(LTRIM($advancedCriteria.myField))) = 'abc' and ($defaultWhereClause)
    </whereClause>
    ...
    </operationBinding>
    then I get no error, but in my sql I get:
    Code:
    select * from myTable where RTRIM(LTRIM('def''))) = 'abc' AND ('1'='1')
    This does not give any error. And this is semantically correct (AND true). But is this the correct way to do it? You say no, the other approach is the correct one: but the other approach gives me the error I mentioned.

    Thanks
    Last edited by edulid; 3rd Apr 2018, 23:45.

    Leave a comment:


  • Isomorphic
    replied
    Looks as though you've answered your own question - yes that latter approach is correct.

    Leave a comment:

Working...
X