Documentation

Using SQL Scripts in Your Automation

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

• 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 Dump SQL Query Output in Excel File function to export the results of an SQL query to an Excel file for reporting or data validation within your automation workflow.
• 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.
4. Enter a variable name in the Element Key field (e.g., QueryResult). This variable will store the query result.

5. Click Form 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).

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 insert multiple details of a person, such as PersonID, LastName, FirstName, Address, and City using dynamic values that are stored in other variables within your case.

• • SQL Script: INSERT INTO Person (PersonID, LastName, FirstName, Address, City)
    VALUES (‘@id’, ‘@lName’, ‘@fName’, ‘@add’, ‘@userCity’);

• Configuration:
  • Element Key: EmployeeName.
  • db setting name:sql script name: EmployeeDB:fetchEmployeeDetails.sql
  • varData: id:${PersonID},lName:${LastName},fName:${FirstName},add:${Address},userCity:${City}

(here each variable dynamically references values defined elsewhere in the test case)

Result:

• The query inserts a new record into the Person table using the values stored in the corresponding variables.
• The execution status (true or false) is stored in the variable defined in the Element Key.
• This variable can be dynamically used in subsequent steps using the syntax ${VariableName} (e.g., ${EmployeeName}).

Scenario 3: Parameterized SQL Query with Hard-Coded Value
You want to insert multiple details of a person by providing fixed, predefined values in the SQL script configuration.

• • SQL Script: INSERT INTO Person (PersonID, LastName, FirstName, Address, City) VALUES (‘@id’, ‘@lName’, ‘@fName’, ‘@add’, ‘@userCity’);

• Configuration:
  • Element Key: EmployeeName.
  • db setting name:sql script name: EmployeeDB:fetchEmployeeDetails.sql
  • varData: id:101,lName:Doe,fName:John,add:123 Main Street,userCity:New York

Result:

• The query inserts a new record into the Person table using the specified hard-coded values.
• The execution status (true or false) is stored in the variable defined in the Element Key.
• This variable can be dynamically used in subsequent steps using the syntax ${VariableName} (e.g., ${EmployeeName}).

Get data from Database Action: GETDBDATA

The Get data from Database action 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 GETDBDATA 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 action 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}

Scenario: Using a Parameterized SQL Query

You can use a parameterized SQL query with the GETDBDATA action when you need to fetch data dynamically based on specific values provided at runtime. Instead of hard-coding the values in your SQL query, you can pass them as parameters. This makes your automation flexible and reusable.

For example: In the previous scenario, you fetched all employee details using a fixed SQL script. In this scenario, you can use parameters to fetch only specific records that match certain conditions, such as a particular PersonID or City.

Scenario 1: Parameterized SQL Query with Dynamic Values

You want to retrieve details of a person such as: PersonID, LastName, FirstName, Address, and City, using dynamic values stored in variables within your case.

SQL Script:

SELECT * FROM Person WHERE PersonID=’@id’ AND City=’@userCity’;

Example Configuration:

• Element Key: PersonDetails
• Parameters: EmployeeDB:fetchPersonDetails.sql, id:${PersonID},userCity:${City}
  (Here, each variable dynamically references values defined elsewhere in the test case.)

Outcome on execution:

• The query fetches matching records from the Person table using the variable values.
• The retrieved data is stored in the variable defined in the Element Key (for example, PersonDetails).
• You can dynamically access the retrieved data using the following syntax:
  • By column name: ${PersonDetails[LastName]}
  • By column and index: ${PersonDetails[LastName]@index=0}

Scenario 2: Parameterized SQL Query with Hard-Coded Values

You can also configure a parameterized query using predefined (fixed) values instead of dynamic variables.

SQL Script:

SELECT * FROM Person WHERE PersonID=’@id’ AND City=’@userCity’;

Example Configuration:

• Element Key: PersonDetails
• parameters: EmployeeDB:fetchPersonDetails.sql,id:101,userCity:New York

Outcome on execution:

• The query retrieves all records from the Person table where PersonID = 101 and City = New York.
• The retrieved data is stored in the variable defined in the Element Key field (for example, PersonDetails).
• You can use this data dynamically in subsequent steps using the syntax:
  • By column name: ${PersonDetails[FirstName]}
  • By column and index: ${PersonDetails[FirstName]@index=0}

Dump SQL Query Output in Excel File: SQLDUMPHANDLER

The Dump SQL Query Output in Excel File function exports the results of an SQL query into a new Excel file. Use this function when you want to save query results for reporting, validation, or data comparison within your automation workflow.

Steps to Configure:

1. Add a new step to your case.
2. From the Action drop-down list, select SET.
3. Leave the Screen Name field blank.
4. In the Element Key field, enter a variable name to store the function’s execution result (for example, DumpStatus).
5. Click Form, select Functions, and choose Dump SQL Query Output in Excel File.

6. In the Parameters section, provide the following details:
   • connName: Enter the database connection name (for example, EmployeeDB). Type default if you are using the default connection.
   • queryName: Enter the name of the SQL script created earlier (for example, fetchEmployeeDetails.sql).

Avoid using spaces in the script name.

• • varData: Use this field only if your SQL query contains parameters. (See Examples 2 and 3.)
  • filePath: Specify the complete path where you want the Excel file to be created (for example, C:\HuLoopOutput\EmployeeData.xlsx).
  • alias: Provide a short, meaningful name to reference this Excel file in later steps or use cases (for example, EmployeeData).
  • type: Type query.

7. Click Save.

Outcome on Execution:

• The automation runs the SQL query using the specified connection.
• A new Excel file is created with the query results.
• The result status (true or false) is stored in the variable defined in the Element Key field.
• This variable can then be dynamically used in subsequent steps using the syntax ${VariableName} (e.g., ${DumpStatus}).

Usage Examples:

Scenario 1: Static SQL Query

You want to fetch all employee details from the database and export them to an Excel file for reporting or review.

SQL Script:

SELECT * FROM dbo.EmployeeDetails1;

Configuration:

• Action: SET
• Element Key: EmployeeList
• conneName: EmployeeDB
• queryName: fetchEmployeeDetails.sql
• filePath: C:\HuLoopOutput\EmployeeDetails.xlsx
• alias: EmployeeData
• type: query

Outcome on Execution:

• The automation runs the SQL query and exports the results (all employee details) to the specified Excel file.
• If a file with the same name already exists, it will be overwritten.

• The result status (true or false) is stored in the variable defined in the Element Key field. This variable can then be dynamically used in subsequent steps within the automation case using the syntax ${VariableName} (e.g., ${EmployeeList}).

Scenario 2: Using a Parameterized SQL Query with Hard-Coded Value
You want to retrieve a person’s first name from the database using a fixed PersonID value and save the result to an Excel file.

SQL Script:

SELECT FirstName FROM dbo.Persons WHERE PersonID = ‘@id1’;

Configuration:

• Action: SET
• Element Key: EmployeeName
• connName: EmployeeDB
• queryName: fetchEmployeeDetails.sql
• varData: id1:5
• filePath: C:\HuLoopOutput\EmployeeDetails.xlsx
• alias: EmployeeData
• type: query

Outcome on Execution:

• The automation retrieves the first name of the person whose PersonID equals 5 and saves the result in an Excel file.
• The result status (true or false) is stored in the variable defined in the Element Key field. This variable can then be dynamically used in subsequent steps within the automation case using the syntax ${VariableName} (e.g., ${EmployeeName}).

Scenario 3: Using a Parameterized SQL Query with Dynamic Value

You want to retrieve a person’s first name using a dynamic PersonID value stored in another variable within the same case.

SQL Script:

SELECT FirstName FROM dbo.Persons WHERE PersonID = ‘@id1’;

Configuration:

• Action: SET
• Element Key: EmployeeName
• connName: EmployeeDB
• queryName: fetchEmployeeDetails.sql
• varData: id1:${PersonID} (where ${PersonID} dynamically references another variable defined in the Use case)
• filePath: C:\HuLoopOutput\EmployeeDetails.xlsx
• type: query
• alias: EmployeeData

Outcome on Execution:

• The automation retrieves the first name of the person whose PersonID matches the value stored in the ${PersonID} variable and saves the result in an Excel file.
• The result status (true or false) is stored in the variable defined in the Element Key field. This variable can then be dynamically used in subsequent steps within the automation case using the syntax ${VariableName} (e.g., ${EmployeeName}).

Important Notes:

• If a file already exists at the specified path, it will be overwritten.
• Ensure the Excel file is not open during execution to avoid conflicts.
• You can reuse the generated Excel file in later steps by referencing its alias. The alias name lets you reference this Excel file in other functions, such as Get EXCEL Cell Value Using Column Name, etc.
• When using parameterized queries, make sure your varData matches the parameter names in your SQL script.

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 Data as shown:

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

Using the database stored procedure in your Automation

Steps to Configure:

1. Add a new step in the Case.
2. Select SET from the Action dropdown (e.g., QueryResult). This variable will store the query result.
3. Click Form to configure the query.

4. Select Function from the dropdown.
5. From the next dropdown menu, select Execute Database Stored Procedure .
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})