# 2.10 - SQL Testing with Appvance SQL Testing with Appvance — Appvance IQ documentation. # SQL Testing with Appvance ## Contents - [How to Create a Valid SQL Script](/books/sql-testing-with-appvance/page/how-to-create-a-valid-sql-script) - [Setting up the Environment](/books/sql-testing-with-appvance/page/setting-up-the-environment) - [Creating a New Database Scenario](/books/sql-testing-with-appvance/page/creating-a-new-database-scenario) - [Execution Results](/books/sql-testing-with-appvance/page/execution-results) - [Sample SQL Scripts](/books/sql-testing-with-appvance/page/sample-sql-scripts) - [Reduce Size and Free Up Storage in MySQL Results Database](/books/sql-testing-with-appvance/page/reduce-size-and-free-up-storage-in-mysql-results-database) - [Appendix](/books/sql-testing-with-appvance/page/appendix) ## Appvance SQL Client The Appvance SQL Client allows you to perform SQL queries on an existing database and analyze the query data such as query timing (step time) and response data (requests data). There are four ways to use SQL files in AIQ: - SQL Scripts - SQL within Web Designer - SQL within Services Workbench - SQL within Mobile Designer The Appvance SQL Client (`processSQL`) supports connections to the following: - MySQL - Oracle DB - MS SQL - Ignite - Snowflake - Redshift - PostgreSQL The following topic explains how the Appvance SQL Client works and provides you with the basic knowledge about the configuration needed to successfully configure and run a SQL test scenario using the Appvance SQL Client. It’s important to know that Appvance requires only a “.sql” script to run the test, no additional advanced configuration settings are required from Appvance to run the test. However, this script is the one that must contain basic configuration data needed to connect to the desired database. This document assumes that you already know SQL syntax. It’s important to mention that each SQL script that you provide to Appvance SQL Client not only must contain the connection settings to perform the queries, but also the proper SQL syntax valid for the SQL database engine that you are trying to reach. I.E: MySQL, SQL Server etc. Most SQL engines have restrictions on the SQL syntax, this is why it’s important for the user who is creating the script, to know these specific limitations before start creating the script. Supported SQL statements are UPDATE, INSERT, DELETE, CALL, SELECT, EXECPROC. In the case that the above supported SQL statements are not enough, you create a stored procedure and then use the CALL command to execute it. You can use the comment notation to change the database using: `-- Database=aiq` You are not limited to use just one ‘commented’ action per .sql script. So for example in a multiple SELECT .sql script you can add several `storeInLocker` lines, like `-- storeInLocker` Adding storeInLocker after the wanted SELECT statements allows you to shared multiple results with other scripts. (edited) ## Creating a Valid SQL Script To create a valid SQL script, you need to understand the basic configuration settings embedded into the script file to create a connection. These settings are based on **JDBC** to map the database configuration settings correctly the only thing you will need to specify is the following: `-- dbvendor= (MYSQL,ORACLE,MSSQL,IGNITE,SNOWFLAKE,MSSQL,REDSHIFT,POSTGRESQL)` `-- username= (User that is owner of the database objects)` `-- password= (Related password)` `-- host= (dns name for database host)` `-- schema= (database or schema)` `-- port= (Port for related database vendor or custom port) Defaults: mysql 3306, oracle 1521, MSSQL 1433, IGNITE 10800, SNOWFLAKE 0, REDSHIFT 5439.` `-- limit= - (Portion of the result set of rows that would depend of the limits established)` `-- connect (Connecting to the specified database)` `-- disconnect (Disconnecting from the specified database)` `-- tag= (Text identifier for the object in The Locker or the Local White Board)` `-- storeInLocker= (Storing an object in the locker, if no value is specified the first value will be the first one, must be blank in combination of limit, take a look to Communication Intra-Steps Using TheLocker)` `-- storeInWhiteboard= (Storing an object in the white board, if no value is specified the first value will be the first one, must be blank in combination of limit, take a look to WhiteBoard DPL)` `-- retrieveFromTheLocker=varName (Retrieve the variable varName from the locker, which can then be use in the sql queries as :varName)` `-- warehouse= Parameter required by SNOWFLAKE engine when not provided in URL` `-- db= Parameter required by SNOWFLAKE engine when not provided in URL` `-- account= Parameter required by SNOWFLAKE engine when not provided in URL` `-- role= Parameter required by SNOWFLAKE engine when not provided in URL` `-- step= (During de definition of a SQL script testing based testcase, it is possible to establish each sql statement as a step with a name)` The following configuration lines have been deprecated: `-- driver_class= (DEPRECATED) When not provided this will default to com.mysql.jdbc.Driver` `- MySQL: com.mysql.jdbc.Driver` `- Oracle: oracle.jdbc.driver.OracleDriver` `- MSSQL: com.microsoft.sqlserver.jdbc.SQLServerDriver` `- Ignite: org.apache.ignite.IgniteJdbcThinDriver` `- Snowflake: net.snowflake.client.jdbc.SnowflakeDriver` `- Redshift: com.amazon.redshift.jdbc.Driver` `-- url= (DEPRECATED) Endpoint containing all needed parameters to make the connection. If this is not available then URL will be constructed out of other options according to each engine specs.` All of these items are required, except for the port token that will have the default port number if omitted, -- connect will use previous information to create a connection to the desired database,-- disconnect would be the token the parser finds to disconnect from the database. These last six tokens need to be the header of the SQL file and SQL statements must be stated between the "–- connect" and the "–- disconnect" tokens. ## SQL Tokens We are including the most used DML sentences in the set of possibilities as per the necessities of testing, as below. ### Select Performs a select query where a row (can be chosen) of the obtained values can be stored in the locker (later it will be covered in the document) ### Update Performs an update sentence, the status of the operation will be registered in the request. ### Call Execute an Oracle or MySQL stored procedure, the resulted row will be stored in the locker. ### ExecQuery Execute a Microsoft SQL server-stored procedure, resulting row will be stored in the locker. ### ExecProc execute a Microsoft SQL server stored procedure that does not return values, just a status of the result of the operation in the request. ### Create Execute a Microsoft SQL server stored procedure that does not return values just an status of the result of the operation in the request. ### Limit Used to limit the number of results returned by the select query ## Creating a new scenario using SQLClient Now it’s time to create a new Appvance scenario and use it to run a SQL script using SQLClient to execute it. We assume a basic knowledge of the Appvance Scenario Builder system so this document will not go into details about it, we will only set up the Test Case for the test. 1. In the Test Case section, you have to select “SQL” from the drop-down menu located next to “Script Type”, this will indicate Appvance to use SQLClient. 2. Then in the “SQL File” you have to click “Browse” and upload the .sql script file you just created in the steps above. ![](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/EeRlOC9yC6cGi52g-4d3e2e72a125.png) 3. By default, the step name displayed on the results screen is the same name as the request, which is the SQL actions in the script. However, as you can notice, this time the step name is being displayed with a custom name. We can do this by adding some extra lines to the script which will be used to provide a custom step name to ASE. The way to do this it’s just by adding a new instruction to the script right before the SQL statement with the following syntax: **-- step=**<Step Name String>. Where **<Step Name String>** must be replaced with the custom step name you want to have displayed on the results screen, for example **: -- step=Select All** . This will display the SQL action as a step with the name “Select All”. Our .sql script settings for this execution are the following: ![](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/Jsi2Y2rb5Ol8bdgB-3e6aa747806b.png) If you’d like to validate the data retrieved from the SELECT action. You can have downloaded the request response for this step. You can do this by navigating to the panel located at the right of the results display screen. Then select “Testcase Executions”, you will see all of the test cases executed on that test, if you have only one then you should see just one registry. Click the “Request & Response” tab and then click the test case execution registry above to have its data displayed. Once you do this the requests and responses data get displayed in the “ Request & Response” tab. This tab shows the steps requests and responses identified by each step name. If you click the request that belongs to the SELECT statement, this action will download a file named “request”. This is a standard plain text file with no extension, therefor you can open this file with a word processor such as NotePad or Sublime (recommended). When you open this file, you will see all of the records that were retrieved from the SELECT statement from your test case execution. This way you can validate the data contained in your database registries against the test response. ![](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/VGQE4RAg6j9zfExQ-4d800d57ed6a.png) This test just simply inserts a new record into the database and then selects all of the records out of the database. Also, we will see how to validate the data retrieved from the SELECT statements. After setting up the test case configuration you are ready to go, you just have to click “Play” once the scenario has been created. ### SQLClient Test Designer Executions There are two reasons it is desired to issue SQL scripts, first one is in a testcase to test queries against a specific database (as shown in the previous part) and the second one is when you use SQL scripts to extract data to the locker by performing select sentences or stored procedures or it is required to execute update sentences or execute a stored procedure that performs operations related to the objects of the database, in that case, no result will be stored in the locker. ![](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/d1QkkjXkKJfKV9g7-f2edda0edee9.png) ### SQLClient Service Suite Executions It's related to the **processSQLFile** service suite call and allows the service suite to extract either a row or a multiple rows by returning a **JSONArray** of **JSONObject** as below. #### test\_13.sql ![](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/wWDiCtVRIurW68RW-bab6d5787bf3.png) ![](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/kX6A8OO1TIB3Hpks-6114b69121cc.png)
--- Last updated: September 20, 2024 If you have any feedback on this topic, please contact us at
# How to Create a Valid SQL Script To create a valid SQL script in Appvance AIQ, you must configure the database connection properties correctly. These properties are based on Hibernate and are used to map the database configuration and establish a connection through the appropriate JDBC driver. The following configuration values are required: - User Name - Password - URL (Connection String) - Driver Class - Database Name - Dialect - Connection Delimiters All of these values must be correctly configured in the SQL script when using ASE to establish a database connection. ## Database Connection Properties ### 1. User Name The **User Name** is the database account used to authenticate and access the required database. Make sure that: - The user exists in the database. - The user has the required permissions to perform the requested operations. - The database allows connections from the machine where the script is executed. - For remote databases, remote access is enabled and permitted by the database and network configuration. ### 2. Password The **Password** is the password associated with the specified database user. The credentials must match a valid database account. If authentication fails, verify the username, password, and database access permissions. ### 3. URL The **URL** specifies the JDBC connection string used to connect to the database. The format of the URL depends on the database engine and JDBC driver being used. For example, a typical MySQL JDBC connection URL can be structured as follows: ```text jdbc:mysql://:/ ``` Additional connection parameters can be included when required by the database or JDBC driver. ### 4. Driver Class The **Driver Class** identifies the JDBC driver that Appvance uses to establish the database connection. Each database engine has its own JDBC driver class. For example, the current MySQL Connector/J driver class is: ```text com.mysql.cj.jdbc.Driver ``` > **Note:** Older MySQL documentation may reference `com.mysql.jdbc.Driver`. This class name was deprecated in MySQL Connector/J 8.0 and should not be used for current configurations. Make sure the appropriate JDBC driver is available to the AIQ environment where the SQL script is executed. ### 5. DB Name The **DB Name** is the name of the database that the script connects to. It must correspond to an existing database that is accessible by the configured user. For example: ```text my_database ``` The database name is also commonly included as part of the JDBC connection URL. ### 6. Dialect The **Dialect** specifies the Hibernate dialect used to communicate with the selected database engine. For Appvance AIQ, the dialect must use the Appvance-specific Hibernate dialect package: ```text org.appvance.hibernate.dialect ``` The Appvance dialect package is based on Hibernate dialect implementations and was modified to include the `appvance` package name to avoid runtime conflicts. The appropriate dialect must be selected according to the database engine being used. Refer to the supported dialect table in this documentation for the available values. ### 7. Connection Delimiters The SQL script must include the commands required to explicitly open and close the database connection. The connection delimiters are: ```text Connect Disconnect ``` **Connect** starts the database connection using the configured connection properties. **Disconnect** closes the database connection after the SQL operations have completed. These commands should be used to clearly define the beginning and end of the database interaction. ## Example Configuration A valid SQL script must contain the required connection information and use the appropriate connection delimiters. The exact syntax and values depend on the database engine and JDBC driver being used. Before executing the script, verify that: 1. The database user and password are valid. 2. The database is reachable from the AIQ execution environment. 3. The JDBC URL is correctly formatted. 4. The required JDBC driver is available. 5. The database name is correct. 6. The appropriate Appvance Hibernate dialect is configured. 7. The `Connect` and `Disconnect` delimiters are present. 8. The database user has sufficient permissions for the SQL operations performed by the script. --- **Last updated: August, 17 2026** If you have any feedback on this topic, please contact us at . # Setting up the Environment Now let’s see how to create an SQL script using the required configuration settings. In this example, we use a MySQL database named `appvancetest` with a `worker` table containing the following columns: - `idworker` - `name` - `lastname` The database can be hosted on a local or remote machine. MySQL uses port `3306` by default, although the port may vary depending on your database configuration. ## 1. Configure the database connection The first step is to add the Hibernate settings required to connect to the database and execute the script. The configuration settings must be defined at the beginning of the SQL script, before any SQL actions are executed. As shown in the example, provide the appropriate value for each setting required to establish the database connection. These settings are specific to your database environment. For example, the database host, database name, username, password, and port must correspond to the MySQL instance you want to access. [![image.png](https://docs.appvance.net/uploads/images/gallery/2026-08/scaled-1680-/l1NTkK61H6zQtrhN-image.png)](https://docs.appvance.net/uploads/images/gallery/2026-08/l1NTkK61H6zQtrhN-image.png) ## 2. Add the SQL actions After defining the connection settings, add the **connect delimiter** before the SQL actions. You can then add the SQL statements that you want to execute. Once all SQL actions have been defined, add the **disconnect delimiter** to close the database connection. For example, a script that inserts a new record into the `worker` table follows this structure: [![image.png](https://docs.appvance.net/uploads/images/gallery/2026-08/scaled-1680-/vRQBU77x6zNxh0YN-image.png)](https://docs.appvance.net/uploads/images/gallery/2026-08/vRQBU77x6zNxh0YN-image.png) ``` -- Hibernate configuration settings connect INSERT INTO worker (idworker, name, lastname) VALUES (1, 'John', 'Doe'); disconnect ``` The exact configuration values and SQL statement will depend on your database environment and the operation you want to perform. These are the basic steps for creating a valid SQL script for ASE and using it in a scenario to execute database-related test actions. [![image.png](https://docs.appvance.net/uploads/images/gallery/2026-08/scaled-1680-/AhkPZKXPRpPYTjrW-image.png)](https://docs.appvance.net/uploads/images/gallery/2026-08/AhkPZKXPRpPYTjrW-image.png) ### Important - The configuration settings must be placed before the SQL actions. - Type `--` before each configuration setting so that ASE recognizes the setting. - The SQL script must use the `.sql` file extension. - Make sure the database host, port, credentials, and database name are valid and accessible from the environment where the script is executed. --- **Last updated: August 13, 2026** If you have any feedback on this topic, please contact us at . # Creating a New Database Scenario Once you have created a valid SQL script, you can create an AppVance Scenario to execute it using the Scenario Builder. This article assumes basic knowledge of AppVance Scenario Builder and focuses only on the configuration required to create a Database Scenario and execute a SQL script. ## Configure the Scenario ### 1. Select the Database Scenario Type In the **Info** section of the Scenario Builder, select **Database** as the **Scenario Test Type**. ![KIog241FjXGrQvrY-181b40f78b88.png](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/KIog241FjXGrQvrY-181b40f78b88.png) ### 2. Configure the SQL Test Case In the **Test Case** section: 1. Select **SQL** from the **Test Type** drop-down menu. 2. In the **SQL File** field, click **Browse**. 3. Select and upload the `.sql` script you created previously. Selecting **SQL** as the Test Type tells AppVance to use the **ASE** database execution mechanism.![m7q6OJ9qGc0lyOkd-d58103045542.png](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/m7q6OJ9qGc0lyOkd-d58103045542.png) ## After configuring the Scenario and uploading the SQL file, save the Scenario. Click **Play** to execute the Scenario and run the SQL script. Before executing the Scenario, make sure that the database connection settings defined in the SQL script are valid and that the target database is accessible from the environment where the Scenario is running. --- **Last updated:** September 2, 2026 If you have any feedback on this topic, please contact us at # Reviewing SQL Test Execution Results This section explains how to review the results of a SQL test execution and how to customize the step names displayed in the execution results. In this example, the SQL test inserts a new record into the database and then retrieves the records using a `SELECT` statement. The execution results can then be reviewed to validate the data returned by the database. The following example shows the results of a SQL test executed against a MySQL database: ![0U96wdbhIHbxgnWb-7c7fa4af8ab2.png](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/0U96wdbhIHbxgnWb-7c7fa4af8ab2.png) ## Customizing SQL Step Names By default, the step name displayed in the execution results corresponds to the SQL action or request defined in the script. You can assign a custom name to a SQL step by adding a `-- step` instruction immediately before the corresponding SQL statement. Use the following syntax: ``` -- step=Custom Step Name ``` For example: ``` -- step=Select All SELECT * FROM users; ``` The SQL action is displayed in the execution results using **Select All** as the step name. This can make execution results easier to understand, especially when a SQL script contains multiple SQL statements. The `.sql` script settings used for this execution are shown below: ## Reviewing ![Fjm3QJ02JSjivQrm-5198a07e5b97.png](https://docs.appvance.net/uploads/images/gallery/2026-07/scaled-1680-/Fjm3QJ02JSjivQrm-5198a07e5b97.png)SELECT Results You can review the data returned by a `SELECT` statement from the **Request & Response** section of the execution results. To review the response: 1. Open the SQL test execution results. 2. In the panel on the right side of the results screen, select **Test-case Executions**. 3. Select the test case execution you want to review. 4. Open the **Request & Response** tab. 5. Select the step associated with the `SELECT` statement. The **Request & Response** tab displays the requests and responses associated with the individual execution steps. The custom step names assigned with the `-- step` instruction can be used to identify the corresponding SQL statements. Selecting the request associated with the `SELECT` statement downloads the corresponding request data as a plain-text file. The downloaded file does not have a file extension, so it can be opened with a text editor such as Notepad or Sublime Text. The file contains the records returned by the `SELECT` statement during the test execution. You can use this information to verify that the database returned the expected data and validate the results of the SQL operation. --- Last updated: September 3, 2026 If you have any feedback on this topic, please contact us at . # Sample SQL Scripts ## MySQL with limit
``` -- dbvendor=MYSQL

-- username=aiq

-- password=thePassword

-- host=mysqlsmoketest.c7nn5t4czzya.us-west-2.rds.amazonaws.com

-- schema=aiq

-- port=3306

-- limit=21-50

-- tag=executions

-- connect

SELECT * FROM `aiq`.`UTPScenarioExecution`;

-- storeInLocker=

-- disconnect ```
## Redshift
``` -- dbvendor=REDSHIFT

-- username=theUser

-- password=thePassword

-- host=sqlclient-test.cqzappbyyyx5.us-east-2.redshift.amazonaws.com

-- schema=dev

-- port=5439

-- limit=1-50000

-- tag=saleswb

-- connect

select sellerid, username, (firstname ||' '|| lastname) as name,

city, sum(qtysold)

from sales, date, users

where sales.sellerid = users.userid

and sales.dateid = date.dateid

and year = 2008

and city = 'San Diego'

group by sellerid, username, name, city

order by 5 desc;

-- storeInWhiteboard=

-- disconnect ```
## Snowflake
``` -- dbvendor=SNOWFLAKE

-- region=us-west-2

-- account=qrazxhk-kiw37206

-- role=ACCOUNTADMIN

-- username=theUser

-- password=thePassword

-- warehouse=COMPUTE_WH

-- db=APPVANCE

-- schema=AIQ

-- connect

update COUNTRIES 

set COUNTRY_NAME='NAME CHANGED' 

where COUNTRY_CODE=999;

-- disconnect ```
## MSSQL
``` -- dbvendor=MSSQL

-- username=theUser

-- password=thePassword

-- host=mssqlsmoketest.c6mn5c3czyyb.us-west-2.rds.amazonaws.com

-- schema=master

-- port=1433

-- connect

call sp_tables;

-- storeInLocker=

-- disconnect ```
## Oracle
``` -- dbvendor=ORACLE

-- username=theUser

-- password=thePassword

-- host=oraclestest.c4nm5t3czzya.us-west-2.rds.amazonaws.com

-- schema=ORCL

-- port=1521

-- connect

SELECT aiq.getversion() FROM dual;

-- tag=results

-- storeInLocker=

-- disconnect ```
--- Last updated: September 19, 2024 If you have any feedback on this topic, please contact us at
# Reduce MySQL Results Database Storage Usage # Reduce MySQL Results Database Storage Usage Use this procedure when the AIQ results database is consuming excessive storage. Treat database cleanup as a controlled maintenance task because deleting execution data can permanently remove historical evidence and reports. ## Before making changes - Confirm the affected MySQL instance, schema, and available disk space. - Create a verified backup or snapshot before deleting or archiving results. - Confirm the retention window with the customer or project owner. - Prefer supported AIQ cleanup, archival, or retention settings before using direct database changes. ## Recommended cleanup workflow 1. Identify the largest result tables and the date range that can be archived or removed. 2. Export any reports or evidence that must be retained. 3. Run cleanup in a maintenance window with AIQ services stopped or quiesced if required by the procedure. 4. Optimize or analyze tables only after the cleanup approach has been validated. 5. Restart services and verify that recent executions, reports, and login flows still work. **Warning:** Do not run broad delete statements without an approved backup and an exact retention target. *Last updated: October 8, 2026* # SQL Testing Appendix for AIQ ## Recommended guidance Use the SQL Testing appendix for supplemental reference material that supports active SQL testing workflows. Keep appendix content concise, current, and linked to the main SQL testing guidance. ## What belongs here - Reference examples that are too detailed for the main article. - Terminology, troubleshooting notes, or environment-specific reminders. - Links to approved SQL call, data, and validation articles. - Warnings about database permissions, sensitive data, and cleanup. ## Maintenance checklist 1. Remove duplicate examples that already exist in active SQL pages. 2. Replace Appvance wording with AIQ where appropriate. 3. Use placeholders instead of customer-specific connection strings or credentials. 4. Validate examples against current Service Suite or SQL testing behavior. *Last updated: October 7, 2026*