Using SELECT IN Query in Workflow
I'm creating this article in response to a forum question. It demonstrates how to use a SELECT .. IN statement using the SQL query generator. There are a couple of pitfalls involving the input parameters and formatting the input CSV string. Also, I've attached a sample integration project, webform project, and the Northwind create database script in case you didn't install it with SQL.
Creating the Integration Project
To start, you must have the Northwind database available to your Workflow Designer. If you don't have it, execute the SQL script inside the zip file attached to this project. Northwind is a very old sample database with some known vulnerabilities, so don't install on a production MS SQL server.
- Create a new SQL Query Integration project (or open the attached project).
- Set and test your configuration string to connect to the Northwind database
- Enter the following query into the SQL Query textbox
SELECT CustomerID, CompanyName
WHERE CustomerID IN (@CustomerIDs)
- Click the Find Input Parameters button
- Uncheck the "Db Parameter" option to avoid string quoting problems.
Note: Doing this makes the query more vulnerable to injection attacks, using a stored procedure and or inserting + joining your data using a separate table is safer for public/external facing needs.
- Make sure the Quotes Type is set to "SingleQuoted"
- Continue through the wizard until you reach the Fields step
- Check the box "Returns Data"
- Click the Fill Schema button
- Enter the following sample data
You should see two columns returned as depicted below:
Finish completing the wizard and use the "Recompile and Close" button.
Creating the Webform project
- Create a new webform project. Import the integration library dll from the previous section. Or, import the attached webform project.
- Use an Initialize Data component to create a new string array consisting of the following sample data:
- Use a Build Text From Elements to create a CSV text string using the string array from the prior step.
Use ',' as the delimeter text
- Optional, create a Form Builder page to show the input data (see below)
- Next, use the new generator component, using the CSV string as input
- Create a form builder page to show the results: