This topic explains how to configure the SQL Responder tool, which virtualizes database responses to various queries. Responders are the core tools used in Parasoft Virtualize.
Creating SQL Responders
Modifying Queries and Results
Each SQL Responder handles SQL queries for a particular JDBC URL. When a query is received, it is matched against the set of available query templates. If a match is found, the correlated result set is used as a response. You can customize the virtualized database behavior by modifying the queries that the SQL Responder handles and the results it delivers.
To modify the queries that the tool handles, add or edit SQL templates. Wildcards can be used.
To modify the results that are delivered, add or edit the associated parameters and result sets.
The manner in which you add and edit data depends on whether the SQL Responder was created to be parameterized from a CSV or from a Parasoft Data Repository.
Editing Data Stored in a Data Repository
To edit data stored in a Data Repository, click the Open in Table Editor button in the SQL Responder editor.
This opens the associated data repository editor—where you can review and edit/extend the stored values. You can also open the data repository editor by double-clicking the appropriate SQL Data Sets node in the Data Repositories view.
Initial View (Level 1)
The initial view of the data repository will always show three columns at the top level:
- JDBC Connection URL (string): The name of the database recording you selected in the wizard.
- SQL Template (string): The SQL queries recorded. When the SQL Responder receives a query, it tries to match it against the set of available query templates. If a match is found, the correlated result set is used as a response.
- SQL Parameters (list of data set records): The parameters recorded for the above SQL queries (values in WHERE clauses, etc).
JDBC Connection URL and SQL Template are key columns (as indicated by the light purple coloring). The yellow coloring in the SQL Parameters column indicates that you can drill down into it.
SQL Parameters Table (Level 2)
Double-clicking a value in the SQL Parameters column opens the SQL Parameters table, which always contains the following columns:
- Result Set: Lets you drill down into result set values.
- Additional Response Delay: Lets you adjust timing as described in Modifying Response Times).
If any parameters were associated with the recorded query (values in WHERE clauses, etc), additional columns will be added to allow you to view and edit related parameter values.
Result Set Table (Level 3)
From the SQL Parameters view, double-clicking a value in the Result Set column opens the Result Set table, which allows you to edit the values that will be used in a response.
To review, edit, and extend the SQL templates, result sets, and associated parameters, use the data repository editor functionality, which is described in Viewing and Modifying the Repository Structure and Contents.
If you prefer to edit Result Set data in csv format, you can export data, manipulate it in a csv editor, then reimport the edited file.
To export Result Set data in CSV format:
- Right-click the Result Set table, then choose Export to CSV.
To import the edited data,
- Right-click the Result Set table, then choose Import from CSV. Note that any existing entries in the repository will be overwritten upon import.
Dos and Don’ts
When editing or extending SQL data sets:
- Avoid editing recorded JDBC URLs. They must exactly match the JDBC URLs that the database is configured with.
- If you add any SQL queries, set the parameters (if any) and result set (if any).
- You can use expressions as parameter matching criteria in SQL Templates, SQL Parameters, and Result Sets. For details, see Using Criteria Expressions to Match Values.
- If you want to adjust timing, you can modify response times as described in Modifying Response Times.
Note that existing records from SQL data sets can’t be reused.
Editing Data Stored in a CSV File
The Queries panel will display all the SQL queries recorded. For each SQL Query, there may be parameters (values in WHERE clauses, etc). To see the result sets and parameters associated with a specific SQL query, select that query.
The result set files will display in the Parameter Criteria table.
To edit the SQL templates, use the controls in the Queries panel. You can add or remove entries as needed.
To edit result sets and parameter criteria, first select the correlated query. The name of the associated CSV file will then display in the Parameter Criteria area. These CSV files are stored within the database_recorded_data folder inside the VirtualAssets project. To edit the result set values, edit those CSV files.
To edit parameters, modify them directly in the Parameter Criteria area.
Using Criteria Expressions to Match Values
To configure the tool to use expressions as parameter matching criteria, you can use criteria expressions syntax, which supports comparing strings and numbers as well as matching strings using wild-cards and regular expressions. This is described in Criteria Expressions for Matching Values.
For example, the following configuration is set to match the exact salary of 80,000 to one ResultSet file and the exact salary of 100,000 to another.
You could use criteria expressions to match all salaries up to 80,000 to one ResultSet file, match all salaries between 80,000 and 100,00 to another, and match all salaries above 100,000 to a third ResultSet File. Or, you could use [*] in the SQL Template area to create a "catch all" for unmatched SQL queries.
Modifying Response Times
You can modify the SQL Responder to reflect different database response times—for instance, to reflect realistic database performance or to simulate how performance might vary under different con-ditions (such as the size of database and tables, the number of tables used in executing the query, the existence of indexes, and server load).
If the SQL Responder is triggered to use a result set with an additional response delay, it will pause for the amount of time specified in the Additional Response Delay (ms) column—plus any additional time specified in the virtual asset’s performance profiles, which are described in Working with Performance Profiles —before it returns the result set. The time specified in the Additional Response Delay (ms) column is a per query delay. For example, if you specify a delay of 5000 ms here and two queries are executed, the additional delay will be 10,000 ms.
Note that additional response delays apply to all queries—including metadata queries.
If the data is stored in a repository data source, response time adjustments can be made by altering the Additional Response Delay (ms) column value in the SQL data set (under SQL Parameters).
If the data is stored in a CSV file, response time adjustments can be made by altering the Additional Response Delay (ms) column value in the Parameter Criteria panel.
Chaining Tools to the Responder’s SQL Query or Result Set
You can chain tools to a SQL Responder’s incoming request (SQL Query) or outgoing response (Result Set) as follows:
- Right-click the Virtual Asset Explorer node to which you want to add this tool.
- Choose Add Output.
- Select the desired output type on the left and the desired tool on the right. For example, if you want a custom tool to perform some action on the query, you would select Incoming Request> SQL Query on the left, then the appropriate tool on the right.