Export Data Securely(DB)

This is a pre-built Action located under the @Generic Element. The functionality of this Action in Avo Assure is to execute the specified query provided in the input field and export the retrieved data to an external file, with the password provided as input in encrypted text. The result is stored in the given dynamic variable for further use during test execution.

Note: The DB password must be encrypted using the AES encryption method. For more information, click here.

Syntax

Input Value: <DB IP/instance name/DB file path>;<DB port>;<DB username>;<encrypted DB password>;<DB name>;<Query>;<DB Number/DB Tags>;<querytype/<warehouse>(Optional);<schema>(Optional)

Argument	Description
<DB IP/instance name/DB file path> (Required)	Provide the Database server location, instance name, or DB file path which you want to use.
<DB port> (Required)	Provide the port number which you want to use to connect to the Database.
<DB username> (Required)	Provide the username credential which you want to use for Database access.
<encrypted DB password> (Required)	Provide the encrypted password associated with the username.
<DB name> (Required)	Provide the Database name which you want to connect to.
<Query> (Required)	Provide the SQL or Non-SQL query which you want to execute.
<DB Number/DB Tags> (Required)	Provide the Database identifier which you want to use for the connection.
<querytype/warehouse> (Optional)	Provide the data container you want to target within the Database.
<schema> (Optional)	Provide the schema you want to use.

• To learn more about supported Database and there respective Database Numbers and Tags, click here.

Note:

• Provide either the Database IP address, the instance name, or the Database file path in the input value as required.

• Provide either the Database Number or the Database Tags in the input value as required.

• When you are using a non-SQL query, you must provide either the querytype or the warehouse name in the input value.

Output Value: <File Path>;<Sheet Name>(Optional)

Argument	Description
<File Path> (Required)	Provide the Excel file location where you want to store the exported data.
<Sheet name> (Optional)	Provide the Excel sheet name where you want to store the exported data.

Note: The Sheet Name parameter is optional. If you want to export the data to a specific sheet, you must provide the sheet name.

Example: Exporting Data from a Database to an Excel File

In this example, we explain how to export data from a Database and save it into an Excel file.

• Action: Verify Data Securely(DB)

• Input Value: 11.88.0.57;1433;John;FbSveCI31T0LIRLTdqsw==;Student;SELECT * from PQRS;MSSQL

  • The first argument (10.88.0.57) specifies the Database server IP address.

  • The second argument (1433) specifies the port number used to establish the Database connection.

  • The third argument (John) specifies the Database username.

  • The fourth argument (FbSveCI31T0LIRLTdqsw==) specifies the Database password.

  • The fifth argument (Student) specifies the Database name.

  • The sixth argument (SELECT * from PQRS) contains the SQL query to be executed.

  • The seventh argument (MSSQL) specifies the Database tag used for execution.

• Output Value: C:\sam\Excel.xls;Sheet1

  • The Output Value column contains the Excel file path (C:\sam\Excel.xls) and sheet name (Sheet1) where the data is exported.

• Logical Explanation: The Verify Data Securely(DB) action takes the value from the Input Value column (11.88.0.57;1433;John;FbSveCI31T0LIRLTdqsw==;Student;SELECT * from PQRS;MSSQL). The action connects to the specified Database and executes the query. It retrieves the data from the Database. The retrieved data is exported to the specified Excel file path (C:\sam\Excel.xls) and sheet (Sheet1) provided in the Output Value column.