Parameterized Queries

Overview

Parameterized Queries are used to prevent SQL Injections. In these types of queries, parameters that are defined become variables when the query is run in the workflow process. 

Example

This example demonstrates how to create and use a Parameterized Query to get a list of accounts for a company named Test Company. 

1.  In the Designer Project, click on CREATE DATATYPES/INTEGRATIONS on the Global Action Bar and select Database Integration. Click Add Query.
   
2. In the Database Integration dialog window, select an existing connection and click NEXT.
   
3. Under the QUERY SETTINGS catalog, provide a name for the query (GetAccountsByCompany). In the Query text area, enter "select * FROM dbo.entity_account WHERE company=@company". Under the INPUT PARAMETERS category, click the pencil icon to open the Edit Parameters dialog window. 
   The @company in this example is the parameter. In a SQL Server query, the @ designates a parameter. Other Database Servers will have a different parameter syntax.

   

4. Enter "Test Company" in the Default Value text box, leave the Type selected as String, and define the desired Maximum Size. Then click OK. 
   Note on Maximum Size 

   The Maximum Size Parameter is used to determine the maximum number of characters in a Data Value return. Returning a value with a shorter Maximum Size will make the Query run quicker. 

   

5. Click RUN QUERY to view the results.
   
6. Click OK to save the query and return to Designer Project.
7. On the Global Action Bar, click CREATE FLOW and select Flow. Name the Flow (Parameter Flow Query) and click CREATE.
   
8. Click the Done path on the Start step. Select the Show Form step and click ADD.
   
9. Click PICK OR CREATE FORM from the Properties panel and click CREATE on the dialog window.
   
10. Click Form on the Create New dialog window. Name the Form (Query Inputs) and click CREATE.
    
11. Configure the Form to include a Label (Enter Company), Text Box component (Company), and Button component (Search). Then, click Save and close the Form Designer.
    
12. Click on the Search path from the Show Form step. Navigate to Integrations > All Integrations > Database > LOCAL CONNECTION (Database). Select the Run query GetAccountsByCompany step and click ADD.
    
13. Under the INPUTS category, click Unknown next to the @company field and select Select From Flow. Select Company [String] and click DONE. Under Limit count, enter "10".
    
14. Click on the Done path and add another Show Form step. Configure the Form (QueryDisplay) to have a Label, Text Box, Data Grid, and Button component. With the Data Grid selected, click the dropdown list for Type. Navigate to User Defined Types > Defined Database Structure [Advanced] and select LOCALCONNECTION_Query_GetAccountsByCompany. Enter "Display Results" as the Data Name.
    
15.  Click Save and close the Form Designer.
16. With the Form selected, click Edit Input Mappings.
    
17. Click on Get Accounts By Company_ Result and drag the line to Display Results. Click on Company [String] and drag the line to Company Selected. Then, click OK.
    
18. Connect the Failed path from the Run query GetAccountsByCompany step and the Done path from Show Form 1 to the End step. Click Save to save changes to the Flow.
    

Debug

1. On the top action bar, click Debug. Then, click START DEBUGGING.
2. When the Form appears, enter "Test Company" and click Search.
   The Debugger will run the query and display the results of the accounts that matched the parameter Test Company.