Getting started with the JDBC TestStep
Introduction
soapUI 3.5 introduces a new TestStep for retrieving data from a
database using JDBC. The result is formatted as XML and can be asserted or processed
in the standard
way (see
Test Request Assertions
).
TestStep Properties Configuration
For using the JDBC TestStep you will need to add a JDBC driver to
soapui_home/bin/ext folder and restart the application
The Open source version of JDBC TestStep has fields for manually setting the
connection settings as well as the SQL query
and an option for checking if
query is a stored procedure call. The query result
can be viewed only as plain XML.
The Pro version has option of using a connection configured at the project level and a graphical wizard
for building the SQL query.
There are two ways of supplying the JDBC connection settings:
-
Use a project level JDBC Connection
- in this case the driver/connection string/password properties will be populated with
configured values for the selected JDBC Connection (but the fields will be disabled and read-only).
-
Use text field values for driver/connection string/password - in this case the Database Connection is set to
"None".
The specified settings (i.e. JDBC Connection or driver/connection string/password) will be used when
building the query and running the TestStep.
The JDBC panel consists of the following components:
- Query properties - number of properties can be added for replacement in query through PreparedStatement.
Properties need to be specified in the query as ":propertyName" and will by replaced by exact names.
- JDBC Connection selection with options
- Create-new - adds a new JDBC Connection to project and preselects it here
- None - do not use a JDBC Connection, but use driver/connection string/password values instead
- Existing project-level JDBC Connection
- Button for configuring the selected JDBC Connection - enabled only
if a connection is selected (this will apply to the connection settings at project level)
- Driver
- Connection string
- Password
- Button for configuring connection properties - enabled only
if a connection is not selected, but plain connection properties are used
- Button for testing connection
- SQL Query
- Indicator if query is a stored procedure call
- Button for building the SQL query (soapUI Pro only)
Result can be displayed in
- XML view
- Outline view (soapUI Pro only)
Configuring DatabaseConnection
Based on the driver selected from the list of configured JDBC Drivers Properties you get the list of properties to fill/change
in order to configure the connection. Configuration changes will take immediate affect on the project level as well.
Configuring the SQL Query
There are two options for configuring the SQL query to be used:
- A plain listing of stored procedures along with available output parameters - launched when the stored procedure check box is selected
- A powerful visual query builder for creating complex SQL queries with unions and sub-queries - launched when stored procedure
check box is not selected
For the visual query builder there are two preview tabs at the bottom part of the window:
- SQL preview - where you can view the SQL query you've visually created and add properties to be used in the query
- Result preview - Displays current query results in form of a plain table with no additional options.
This preview should be used just to verify the SQL result before including the query in a TestStep.
There are a large number of possibilities when building your query visually, including
- Adding objects to the query - you can simply double click an object in the right tree or drag it to the query area
- By right clicking on the query area you can choose to add unions and derived tables also, along with existing objects.
- Joining tables - When two objects referenced with a foreign key relationship are added to the query
they become joined automatically with an INNER JOIN clause. To join them manually, you should select the field by
which you want to link the object with another and drag it to the corresponding field of the other object.
To define join type you may right click the link and select the Properties item from the context popup menu or
double-click it to open the Link Properties dialog.
- Setting object aliases
- Selecting output fields
- Sorting
- Defining criteria
- Grouping output fields
- Working with sub queries - You may add a sub-query as part of the expression or condition in the Columns Pane while editing text in a cell. To add a sub-query, right
click at the text position for a new sub-query and select the Insert Sub-query item from context popup menu
Happy testing, and please don't hesitate to give us your feedback!