Video Screencast Help

Using SELECT IN Query in Workflow

Created: 20 Sep 2010 • Updated: 05 Nov 2010 | 1 comment
Language Translations
scottwed's picture
+1 1 Vote
Login to vote

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. 

  1. Create a new SQL Query Integration project (or open the attached project). 
  2. Set and test your configuration string to connect to the Northwind database
  3. Enter the following query into the SQL Query textbox

    SELECT CustomerID, CompanyName
    FROM Customers
    WHERE CustomerID IN (@CustomerIDs)

  4. Step 1

     

  5. Click the Find Input Parameters button
  6. 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.
  7. Make sure the Quotes Type is set to "SingleQuoted"
  8. Continue through the wizard until you reach the Fields step
  9. Check the box "Returns Data"
  10. Click the Fill Schema button
  11. Enter the following sample data

    BOTTM','CACTU

  12. Fields screenshot

     

  13. You should see two columns returned as depicted below:

  14. Finish completing the wizard and use the  "Recompile and Close" button.

Creating the Webform project

Full SELECT In Model screenshot

  1. Create a new webform project.  Import the integration library dll from the previous section.  Or, import the attached webform project.
  2. Use an Initialize Data component to create a new string array consisting of the following sample data:
  3. BOTTM CACTU CENTC

     

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

  5. Optional, create a Form Builder page to show the input data (see below)
  6. Sample Show input screen

     

  7. Next, use the new generator component, using the CSV string as input
     
  8. Create a form builder page to show the results:
  9. Sample Show Results Screen

     

 

 

    Comments 1 CommentJump to latest comment

    Vlastislav Drga's picture

    Hi, just a little advice for those who will need to use the same parameter in query for more than once.

    SQL query builder will create empty line for every other usage of parameter.

    For example in query like “select * from table where a>@param and b>@param and c=@param” after pressing “Find Query Parameters” builder will create one parameter called @param and 2 empty parameters.

    So as workaround can be used SQL query containing only unique parameters used in your query (in this case query would be only: @param) if you pres “Find Query Parameters” button it will create query parameters. Then you can go ahead, paste your desired query into SQL query field  - do not press “Find Query Parameters” again and continue with building of component.

    0
    Login to vote