Run Query

  The Run Query construct is an essential component used to execute database queries or stored procedures within a logic flow. It enables seamless database interactions by allowing users to define input parameters, manage query outputs, and map results to variables within workflows and server flows.

Use Cases of Run Query Construct

• Fetching User Data: Retrieve user details from a database.
• Inserting New Records: Add new entries and return inserted IDs.
• Executing Stored Procedures: Run predefined database logic efficiently.
• Updating & Deleting Records: Modify or remove existing data securely.

Adding a Run Query Construct in a Logic Flow

Step 1: Add the Construct

1. Open the Logic Flow Designer Canvas.
2. Drag and drop the Run Query construct into the flow.

Step 2: Configure the Construct

1. Open the Properties Panel by double-clicking the construct.
2. Configure the required settings:

• Select Query Type: Choose between Query or Stored Procedure.
• Select Query or Stored Procedure: Pick from the available options.
• Define Input Parameters and Output Mapping.

Step 3: Save & Apply Configuration

1. Click Save to apply the settings.
2. The construct is now ready for execution within the logic flow.

Property Details

Property	Description	Comments
Label	A descriptive label for the construct.	Improves readability of the logic flow.
Type*	Defines whether the construct executes a Query or Stored Procedure.	Options: Query or Stored Procedure.
Query*	Selects the query or stored procedure from available options.	Pick from the Entities Center.
Dataset Name*	Specifies the dataset name for handling multiple tables (for stored procedures).	Visible only for Stored Procedure and Multi-Table Queries.
Input Parameters	Defines values passed into the query using Expression Builder.	Ensure correct parameter mapping.
Query Output*	Specifies a variable to store the query result.	Essential for data retrieval.
Query Output Item*	A temporary variable for multi-row responses.	Used to map columns in rows (Data Row Item Alias).
Data Rows Alias*	Defines an alias for the dataset result.	Applicable for multi-row responses.
Data Row Item Alias*	Defines alias for individual rows in the dataset.	Maps columns to attributes in a temporary variable.
Output Mapping	Specifies output data mapping using Expression Builder.	Required for data transformation.
Comment	Determines if the construct should be ignored during execution.	If Yes, the construct is skipped.

Note: Properties marked with (*) are mandatory.

Types of Query Output

 

• Multi-Table Select: Returns multiple tables (for stored procedures).
• Multi-Row Select: Retrieves multiple rows (lists or datasets).
• Single Row Select: Fetches a single record.
• Single Value Select: Returns a single column value.
• JSON Object Select: Outputs data as a JSON object.
• JSON Array Select: Outputs data as a JSON array.
• Insert with Return: Inserts data and returns a specified field (e.g., inserted ID).
• Update with Return: Updates data and returns affected row details.
• Delete with Return: Deletes records and returns affected row details.

Query Output Formats (Structured Representation)

1. Multi-Row Select (Query/Stored Procedure)

• Requires: Query Output, Query Output Item, Data Rows Alias, Data Row Item Alias, and Output Mapping.
• Created Query Should be with Type: Select.
• Query/Stored Procedure Output Type: Multiple Row.

2. Single Row Select (Query/Stored Procedure)

• Requires: Query Output, Data Row Item Alias, and Output Mapping.
• Created Query Should be with Type: Select.
• Query/Stored Procedure Output Type: Single Row.

3. Single Value Select (Query/Stored Procedure)

• Requires: Query Output and Output Mapping.
• Created Query Should be with Type: Select.
• Query/Stored Procedure Output Type: Single Value.
• Specify single value output as datatype.

4. JSON Object Select (Query/Stored Procedure)

• Requires: Query Output only.
• Created Query Should be with Type: Select.
• Query/Stored Procedure Output Type: JSON Object.

5. JSON Array Select (Query/Stored Procedure)

• Requires: Query Output only.
• Created Query Should be with Type: Select.
• Query/Stored Procedure Output Type: JSON Array.

6. Insert (Query/Stored Procedure)

• No additional properties required.
• Created Query Should be with Type: Insert.
• Query/Stored Procedure Output Type: None.

7. Insert with Return (Query)

• Requires: Query Output.
• Created Query Should be with Type: Insert.
• Query Output Type: Boolean.

8. Update (Query/Stored Procedure)

• No additional properties required.
• Created Query Should be with Type: Update.
• Query/Stored Procedure Output Type: None

Update with Return (Query)

• Requires: Query Output.
• Created Query Should be with Type: Update.
• Query Output Type: Boolean or Number of Rows Affected.

Delete (Query/Stored Procedure)

• No additional properties required.
• Created Query Should be with Type: Delete.
• Query/Stored Procedure Output Type: None.

Delete with Return (Query)

• Requires: Query Output.
• Created Query Should be with Type: Delete.
• Query Output Type: Boolean or Number of Rows Affected.

Multi-Table Select (Stored Procedure)

• Requires: DataSet Name, Output Parameters.
• Created Stored Procedure Should be with Output Type: MultiTable.
• Define tables with applicableTable Type (Multi Row, Single Row, Single Value, JSON Object, JSON Array) and set Table Alias Names
• Based on the Table Type, repeat the relevant Query Output Format steps.

Steps for Output Mapping

1. Click Expression Builder to open the mapping window.
2. In the left panel, navigate to DB Util under System Functions.
3. Select SQL Activities to define field mappings.
4. Map column values from each row using Data Row Item Alias to the attributes of the temporary variable.
5. Click Proceed to confirm mappings.
6. Save the server flow.