You can use the Query Wizard to build custom JDBC database queries. It provides a visual interface and helps you set up some simple filtering rules.
Click the image to enlarge it.
Adding a Database Query
With the Query Wizard, you can quickly select what fields you need from the database tables:
-
Select the table with the data you need on the right panel and drag it to the middle panel.
Click the image to enlarge it.
-
ReadyAPI will create a visual representation of the table. Select the fields you need from it.
Click the image to enlarge it.
-
The fields you have selected will appear in the tree view on the left, lowermost table, and the manual editor at the bottom.
Click the image to enlarge it.
ReadyAPI will create a query that gets all data from the table you need. To run that query, click 
.
The wizard will display the results on the Result Preview tab. To close the wizard and save changes, click OK.
 |
When working with data sources, you can select Add Properties to create properties for the values returned by the query automatically. |
Configuring the Query
After you have set up the query, use the table in the middle to modify it.
Click the image to enlarge it.
In this table, you can set up the following values:
| Column | Query Argument | Description | ||
|---|---|---|---|---|
| Output | N/A | Remove selection to disable posting of received data when testing the query. Does not affect the query text. | ||
| Expression | Select | The SQL expression specifying the column to get. | ||
| Aggregate | One of SQL aggregate functions (varies) |
The SQL aggregate function to use. These functions return a single value from the column. The availability of specific functions depends on your database. To learn about the most commonly used functions, see the w3schools website. |
||
| Alias | As |
The correlation name assigned to the column. You can use it instead of the column name.
|
||
| Sort Type | Order By (Desc) | Sorts the returned column in the ascending or descending order. Affects the order in which you access the values. | ||
| Sort Order | The order of items in the Order By line | The order in which columns will appear in the output. | ||
| Grouping | Group By | Select to group the results of one or multiple columns. | ||
| Criteria for | Where if For values Having if For groups |
Select, if you use the Where or Having clause to narrow down returned values. | ||
| Criteria | The criteria in the Where or Having line | The clause condition. For example: < 5, = OK. |
||
| Or | Additional criteria | Additional clause conditions to be added after the first one. You can specify up to two additional conditions. |
| Tip: | You can also use prepared properties in your queries. Click  to add a property. Then, add this property to the query in the editor below. |