|
|
相关文章推荐
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 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
------------------------------
Original Message 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 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
------------------------------
Original Message 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 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
------------------------------
推荐文章