Parameterized QueriesLast Updated: 08/25/2016 Introduced in Verision:
This tutorial demonstrates how to use Parameterized Queries in Decisions. Parameterized Queries is the best way to prevent SQL Injections. In these types of queries we define Parameters that become Variables when we run this Query in Workflow process. This technology makes our queries dynamic.
In this example we are going to demonstrate how to create and use Parameterized Queries in Decisions.
We are going to create Parameterized Query for SQL Server.
For this example we integrated Decisions Instance with Northwind external database. To learn how to Integrate Decisions with External Database please click here.
In this example we are going to query Customers table in Northwind database.
First, in Decisions Portal we navigate to System > Integrations > Databases > Northwind. Then, from Folder Actions Panel we click All > Add Query.
Database Integration window pops-up. In the Query Settings we provide desirable Name for the Query.
Next, if we need our Query to Return Data we check Return Data check-box. Also, we can check Output Count. Moreover, we can set Query Timeout (in seconds). In the Query window we define our Query statement. In this Example we are going to Get All Customers by Country field. Notice, last field of the Query definition states where Country = @Country. In this example @Country will be our Parameter. ‘@’ character defines a Parameter for the SQL Server query. Note: for different types of database Servers query parameter character may vary. Then, we click Run Query button to test our Query.
We can see that our Query did not fail, but returned 0 Rows. Also, we can see that System generated @Country parameter in the Input Parameters section. We can Edit this Parameter by clicking pencil icon.
In the Edit Parameters window we can define Name, Default Value, and Type for this Parameter. In this example we define Default Value for parameter and click Ok to save and continue.
Back in the Database Integration window in Output Settings we can select different Return Data Options like:
- Create Type (system creates new Type based on returned Fields)
- Select Type (to let user pick existent Type)
- Data Rows (to output dynamic Data Rows structure)
- CSV Data Rows (to output comma separated rows)
In this example we will leave Create Type Return Data Option.
Next, we Run Query again…
Because we defined Default Value for our Parameter (USA), our Query returns all Customers that located in USA. We can click Ok to save and close Database Integration window.
Back in the Northwind Folder we can see our Query definition.
Next, we are going to demonstrate how to use this Parameterized Query in the Workflow process.
In the Designer Folder we click Create Flow on Folder Actions Panel.
In the resulting window we Name the Flow and click Create to proceed to the Flow Designer.
In the Flow Designer we add Show Form Step from Favorite Steps category.
Then, we Name the Form and click Create to proceed to the Form Designer.
This Form is designed as following… It has a Label, Text Box control (for User to input Country), and Button to submit the Form. When finished we can save and close Form Designer.
Back in the Flow Designer next to our Form step in the Flow we add Run query GetCustomersByCountry Step from Integrations > My Integrations > Database Northwind (Database) category.
Then, for this step we Select Value of Country output from the first Form in the Flow for the @Country Input and Ignore Limit count Input.
Next, we connect Failed outcome from Run query GetCustomersByCountry Step to the End Step in our Flow. On the Done outcome we add another Show Form Step from Favorite Steps category.
In the resulting window we Name the Form and click Create to proceed to the Form Designer.
This Form is designed to display our Query Results. It has two Labels: Constant and Dynamic [Country] that displays User input from the previous Form in our Workflow. Query Results will be displayed in Dynamic Data Grid control with Northwind_Query_GetCustomersByCountry Type. Finally, it has Button control to submit and close Form. When finished, we can save and close Form Designer.
Back in the Flow Designer we connect Done outcome from Display Form Step to the End Step in our Flow. Then, we Show Mapping Editor for Display Form Step.
In the Mapping Editor we map Country output from the first Form to Country (dynamic Label), and GetCustomersByCountry_Result to Customers (Dynamic Data Grid control). Then, we can save and close Mapping Editor.
This completes our Flow. We can click Test Flow on top Panel of the Flow Designer.
First Form opens in the Debugger. We input Country and click Search Customers.
Second Form opens and displays all Customers from Mexico based on our Parameterized Query Result. We click Done to close the Form and continue Flow execution.
Flow executes to the End Step with no issues. If we look up our Query step Input Data we can see value Mexico for the Key @Country…
And the Output Data for the Query Step is a List of Customers in Mexico…