Database testing with ACCELQ

ACCELQ supports logic development with database query executions and verifications against most major database providers. Current support includes Oracle, SQL Server, MySQL, PostgreSQL and Teradata. In addition, Stored Procedure executions are also supported with the same simple NL interface in the logic editor. Following sections describe various commands available for database testing.

Establishing Connection

You can establish connection to database in 2 different ways. For majority of cases, you can just supply information for simple DB credential fields while the system takes care of building the connection string. In cases where there are custom or proprietary fields required for connecting with DB, you can use the other variant, where you supply the entire JDBC connection string as an argument.

Every connection command includes a Session Name, which will be used to refer to this connection in subsequent operations such as data extractions and verifications. You can establish multiple DB connections with different reference names, so that you can easily shuffle between them based on your requirement for database logic.

Connect to Database using Key/Value Parameters

Following commands are helpful to establish a connection with respective database types. 

• Connect to Oracle Database
• Connect to MySQL Database
• Connect to SQL Server Database
• Connect to PostgreSQL Database
• Connect to Teradata database
• Connect to DB2 database
• Connect to Informix database
• Connect to Snowflake database

Here is a listing of various parameters required for different database types

Session Name

Server

Port

Database Identifier (oracle)

Database Identifier Type (oracle)

User Name

Password

Database name (PostgreSQL, MySQL and SQL Server)

Server Instance (SQL Server)

Authentication Type (SQL Server)

Domain Name (SQL Server)

Authentication Type (PostgreSQL)

Azure app tenant id (PostgreSQL)

Azure app client id (PostgreSQL)

SSL required (PostgreSQL)

Unique name for the session to refer subsequently

Database server name or IP

Database server port number

Database Identifier (for Oracle database)

Specify if the identifier is an SID or service name

User name for the db access

Password for db access (basic authentication)

Database name for applicable databases

Server instance to be specified for SQL server

Specify if Windows or SQL Authentication

Domain Name for SQL Server

Specify if basic or azure active directory Authentication

tenant id from azure app (If Azure AD Authentication)

client id from azure app (If Azure AD Authentication)

yes or no

Connect to Database using JDBC Connection String

This is a more free-form connection command, where you will directly supply JDBC connection string as an argument. Consult with your developer or database administrator to get the syntactically correct connection string.

You can also use this command for databases that are not listed directly with the named connection commands above, such as the Ingres database (as shown in the example below).

Type "jdbc connection" in the logic editor and select the command.

Session Name

Connection String

Unique name for the session to refer to this connection in the current action logic

JDBC connection string to connect to the database

Once a connection is established with this command, you can refer to this session name, and utilize all other available commands for querying the DB or parsing the result set as described in this article.

Switch to Database Session

When you are working with multiple database connections simultaneously, this command allows you to switch to the required database session. Once the session is switched, all subsequent database logic will run against this session. Make sure the DB connection is already established with this session name in the current action logic.

Note: When you just create a DB connection, the current session is already set to this connection. There is no need to use this command to switch to this session. Use this command only when you have multiple DB connections in your action logic and you need to switch back and forth between these connections.

Session Name

Reference name of the session to switch to

Close Current Database Session

Closes the currently active database session. To close multiple sessions, incorporate Switch Database Session command followed by the close command.

Running Queries

Execute Database Query

Executes given database query on the currently active DB session.

Query

Query Reference Name

Returns

SQL query to be executed. This can include Select, Insert, Update, Delete etc.

Reference name for this Query to run data extractions and verifications in subsequent steps

Number of rows affected by the query

Get Database Field Value

Retrieves the required field value from the results of the given database query. Note that the database query should have already been executed with the given reference name. Row number and column number starts from 1.

query ref name

row number

column specification

column

Returns

reference name of the query from which results are retrieved

row number in the executed query results

Specify if column is mentioned as column index (starts from 1) or column name 

column index or column name to extract the value from

required field value from the results of given db query

Verify Database Field Value

Verifies the value in the result of db query, with an expected value. Note that the database query should have already been executed with the given query reference name.

query ref name

row number

column specification

column

expected value

failure action

reference name of the query from which results are retrieved

row number in the executed query results

Specify if column is mentioned as column index (starts from 1) or column name 

column index or column name to extract the value from

value to verify against (case sensitive)

whether to abort or continue the test if the verification fails

Set DB Query ResultSet Prefetch Option

This command toggles the database resultset prefetch option, which is off by default. Enable it only in exceptional cases, like when dealing with unsupported column types (e.g., MONEY, NVARCHAR in SQL Server, CLOB in Oracle). Note that enabling prefetch may slightly impact performance. Turn it on/off as needed in your logic, ensuring it's active only for necessary queries. It resets to 'off' with each new test case execution.

prefetch option

Database resultset prefetch option yes/no.

Database Query by Example

Here is an example of a Database action with verification on the UI. This sample logic performs following steps:

1. Connect to db (Connect to MYSQL Database)
2. Execute a query to get recent transactions. (Execute Database Query)
   • Note: Query execution returns the number of records from the result set of the query.
3. Loop on the number of results (Repeat Loop)
   • Read from the result set of the executed query. (Get Database Field Value)
   • Verify the text on "tx amount" on the screen matches with the value read from the database. (Verify Element Text)
   • Note: "tx amount" is setup as a multi-match (repeat) element, as it repeats a number of times based on the count of transactions. We are updating the ordinal index of the element before making the verification.