Home Docs JDBC Testing JDBC Databases

Testing JDBC Databases

This topic describes how to work with JDBC databases. This feature is only available in standalone SoapUI Pro that has been replaced with ReadyAPI. To try the new functionality, feel free to download a ReadyAPI trial.

ReadyAPI has some very useful features in addition to the open source version:

  1. Using JDBC connections configured on a Project level
  2. Using wizards for creating more complex queries.
  3. Displaying result XML in Outline view (and by that using wizards for XPath assertions available in this view).

jdbc-teststep-pro-plain-with-stamps

There are two ways of supplying the JDBC connection settings:

  • Use a project level JDBC Connection - in this case the driver, connection string and 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 and password) will be used when building the query and running the TestStep.

The JDBC panel consists of the following components:

  1. 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.
  2. 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
  3. Button for configuring the selected JDBC Connection - enabled only if a connection is selected (this will apply to the connection settings at project level)
  4. Driver
  5. Connection string
  6. Password
  7. Button for configuring connection properties - enabled only if a connection is not selected, but plain connection properties are used
  8. Button for testing connection
  9. SQL Query
  10. Indicator if query is a stored procedure call
  11. Button for building the SQL query (ReadyAPI only)
  12. Assertions panel - JDBC result can be asserted just like any other xml result with two additional assertions: JDBC Timeout - that asserts if query was executed in predefined amount of time and JDBC Status - that asserts the execution query status
  13. Result can be displayed in XML view
  14. Outline view (ReadyAPI only)

Configuring DatabaseConnection

Based on the driver selected from the list of configured JDBC Drivers Properties you get the list of properties to fill or change in order to configure the connection. Configuration changes will take immediate affect on the project level as well.

database_connection_configuration

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

    sp_call_query_panel
  • A powerful visual query builder for creating complex SQL queries with unions and sub-queries - launched when stored procedure check box is not selected

    build_query_panel

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.

result_preview

There is 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.
    add_to_query
  • 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 pop-up menu or double-click it to open the Link Properties dialog.
    link_properties
  • 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 pop-up menu
    insert_subquery