Executing SQL IN clause in customSQL

[preeti_kanyal]

Hi team,
I am trying to execute an update customSQL but it's throwing error on IN clause. Because it's just trying to replace the passed data in query as it is. So it's syntax error for SQL.

 <operationBindings>
        <operationBinding operationType="update" operationId="applyToAll" allowMultiUpdate="true">
           <customSQL>
                    UPDATE Test te
                            INNER JOIN
                        Test1 te1 ON te.id = te1.id
                            INNER JOIN
                        Test2 te2 ON te2.id= te1.id
                       SET 
                        te.name= #if($values.name) $values.name#else te.name #end, 
                        te.cost = CASE WHEN te2.customer_id  &gt; 0  OR (te2.customer_id &lt; 0 and te2.name = &quot;Admin&quot;)
                            THEN #if($values.cost) $values.cost#else te.cost#end
                            ELSE te.cost END
                    WHERE
                        te.idin ($values.id_list);
           </customSQL>
        </operationBinding>
    </operationBindings>

I tried sending the id_list as arraylist and a comma separated string too. Is there any way of passing list inside IN clause in customSQL?

[Isomorphic]

You don't have an IN clause here. Perhaps this part is a typo and a space was intended?

te.idin ($values.id_list);

If you want to get your SQL syntax correct, you can just use dsRequest.addToTemplateContext() to add a correctly formatted string that you have created in Java code, then refer to that from <customSQL> template. Just be sure to quote data values properly to make sure you are not vulnerable to SQL injection attacks.

[preeti_kanyal]

Thanks Isomorphic , sorry that was just typo. The suggested addToTemplateContext() method is available on com.isomorphic.datasource.DSRequest.
I am creating the request from client using com.smartgwt.client.data.DSRequest.

  DSRequest req = new DSRequest();
            Map<String, Object> reqData = new HashMap<>();
            reqData.put("id_list", ids);
            req.setOperationType(DSOperationType.UPDATE);
            editValues.putAll(reqData);
            req.setData(editValues);
            req.setOperationId("applyToAll");
            DataSource.get("MyDataSource").execute(req);

[Isomorphic]

com.smartgwt APIs are client-side APIs that run in the browser. You cannot customize SQL from there because that would mean the browser could send SQL to the server to be executed, a security issue. Instead, the server-side addToTemplateContext() API must be used from code that runs on the server. See the QuickStart Guide for further details, paying particular attention to DMI and Server Scripting.