BPM, Workflow, and Case

BPM, Workflow, and Case

Come for answers. Stay for best practices. All we’re missing is you.

 View Only

  • 1.  Use IN clause in SQL Select Query via SQL Connector

    Posted 12/29/21 02:53 AM
    Is there any different way to use the IN clause in the select query. I am trying to pass a comma separated string as a parameter but it is not returning any result.
    Below is my code

    var caseIdCSV="'4141','4140'";
    tw.local.sqlStatement=new tw.object.SQLStatement();
    tw.local.sqlStatement.sql="SELECT * from case_details where case_id IN (?)"

    tw.local.sqlStatement.parameters=new tw.object.listOf.SQLParameter();
    tw.local.sqlStatement.parameters[0]=new tw.object.SQLParameter();
    tw.local.sqlStatement.parameters[0].mode='IN';
    tw.local.sqlStatement.parameters[0].type='VARCHAR';
    tw.local.sqlStatement.parameters[0].value=caseIdCSV;

    ------------------------------
    Akash Gupta
    ------------------------------


  • 2.  RE: Use IN clause in SQL Select Query via SQL Connector

    Posted 12/29/21 03:11 AM
    Why are you not simply adding the value part in the query itself ? Like - 
    tw.local.sqlStatement.sql="SELECT * from case_details where case_id IN ("+caseIdCSV+")";

    Is there any specific need of adding this in the parameters ?


    ------------------------------
    Atanu Roy
    Solution Architect
    Salient Process
    ------------------------------



  • 3.  RE: Use IN clause in SQL Select Query via SQL Connector

    Posted 12/29/21 03:21 AM
    This would be a security concern(SQL Injection). Hence we use only parametrized queries as best practice

    ------------------------------
    Akash Gupta
    ------------------------------

    Original Message


  • 4.  RE: Use IN clause in SQL Select Query via SQL Connector

    Posted 12/29/21 03:42 AM
    Okay, if that is the case, then you need form both the query and the parameters dynamically based on the number of the items in the IN part. For your example - 
    var caseIdCSV="'4141','4140'";
    tw.local.sqlStatement=new tw.object.SQLStatement();
    tw.local.sqlStatement.sql="SELECT * from case_details where case_id IN (?, ?)"

    tw.local.sqlStatement.parameters=new tw.object.listOf.SQLParameter();

    tw.local.sqlStatement.parameters[0]=new tw.object.SQLParameter();
    tw.local.sqlStatement.parameters[0].mode='IN';
    tw.local.sqlStatement.parameters[0].type='VARCHAR';
    tw.local.sqlStatement.parameters[0].value="4141";

    tw.local.sqlStatement.parameters[1]=new tw.object.SQLParameter();
    tw.local.sqlStatement.parameters[1].mode='IN';
    tw.local.sqlStatement.parameters[1].type='VARCHAR';
    tw.local.sqlStatement.parameters[1].value="4140";

    Hope this helps.

    ------------------------------
    Atanu Roy
    Solution Architect
    Salient Process
    ------------------------------

    Original Message


  • 5.  RE: Use IN clause in SQL Select Query via SQL Connector

    Posted 12/29/21 04:07 AM
    Thanks alot this solutions works :)

    ------------------------------
    Akash Gupta
    ------------------------------

    Original Message


  • 6.  RE: Use IN clause in SQL Select Query via SQL Connector

    Posted 04/04/22 08:47 AM
    I recently startred to study and implement mysql performance tuning into my IT project. MySQL performance optimization generally involves configuring, profiling, and monitoring performance at several levels.MySQL is free and open-source. MySQL is ideal for both small and large applications.

    ------------------------------
    geff green
    ------------------------------



Related Content