Documentation

Using SQL Scripts in Your Automation

HuLoop allows you to use SQL scripts in automation cases through Functions and Commands:

• Use Execute Query on SQL Database function when you need to retrieve data from the database, such as fetching a specific column value, executing an INSERT, UPDATE, or DELETE query, or running any other database operation.
• If your requirement involves fetching an entire row or multiple values from the database, consider using the Get data from Database Command instead for a more comprehensive data retrieval.
• Use Execute Database Stored Procedure Function to execute database stored procedures within your automation workflow.

Execute Query on SQL Database Function

The Execute Query on SQL Database function allows you to execute SQL queries directly on a connected database.

In addition to running static queries, this function supports parameterized queries, enabling you to pass dynamic or hard-coded values to your SQL scripts for greater flexibility in automation workflows.

Steps to configure:

1. Open the Case where you need to use the data fetched from the database.
2. Add a new step at the required index.
3. From the Action drop-down menu, select Set a Variable Value.
4. Enter a variable name in the Element Key field (e.g., QueryResult). This variable will store the query result.

5. Click Form (for tabular View) and Parameters Form (for Card View) to configure the query.
6. Select Function from the drop-down, as shown.

7. From the next drop-down menu, select Execute Query on SQL Database function.
8. Provide the following parameters:
   • db setting name:sql script name: Enter the database name and SQL script name in the format DatabaseName:SQLScriptName (e.g., default: My_Script_1).

Note: If your project is connected to only one database and the connection details are stored in the default tab under Settings > DB Settings, you can omit the default keyword and directly specify the script name.

Avoid using spaces in the script name.

• • varData: Use this field only if the SQL query contains parameters. Map the variables in your query to their respective values, either dynamic or hard-coded.
    • For a dynamic value: Use the syntax VariableName:${ReferencedVariable} where ReferencedVariable is defined elsewhere in the test case).
    • For a hard-coded value: Provide it directly (e.g., VariableName:John Doe).

9. Click Save.

Expected Outcome:

• Upon execution, the SQL query fetches the specified data from the database and stores it in the provided variable (e.g., QueryResult).
• This variable can then be dynamically used in subsequent steps within the automation case using the syntax syntax ${VariableName} (e.g., ${QueryResult}).

Usage Examples:

Scenario 1: Static SQL Query
You want to fetch the first name of a person with a fixed PersonID.

• SQL Script: SELECT FirstName FROM dbo.Persons WHERE PersonID = ‘5’;
• Configuration:
  • Element Key: EmployeeName.
  • db setting name:sql script name: EmployeeDB:fetchEmployeeDetails.sql
  • varData: Leave blank.

• Result: The query retrieves the first name of the person with PersonID = 5, storing it in ${EmployeeName}.

If your SQL query includes dynamic variables, you can define them using the syntax ‘@VariableName’ within the query. These variables must be mapped to their values in the varData field. The values can be either dynamic (retrieved from other variables) or static (hard-coded values).

Scenario 2: Parameterized SQL Query with Dynamic Value
You want to fetch the first name of a person using a dynamic PersonID stored in another variable.

• • SQL Script: SELECT FirstName FROM dbo.Persons WHERE PersonID = ‘@FirstName’;

• Configuration:
  • Element Key: EmployeeName.
  • db setting name:sql script name: EmployeeDB:fetchEmployeeDetails.sql
  • varData: FirstName:${id} (where ${id} dynamically references another variable).

• Result: The query retrieves the first name of the person with the dynamic PersonID value and stores it in ${EmployeeName}.

Scenario 3: Parameterized SQL Query with Hard-Coded Value
You want to fetch the first name of a person with a predefined PersonID.

• • SQL Script: SELECT FirstName FROM dbo.Persons WHERE PersonID = ‘@id1’;

• Configuration:
  • Element Key: EmployeeName.
  • db setting name:sql script name: EmployeeDB:fetchEmployeeDetails.sql
  • varData: id1:5

• Result: The query retrieves the first name of the person with PersonID = 5, storing it in ${EmployeeName}.

Get data from Database Command

The Get data from Database command is used to fetch multiple data from a database table and store it in a variable. This approach minimizes the need for multiple database calls when working with data from the same table. The stored data can be accessed dynamically by referring to specific column names or indices.

Example: Suppose you are retrieving employee details from a database table and storing the employee data for processing in later steps.

Steps to configure:

1. Add a new step.
2. Select Get Data from Database from the Action dropdown.
3. Leave the Screen Name field blank.
4. In the Element Key field, enter the variable name to store the data (e.g., EmployeeDetails).
5. In the Parameters section, provide the name of the SQL script (e.g., MySQL_Script_2).
6. Click Save. 

Outcome on Execution:

• The command retrieves entire data based on your query from the database table as specified in the SQL script.
• The retrieved data is stored in the variable defined in the Element Key field (e.g., EmployeeDetails).
• You can get the first-row data using the syntax below:

Syntax to Access Data from a Specific Column at Index 1: ${Variable name[column name]}

Example: ${EmployeeDetails[first_name]}

• Similarly, you can get the row data from a specific index using the syntax below:

Syntax to Access Data at a Specific Index: ${Variable name[column name]@Index=n}

Example: ${EmployeeDetails[last_name]@index=2}

Execute Database Stored Procedure Function

The Execute Database Stored Procedure Function allows you to run stored procedures directly within your automation workflow. This eliminates the need for manually writing complex SQL queries and ensures efficient execution of predefined database operations. The stored procedure must be assigned a unique name, which is then referenced in the automation workflow.

Using SQL Scripts to Execute Stored Procedures

HuLoop also allows you to trigger Database Stored Procedures using SQL scripts. Instead of writing an SQL query, simply provide the stored procedure name in the SQL Script text area. To do so, follow these steps:

1. Open the Automation project you are working on.
2. Click the Menu available on the top right of the screen and you will get the following options:

• • Expand the Data option and select Manage SQL Scripts:

• • You will see the Manage SQL Scripts page:

• • Assign a name to the script (e.g., Get_Employee_Details_Script), leave Environment Name as default, and enter the database stored procedure name in the SQL Script text area (e.g., GetEmployeeDetails).

Avoid using spaces in the script name.

• • Click Save.

Note: The script name you provide here will be used when referencing this SQL script in automation workflows.

Using the database stored procedure in your Automation

Steps to Configure:

1. Add a new step in the Case.
2. Select Set a Variable Value from the Action dropdown (e.g., QueryResult). This variable will store the query result.

3. Click Form (for Tabular View) or Parameters Form (for Card View) to configure the query.
4. Select Function from the dropdown.

5. From the next dropdown menu, select Execute Database Stored Procedure function.
6. Provide the following parameters:
7. Filename:
   • If using a default database, enter only the script name (e.g., Get_Employee_Details_Script).
   • If working with multiple databases, use the format: DatabaseName:ScriptName (e.g., EmployeeDB:Get_Employee_Details_Script).
8. Click Save.

Expected Outcome

• The stored procedure executes successfully.
• The result is returned as true if the procedure executes successfully and false if it fails.
• The result is stored in the specified variable, which can be used dynamically in subsequent automation steps using the syntax ${VariableName} (e.g., ${QueryResult})