Avo Assure - User Guide v24.1

Database Operations

Note:

  1. For keywords requiring an encrypted DB password, the DB password must be encrypted with AES encryption available in the Avo Assure Utility plugin.

  2. For MS Access files, a DB port, username, and DB name are not required. Password is required only if the DB file is password-protected.

  3. DB Numbers are required to be specified in the inputs. The following are the DBs supported, and their corresponding DB numbers must be specified in the keyword input.

Database Supported

DB Number

DB2

2

MSSQL

4

Oracle

5

Sybase

7

MS Access Files (.mdb and .accdb)

3

Mongo

9

Redis

10

Cassandra

11

SQLite

12

Snowflake

13

PostgreSQL

14

Keyword: GETDATA

Description: This keyword or action instructs Avo Assure Client to run a query in the specified database from the input field and save the results in the output variable.

Keyword

Input Syntax

Output Syntax

Supported Input Formats

getData

<DB IP/instance name/DB file path>; <DB port>; <DB username>; <DB password>; <DB name>; <Query>; <DB Number>; <collection/warehouse>(Optional); <querytype/schema>(Optional)

{Variable}

  • String

  • Static values

  • Dynamic variables

Note:

  1. For MS Access files, an additional dedicated query is supported. In the input’s ‘query’ argument, the user must specify “getAllTables” (case insensitive) to fetch all the table names in the DB file.

  2. To view the values of the variable, the user should use the following format: {OutputVariableName[2][3]}. This gives the value of the 2nd row and 3rd column.

Find the snippet of the keyword below for accessing a DB instance:

In the above example, after the test case debug, {result[1][2]} variable contains the value from the first-row second column of query results obtained from the specified DB.

Find the snippet of the keyword below for working with SQLite db, .mdb, and .accdb files:

In the above example, after the test case debug, {result[1][2]} variable contains value from the first row, second column of query results obtained from the specified DB file.

Some database instances, like MongoDB, Redis, and Snowflake DB, require either the Collection/Warehouse name or the Query Type/Schema.

Find the snippet of the keyword below for working with MongoDB:

Find the snippet of the keyword below, for working with Redis DB:

Find the snippet of the keyword below, for working with Snowflake DB:

Keyword: VERIFYDATA

Description: This keyword or action instructs Avo Assure Client to run a query in the specified database from the input field and verify the data with the input file.

Keyword

Input Syntax

Output Syntax

Supported Input Formats

verifyData

<DB IP/instance name/DB file path>; <DB port>; <DB username>; <DB password>;<DB name>; <Query>; <DB Number>; <file path to verify data>; <Sheet Name>; <collection/ warehouse>(Optional); <querytype/schema>(Optional)

{Variable}

  • String

  • Static values

  • Dynamic variables.

  • Input file format supported: .xls, .xlsx and .csv

Find the snippet of the keyword below:

In the above example, after the test case debug, based on the comparison between the query result and the content of the specified file, the {status} variable contains the value “True/False”.

Find the snippet of the keyword below, for working with SQLite db, .mdb, and .accdb files:

In the above example, after the test case debug, based on the comparison between the query result and the content of the specified file, the {status} variable contains the value “True/False”.

Some database instances, like MongoDB, Redis, and Snowflake DB, require either the Collection/Warehouse name or the Query Type/Schema.

Find the snippet of the keyword below, for working with MongoDB:

Find the snippet of the keyword below, for working with Redis DB:

Find the snippet of the keyword below, for working with Snowflake DB:

Keyword: EXPORTDATA

Description: This keyword or action instructs Avo Assure Client to execute a specified query from the input and export the result to an external file.

Keyword

Input Syntax

Output Syntax

Supported Input Formats

exportData

<DB IP/instance name/DB file path>; <DB port>; <DB username>; <DB password>; <DB name>; <Query>; <DB Number>; <collection/ warehouse>(Optional); <querytype/ schema>(Optional)

<File Path>; <Sheet name>

  • String

  • Static values

  • Dynamic variables

  • Output file format supported: .csv, .xlsx and .xls

Note: Sheet name is optional.

Find the snippet of the keyword below:

In the above example, after the test case debug, the results from the specified query will be exported to the DB_export.xls file.

Find the snippet of the keyword below, for working with SQLite db, .mdb, and .accdb files:

In the above example, after the test case debug, the results from the specified query will be exported to the DB_export.xls file.

Some database instances, like MongoDB, Redis, and Snowflake DB, require either the Collection/Warehouse name or the Query Type/Schema.

Find the snippet of the keyword below, for working with MongoDB:

Find the snippet of the keyword below, for working with Redis DB:

Find the snippet of the keyword below, for working with Snowflake DB:

Keyword: RUNQUERY

Description: This keyword or action instructs Avo Assure Client to run the specified input query on the database specified in the input field.

Keyword

Input Syntax

Output Syntax

Supported Input Formats

runQuery

<DB IP/ instance name/ DB file path>; <DB port>; <DB username>; <DB password>; <DB name>; <Query>; <DB Number>; <collection/ warehouse>(Optional); <querytype/ schema>(Optional)

{optional}

  • String

  • Static values

  • Dynamic variables.

Find the snippet of the keyword below:

In the above example, after the test case debug, the execution status (“True/False”) of the specified query will be stored in the output variable {status}.

Find the snippet of the keyword below, for working with SQLite db, .mdb, and .accdb files:

In the above example, after the test case debug, the execution status (“True/False”) of the specified query will be stored in the output variable {status}.

Some database instances, like MongoDB, Redis, and Snowflake DB, require either the Collection/Warehouse name or the Query Type/Schema.

Find the snippet of the keyword below, for working with MongoDB:

Find the snippet of the keyword below, for working with Redis DB:

Find the snippet of the keyword below, for working with Snowflake DB:

Keyword: SECUREGETDATA

Description: This keyword or action instructs Avo Assure Client to run a query in the specified database from the input field and save the results in the output variable. Here, the password in the input field should be AES encrypted.

Keyword

Input Syntax

Output Syntax

Supported Input Formats

secureGetData

<DB IP/ instance name/ DB file path>; <DB port>; <DB username>; <DB Encrypted password>; <DB name>; <Query>; <DB Number>; <collection/ warehouse>(Optional); <querytype/ schema>(Optional)

{Variable}

  • String

  • Static values

  • Dynamic variables

Note:

  1. For MS Access files, an additional dedicated query is supported. In the input’s query argument, the user must specify “getAllTables” (case insensitive) to fetch all the table names in the DB file.

  2. To view the values of the variable, the user should use the following format: {OutputVariableName[2][3]}. This gives the value of 2nd row and 3rd column.

Find the snippet of the keyword below:

In the above example, after the test case debug, {result[1][2]} variable contains the value from the first row and second column of query results obtained from the specified DB.

Find the snippet of the keyword below, for working with SQLite db, .mdb, and .accdb files:

In the above example, after the test case debug, {DBData[1][2]} variable contains the value from the first row, second column of query results obtained from the specified DB file.

Some database instances, like MongoDB, Redis, and Snowflake DB, require either the Collection/Warehouse name or the Query Type/Schema.

Find the snippet of the keyword below, for working with MongoDB:

Find the snippet of the keyword below, for working with Redis DB:

Find the snippet of the keyword below, for working with Snowflake DB:

Keyword: SECUREVERIFYDATA

Description: This keyword or action instructs Avo Assure Client to run a query in the specified database from the input field and verify the data with the input file. Here, the password in the input field should be AES encrypted.

Keyword

Input Syntax

Output Syntax

Supported Input Formats

secureVerifyData

<DB IP/ instance name/ DB file path>; <DB port>; <DB username>;<DB Encrypted password>; <DB name>; <Query>; <DB Number>; <file path to verify data>; <Sheet Name>; <collection/ warehouse>(Optional); <querytype/ schema>(Optional)

{Variable}

  • String

  • Static values

  • Dynamic variables

  • File format supported: .xls, .xlsx and .csv

Find the snippet of the keyword below:

In the above example, after the test case debug, based on the comparison between the query result and the content of the specified file, the {status} variable contains the value “True/False”.

Find the snippet of the keyword below, for working with SQLite db, .mdb, and .accdb files:

In the above example, after the test case debug, based on the comparison between the query result and the content of the specified file, the {status} variable contains the value “True/False”.

Some database instances, like MongoDB, Redis and Snowflake DB, require either the Collection/Warehouse name or the Query Type/Schema.

Find the snippet of the keyword below, for working with MongoDB:

Find the snippet of the keyword below, for working with Redis DB:

Find the snippet of the keyword below, for working with Snowflake DB:

Keyword: SECUREEXPORTDATA

Description: This keyword or action instructs Avo Assure Client to execute a specified query from the input and export the result to an external file. Here, the password in the input field should be AES encrypted.

Keyword

Input Syntax

Output Syntax

Supported Input Formats

secureExportData

<DB IP/ instance name/ DB file path>; <DB port>; <DB username>; <DB Encrypted password>; <DB name>; <Query>; <DB Number>; <collection/ warehouse>(Optional); <querytype/ schema>(Optional)

<File Path>; <Sheet Name>

  • String

  • Static values

  • Dynamic variables

  • File format supported: .csv, .xlsx and.xls

Note: Sheet Name is optional.

Find the snippet of the keyword below:

In the above example, after the test case debug, the results from the specified query will be exported to the DB_export.xls file.

Find the snippet of the keyword below, for working with SQLite db, .mdb, and .accdb files:

In the above example, after the test case debug, the results from the specified query will be exported to the DB_export.xls file.

Some database instances, like MongoDB, Redis and Snowflake DB, require either the Collection/Warehouse name or the Query Type/Schema.

Find the snippet of the keyword below, for working with MongoDB:

Find the snippet of the keyword below, for working with Redis DB:

Find the snippet of the keyword below, for working with Snowflake DB:

Keyword: SECURERUNQUERY

Description: This keyword or action instructs Avo Assure Client to run the specified input query on the database specified in the input field. Here, the password in the input field should be AES encrypted.

Keyword

Input Syntax

Output Syntax

Supported Input Formats

secureRunQuery

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

(Optional)

  • String

  • Static values

  • Dynamic variables

Find the snippet of the keyword below:

In the above example, after the test case debug, the execution status (“True/False”) of the specified query will be stored in the output variable {status}.

Find the snippet of the keyword below, for working with SQLite db, .mdb, and .accdb files:

In the above example, after the test case debug, the execution status (“True/False”) of the specified query will be stored in the output variable {status}.

Some database instances, like MongoDB, Redis and Snowflake DB, require either the Collection/Warehouse name or the Query Type/Schema.

Find the snippet of the keyword below, for working with MongoDB:

Find the snippet of the keyword below, for working with Redis DB:

Find the snippet of the keyword below, for working with Snowflake DB:

PreviousDate OperationsNextMathematical Operations

Last updated