Sections include:

Creating SQL Responders

For details on creating SQL Responders, see Creating SQL Responders from a Database Recording and Creating SQL Responders Manually.

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 Open in Table Editor 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 (like values in WHERE clauses and so on).

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. 

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, 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.

Editing

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 and choose Export to CSV.

To import the edited data, right-click the Result Set table and choose Import from CSV. Note that any existing entries in the repository will be overwritten upon import.

Best Practices

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 show all the SQL queries recorded. For each SQL Query, there may be parameters (like values in WHERE clauses and such). To see the result sets and parameters associated with a specific SQL query, select that query.

The result set files will appear 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 appear 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 conditions (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.

Repository Data

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).
 

CSV Data

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:

  1. Right-click the Virtual Asset Explorer node to which you want to add this tool and choose Add Output.
  2. 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.
  • No labels