Parameterized Queries
  • Updated on 25 Aug 2016
  • 4 minutes to read
  • Print
  • Dark
    Light

Parameterized Queries

  • Print
  • Dark
    Light

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 forSQL Server.
This example assumes the Northwind database has been integrated with the Portal. For your own purposes, you can use your default Decisions database or you can integrate with another database. For more information on integrating a database, see Integrating an External Database with a Flow .

2018-03-07_161228.png

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

databaseViewSQL.png

First, in aDesigner Folder selecting Datatypes/Integrations > Database Integration > Add Query

2018-03-07_161355-1024x608.png

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

2018-03-07_161435.png

Next, if we need our Query to Return Data we checkReturn 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 toGet 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 .

2018-03-07_161747.png

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.

2018-03-07_161842.png

In the Edit Parameters window we can define Name, Default Value, andType for this Parameter . In this example we define Default Value for parameter and click Ok to save and continue.

2018-03-07_161908.png

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.

2018-03-07_161952.png

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.
2018-03-07_162113.png

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

2018-03-07_162213.png

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.
2018-03-07_162301.png

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

2018-03-07_162417.png

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

2018-03-07_162448-1024x714.png

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

2018-03-07_162542.png

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.

2018-03-07_162911.png

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.

2018-03-07_163035-1024x600.png

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 .

2018-03-07_163347.png

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.

2018-03-07_163506.png

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

2018-03-07_163533.png

This Form is designed to display our Query Results . It has a Label: Customers In and a Text Box that displays User input from the previous Form in our Workflow . Query Results will be displayed in aData 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.

2018-03-07_164224-1024x494.png

Back in the Flow Designer we connect Done outcome from Display Form Step to the End Step in our Flow . Then, we Edit Input Mapping for Display Form Step.

2018-03-07_164346.png

In the Mapping Editor we map Country output from the first Form to Country (text box) , and GetCustomersByCountry_Result to Customers (Dynamic Data Grid control) . Then, we can save and close Mapping Editor.

2018-03-07_164535-1024x111.png

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

2018-03-07_164717.png

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

2018-03-07_164903-1024x543.png

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.

2018-03-07_164923.png

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…

2018-03-07_165048.png

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

2018-03-07_165140.png

Was this article helpful?