Announcement

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

    Executing SQL IN clause in customSQL

    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.

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

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

    Comment


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

      Code:
        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);

      Comment


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

        Comment

        Working...
        X