Correct way to pass a value to a customSql statement

[edulid]

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

[Isomorphic]

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

[edulid]

Hi Isomorphic

you mean the latter approach, i.e.using :
Approach 1:

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

and in the operationBinding:

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

Using this I get:

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

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

and *with*

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

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

[Blama]

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

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

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

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

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]

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]

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 this will also work in Java and will also be more readable - split at " ", work with the array.

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

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

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

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.