[Isomorphic]

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.

[edulid]

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:

<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

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:

<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") ?

[Isomorphic]

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.

[edulid]

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.

[edulid]

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.

[Blama]

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]

[edulid]

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.

[edulid]

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.

[Blama]

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

[edulid]

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

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

[Blama]

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

[edulid]

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.

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:

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

[Blama]

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

[edulid]

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.

[edulid]

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:

<field name="myField" customSQL="true" customSelectExpression="null">

and leave the rest:

<operationBinding operationType="fetch" operationId="myOperationId" customFields="myField">
<whereClause>
RTRIM(LTRIM($advancedCriteria.myField))) = 'abc' AND ($defaultWhereClause)
</whereClause>
...
</operationBinding>

I get:

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.