Parameterized Queries

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

Example:

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.

databaseIntegration

In this example we are going to query Customers table in Northwind database.

databaseViewSQL

First, in Decisions Portal we navigate to System > Integrations > Databases > Northwind. Then, from Folder Actions Panel we click All > Add Query.

addQuery

Database Integration window pops-up. In the Query Settings we provide desirable Name for the Query.

nameQuery

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.

defineQueryWithParams

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.

editParam

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.

defineDefaultUSA

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.

returnDataOptions

Next, we Run Query again…

runQuerySecond

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.

queryResults

Back in the Northwind Folder we can see our Query definition.

queryCreated

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.

createFlow

In the resulting window we Name the Flow and click Create to proceed to the Flow Designer.

nameFlow

In the Flow Designer we add Show Form Step from Favorite Steps category.

addShowForm

Then, we Name the Form and click Create to proceed to the Form Designer.

nameForm

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.

firstFormDesign

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.

addQueryStep

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.

mapDataForQueryStep

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.

addDisplayForm

In the resulting window we Name the Form and click Create to proceed to the Form Designer.

createSecondForm

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.

secondFormDesign

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.

showMappingForSecondForm

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.

mappingForSecondForm

This completes our Flow. We can click Test Flow on top Panel of the Flow Designer.

testFlow

First Form opens in the Debugger. We input Country and click Search Customers.

firstFormInDebugger

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.

queryResultsForm

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…

queryInput

And the Output Data for the Query Step is a List of Customers in Mexico

queryOutput

Additional Resources