Announcement

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

  • edulid
    started a topic Correct way to pass a value to a customSql statement

    Correct way to pass a value to a customSql statement

    I want to pass a custom value to the <whereClause>. This field is NOT a field in my table. So it should only be used there.
    What is the correct way to do this?

    My approach is to set the field, e.g. myField, as a criteria. and use the criteria with $advancedCriteria.myField

    This seems to work without having to create the <field name="myField">. But is this also correct? This inserts a '1'='1' in my where statement, since the field "myField" is unknown.
    Or should I create a <field name="myField" customSQL="true"> in this case ?
    If this is correct, do I need then <customFields> in my operation in order to avoid creating myField in the select ?

    Using smartgwt 6.1p
    Last edited by edulid; 3rd Apr 2018, 06:57.

  • Isomorphic
    replied
    The field declaration gives us type information about the data, which is key for non-text fields, and is also required in some cases for serialization between browser and server.

    Leave a comment:


  • edulid
    replied
    Hi Isomorphic

    interesting, this approach is the same as my original approach, with the difference that in your approach you declare the field while I don't declare it.
    In the example, you suggest to use:

    Code:
    <DataSource ID="minimalTestcase" serverType="sql" tableName="minimalTestcase">
        <fields>
            <field name="FAKECOLUMN" type="text" customSQL="true" />
            <field name="f_id" type="integer" primaryKey="true" />
            <field name="f_lastname" type="text" />
            <field name="f_firstname" type="text" />
        </fields>
    
        <operationBindings>
    
            <operationBinding operationType="fetch">
                <whereClause><![CDATA[
                  $advancedCriteria.FAKECOLUMN = 'abc' and ($defaultWhereClause)
               ]]></whereClause>
            </operationBinding>
    
        </operationBindings>
    
    </DataSource>
    which is generating
    Code:
    SELECT minimalTestcase.f_id, minimalTestcase.f_lastname, minimalTestcase.f_firstname FROM minimalTestcase WHERE
          'abc' = 'abc' and (('1'='1'))
    This seems correct.
    My original approach was:
    Code:
    <DataSource ID="minimalTestcase" serverType="sql" tableName="minimalTestcase">
        <fields>
            <field name="f_id" type="integer" primaryKey="true" />
            <field name="f_lastname" type="text" />
            <field name="f_firstname" type="text" />
        </fields>
    
        <operationBindings>
    
            <operationBinding operationType="fetch">
                <whereClause><![CDATA[
                  $advancedCriteria.FAKECOLUMN = 'abc' and ($defaultWhereClause)
               ]]></whereClause>
            </operationBinding>
    
        </operationBindings>
    
    </DataSource>
    which is generating the same SQL.

    So my question is:
    If you just want to write the SQL yourself, you still need the field declaration
    why do I need the field declaration? Not declaring it causes the same behavior. Is this a bug or is it intended ?
    And why do you recommend to declare the field although it is not being used (because of the customSQL="true" and the lack of customFields="FAKECOLUMN") ?
    Last edited by edulid; 16th Apr 2018, 09:13.

    Leave a comment:


  • Isomorphic
    replied
    You need customFields if you want our the generated SQL to include the custom field in both the select and where clauses. If you just want to write the SQL yourself, you still need the field declaration, but you should just set customSQL="true" and do not use the customFields property.

    Leave a comment:


  • edulid
    replied
    Hi Isomorphic

    as you see in this thread, I solved this issue by refactoring my code, such that no (fake) myField was necessary.
    But if this would not be possible, i.e. if I need a (fake) myField, which approach would be the correct one? Approach 1, 2 or 3? And please take a look at the issues with each approach. No approach seems perfect, that's why I need your recommendation for next time.

    Leave a comment:


  • edulid
    replied
    Hi Blama
    you are right, thank you, this worked like a charm. I now don't have a custom whereClause since the fields sent are simple fields that exist in the server.

    So in the case I really need a "fakeField", then I will try to do as Isomorphic recommended in your thread.

    Leave a comment:


  • Blama
    replied
    Yes, split the string in java and create criteria for f_stufe and f_klasse.
    f_stufe equals array[0]
    f_klasse equals array[1]

    Leave a comment:


  • edulid
    replied
    Or do you mean split the $advancedCriteria.myField in java and send the splitted values to sql as criteria? Example:
    $advancedCriteria.left and $advancedCriteria.right This would work, I think, let me check.

    Leave a comment:


  • edulid
    replied
    But for this I would have to get all values from the table and them compare them one by one with the splitten value in java. This would work, but for a large number of records I think it would be better to set the SQL to return only the correct records.

    Leave a comment:


  • Blama
    replied
    OK, but this will also work in Java and will also be more readable - split at " ", work with the array.

    Leave a comment:


  • edulid
    replied
    Hi Blama
    I think this may work for this simple example, but the "real" example is:

    Code:
    <whereClause><![CDATA[
    ((CHARINDEX(' ', RTRIM(LTRIM($advancedCriteria.myField))) > 0
            AND SUBSTRING($advancedCriteria.myField,1,CHARINDEX(' ', RTRIM(LTRIM($advancedCriteria.myField)))) = f_stufe
            AND SUBSTRING($advancedCriteria.myField,CHARINDEX(' ', RTRIM(LTRIM($advancedCriteria.myField))) + 1,LEN($advancedCriteria.myField)) = f_klasse)
        OR (CHARINDEX(' ', RTRIM(LTRIM($advancedCriteria.myField))) = 0
             AND $advancedCriteria.myField= f_stufe))
        AND ($defaultWhereClause)
                  ]]></whereClause>
    So I am comparing myField with f_stufe and f_klasse, depending on the type of "myField".
    Example:
    myField = "1 ABC". I compare 1 with f_stufe, ABC with f_klasse
    myField = "10 DEF". I compare 10 with f_stufe, DEF with f_klasse
    myField = "2". I only compare 2 with f_stufe.

    Leave a comment:


  • Blama
    replied
    Ok, but you are comparing it to something, in the code to show to 'abc'.
    Can't you use a customSelectExpression='abc' and apply trimming to the value before you use it in myFieldCriterion in your server side java code?

    Best regards
    Blama

    Leave a comment:


  • edulid
    replied
    Originally posted by Blama View Post
    strange indeed. Also a transformation of defaultWhereClause I've not seen before (only 1=1 or correct ones so far).
    Yes, this sql seems strange, since it will never return columns from the table. Isomorphic is this a bug? We had some issues with 1=1 which you corrected: https://forums.smartclient.com/forum...lowmultiupdate and https://forums.smartclient.com/forum...a/252444-inset
    Maybe this is a similar issue.

    Originally posted by Blama View Post
    IMHO, for my approach you don't need customSQL, or do you?

    Another suggestion: Could you just create a new field with customSelectExpression="RTRIM(LTRIM(myDBOnlyField))" and apply criteria on it normally with the client request?
    with customSelectExpression="RTRIM(LTRIM(myDBOnlyField))" -> my field is *not* in the db, it is as you call it a fakefield. So with this approach, I would still have to set the fakefield to something. I am not working from the client, but from the server, setting the fakefield with:

    Code:
    Criterion myFieldCriterion = new SimpleCriterion("myField", DefaultOperators.Equals, "def");
    ...
    AdvancedCriteria ac = new AdvancedCriteria(DefaultOperators.And, criterions.toArray(new Criterion[] {}));
    myRequest.setAdvancedCriteria(ac);

    Leave a comment:


  • Blama
    replied
    Hi edulid,

    strange indeed. Also a transformation of defaultWhereClause I've not seen before (only 1=1 or correct ones so far).

    IMHO, for my approach you don't need customSQL, or do you?

    Another suggestion: Could you just create a new field with customSelectExpression="RTRIM(LTRIM(myDBOnlyField))" and apply criteria on it normally with the client request?

    Best regards
    Blama


    Leave a comment:


  • edulid
    replied
    Blama
    you seem not to use your fakeField in your whereClause, so your SQL would be something similar:
    select f1, f2, null as FAKECOLUMN from yourTable.
    So in this case, this seems ok.

    --> Nevertheless, if this is the correct way to do it, it should also work as a criteria in the WhereClause, I think.

    Leave a comment:

Working...
X