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

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

Original Message:
Sent: Wed December 29, 2021 02:52 AM
From: Akash Gupta
Subject: Use IN clause in SQL Select Query via SQL Connector

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

This would be a security concern(SQL Injection). Hence we use only parametrized queries as best practice

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

Original Message:
Sent: Wed December 29, 2021 03:10 AM
From: Atanu Roy
Subject: Use IN clause in SQL Select Query via SQL Connector

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

Original Message:
Sent: Wed December 29, 2021 02:52 AM
From: Akash Gupta
Subject: Use IN clause in SQL Select Query via SQL Connector

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

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:
Sent: Wed December 29, 2021 03:21 AM
From: Akash Gupta
Subject: Use IN clause in SQL Select Query via SQL Connector

This would be a security concern(SQL Injection). Hence we use only parametrized queries as best practice

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

Original Message:
Sent: Wed December 29, 2021 03:10 AM
From: Atanu Roy
Subject: Use IN clause in SQL Select Query via SQL Connector

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

Original Message:
Sent: Wed December 29, 2021 02:52 AM
From: Akash Gupta
Subject: Use IN clause in SQL Select Query via SQL Connector

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

Thanks alot this solutions works :)

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

Original Message:
Sent: Wed December 29, 2021 03:41 AM
From: Atanu Roy
Subject: Use IN clause in SQL Select Query via SQL Connector

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:
Sent: Wed December 29, 2021 03:21 AM
From: Akash Gupta
Subject: Use IN clause in SQL Select Query via SQL Connector

This would be a security concern(SQL Injection). Hence we use only parametrized queries as best practice

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

Original Message:
Sent: Wed December 29, 2021 03:10 AM
From: Atanu Roy
Subject: Use IN clause in SQL Select Query via SQL Connector

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

Original Message:
Sent: Wed December 29, 2021 02:52 AM
From: Akash Gupta
Subject: Use IN clause in SQL Select Query via SQL Connector

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

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

Original Message:
Sent: Wed December 29, 2021 02:52 AM
From: Akash Gupta
Subject: Use IN clause in SQL Select Query via SQL Connector

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