Direct to Database Testing

Document created by Chris Sommerstad Employee on Jul 20, 2017Last modified by Jijius Jabez on Aug 10, 2017
Version 2Show Document
  • View in full screen mode

Direct-to-Database Testing, allows users to directly connect to databases, and to execute SQL statements against them for functional or performance testing.

Note: This feature is NOT enabled in environments automatically. Please contact your SOASTA sales person about having this important feature enabled in your environment.

Direct to Database testing conveys all the benefits that CloudTest users are accustomed to; including visual test creation, scale testing from the cloud, and real-time analytics.

For example, Direct to Database testers can execute SQL workloads for performance testing, insert data into a database prior to a test, or clean up test data after a test has completed. CloudTest can return Results in various formats including CSV, XML, and JSON.

Database Support

The necessary JDBC drivers are included with CloudTest.

For the first release, CloudTest supports Oracle, Microsoft SQL Server, and PostgreSQL databases. The full list of databases and versions includes:

  • Oracle (versions: 11.2.0, 11.1.0, 10.2.0, 10.1.0, 9.2.0, 9.0.1)
  • Microsoft SQL Server (versions: SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005, SQL Azure)
  • PostgreSQL (versions: 7.2 or newer)

Users will directly access databases installed in their environments including the ability to connect to, query to, and to time the performance of a customer installed database.

Users can build entire SQL workloads that represent peak database traffic, much like a composition for a load test contains clips that are common workloads of HTTP traffic. For example, a clip that has the SQL traffic for something like an order placement built in. Such a clip will include numerous SQL statements one query per database action (e.g. clip element).

At the composition-level, the test composition may have many database clips that ultimately create the 'peak database traffic profile' for an application. This includes all types of SQL activity within the given database. An order placement usually has SELECTs, UPDATEs, INSERTs, and such for that single operation spread out over a session.

  • Typically the SQL statements are provided to a tester or retrieved through the application itself, not through the test tool. These are usually copied and pasted in to a test from an external source.
  • User's database knowledge: basic to medium level knowledge of databases and the Structured Query Language (SQL) is presumed.

Database Target Type

To use Direct to Database Testing in a test composition, you must first have a Database target. During target definition, you will need to provide the following required parameters:

Note: The Database target type was introduced with the SOASTA 47 release (Build 6656.7). As noted above, this feature requires enablement by your SOASTA representative.

Database URL — The JDBC database URL for connecting to the database

User Name — The Database user name to connect

Password — The Database password to connect

The following optional parameters may also be provided within your target definition:

Using SSL — Check this box during target creation (or while editing an existing target) if Secure Sockets Layer (SSL) should be used to connect

Connection Timeout — Specify a database connection timeout in seconds (minimum 1, maximum 65535, default 15)

Execution Timeout — Specify a SQL statement execution timeout in seconds (minimum 1, maximum 65535, default 60)

Creating a Database Target

Use the Target Definition Wizard (via Central > Targets, New) to create a Database target providing all the necessary parameters.

  1. In Central > Targets, click the New icon, to manually create a new Database target n the Target Definition Wizard.

  1. Select Database and then click Next.

The Define the Database Target page appears in the wizard.

  1. Enter the target name and select the RDBMS to use from the drop-down. For example, to connect to a PostgreSQL database on the same server running CloudTest, select PostgreSQL.
  2. Provide a host name and port.
  3. Provide a catalog name (optional).
  4. Enter the database user name and password credentials to use.

The Target Definition Wizard will construct your JDBC URL based on the information provided here.

Targets can also be defined and edited within the Target Editor. Consider the following information in making any changes or during troubleshooting database connections.

A PostgreSQL database requires the following Database URL format:

jdbc:postgresql://<database_server_host>:<port_number>/<database_name>

Refer to the PostgreSQL online documentation for more details.

A Microsoft SQL Server database requires the following Database URL format:

jdbc:sqlserver://[databaseServerHost[\databaseInstanceName][:portNumber]][;<property>=value[;<property=value>]] <property=value>]]

The database name is specified by the property "databaseName". Refer to the Microsoft online documentation for more details.

An Oracle database requires the following Database URL format:

jdbc:oracle:thin:@:<port_number>:<database_name>

Refer to the Oracle database documentation for more information.

Note: The database user name and password can be embedded in the database URL for some databases such as Oracle and Microsoft SQL Server. But, they are overridden by the user name and password entered on the Target Editor. So, users should not put the user name and password in the database URL.

The Target Definition Wizard, Advanced tab presents the same information only in the style that it is also presented in the Target Editor. Use this to inspect the JDBC URL in cases where it exceeds the display in the Basic tab.

  1. Click Next to view the Summary page.

  1. Double check the database URL, ensuring that the URL form is correct. Click Finish to complete creation of the database target. The target will open in the Target Editor.

Once completed, you can click Test Connection in the Target Editor to ensure the target definition has been satisfactorily completed. If the connection is unsuccessful, check the JDBC URL, ensure that the database is running, try increasing the Connection Timeout and/or the Execution Timeout for the new target. and then refer to the Troubleshooting section below.

Creating a Clip for Database Actions

The Direct to Database feature uses the clip element type, database action. A user can create a clip containing one or more database actions for one or more database targets. All clip timing types, including Sequenced, Manually Timed, and Evenly Timed, are supported for use with database actions. Users should specify only one SQL statement (or SQL command) per database action. If multiple SQL statements are bundled for one database action, only one Result for the last SQL statement will be returned and displayed within CloudTest's Result Details widget.

Including Database Target(s) in the Clip Editor Lower Panel

As with any target, your Database target can be manually added to a test clip via the Clip Editor, Messages/Actions tab, Available Targets list.

  1. Create a new or open an existing test clip and then maximize the Messages/Actions tab.

  1. Locate the target by name in the Available Targets list and then click the green arrow move it into the Included Targets list just as you would with any target.

The Database action appears in the Actions list as the only action type for this target type.

  1. Double-click the action to add it to the Clip Editor workspace. If other clip elements are present, first select the one prior to where you'd like the Database action to be added. You can always drag it into place if it is not at first added where it will be used.

  1. Repeat for any additional database actions (one executed query per database action) and targets that this clip will comprise.

Defining a Database Action

Once you've added one or more database actions to a test clip, use the following steps to define a given action.

  1. Double click the action to open its properties in the lower panel. When you do so, the General tab appears.

For each action that you add, you will specify one and only one SQL statement (or SQL command).

    1. Define the following Database action parameters in the General tab:
      • Output — Select the output option (from the drop-down list) for the Result of SQL statement execution (from among the following output options):
        • execute-only – The Query will be executed but no rows will be retrieved from the database.
        • summary – The Query will be executed but no rows will be retrieved from the database. Two examples:

          The SQL statement was executed successfully. (0) 
          The SQL statement was executed successfully. (1 row affected)
      • read-and-discard – The Query will be executed and all rows will be retrieved from the database but then discarded. Applies to SELECT FROM statements only.
      • csv – Retrieved rows will be formatted as CSV. Applies to SELECT FROM statements only.
      • csv-with-header – Retrieved rows will be formatted as CSV with header line. Applies to SELECT FROM statements only. The header line contains the column names specified in the SELECT FROM statement.
      • xml – Retrieved rows will be formatted as XML. Applies to SELECT FROM statements only. The root node of the XML document is Output, which contains the Results element that contains one or more Resultelements for multiple results. Each result contains Summary and Data elements.
      • xml-with-summary – Retrieved rows will be formatted as XML with summary element. Applies to SELECT FROM statements only. The root node of the XML document is Output, which contain the Results element that contains one or more Resultelements for multiple results. Each result contains one Data element.
      • json – Retrieved rows will be formatted as JSON. Applies to SELECT FROM statements only. The top level tag is output. The tag at the next level is results which contains one or more results represented by a JSON array. Each result is represented by the summary and data tags.
      • json-with-summary – Retrieved rows will be formatted as JSON with summary. Applies to SELECT FROM statements only. The top level tag is output. The tag at the next level is results which contains one or more results represented by a JSON array. Each result is represented by one data tag. The summary element contains some information about the columns specified in the SELECT FROM statement, including column name, column (data) type, and approximate column size.
    • Max Row Count — The maximum number of rows that a query (or SQL SELECT statement) can return to the given number. If the limit is exceeded, the excess rows are silently dropped. Zero (which is the default) means there is no limit.
    • Max Bytes to Retrieve Per Column — The maximum number of bytes per column that can be returned for character and BINARY column values by a query (or SQL SELECT statement). This limit applies only to BINARY, VARBINARY, LONGVARBINARY, CHAR, VARCHAR, NCHAR, NVARCHAR, LONGNVARCHARandLONGVARCHAR fields. If the limit is exceeded, the excess data is silently discarded. Zero (which is the default) means there is no limit.
    • Execution Timeout (optional) — SQL statement execution timeout, in seconds, minimum 1, and maximum 65535. If specified, the value overrides the Execution Timeout set at the Target level.
    • Errors Should (optional) — As with other types of messages and actions, the default failure action is to fail the parent. Select another option, if necessary.

Database Column Data Type Support

CloudTest supports most database column data types supported by the JDBC drivers, except for the following advanced types:

  • Array
  • Distinct
  • Other
  • Struct
  • UDT (User Defined Types) (this pertains to Microsoft SQL Server only)
  • Variant - Note that CloudTest relies on the JDBC driver of SQL Server for Direct to Database Testing. The user has to avoid the “variant” data type when using the Direct to Database Testing feature on CloudTest. 

A given JDBC driver's unsupported data types will not return the column value on query (e.g., SQL SELECT statement). Instead, a text in the form of <ColumnTypeName>(<ColumnIndex>):<ColumnObjectName> will be returned and displayed on the UI. For example, the Oracle JDBC driver doesn't support the data type UROWID (Refer to Valid SQL-JDBC Data Type Mappings).

Using the SQL Editor

Once you have defined the given Database Action via the action's properties, General tab, use the following steps to add your SQL statement.

  1. With the lower panel properties for the action still open, select the SQL Editor tab.

  1. From this point enter your SQL database query just as you would on the command line or any SQL editor. For example, if you are inserting data at runtime for a given test your workflow may be something like CREATE TABLE, INSERT INTO, SELECT * FROM.

In such a scenario, you will use the first database action to create the table. This is just the first action in the scenario.

Define the Output type per action just as described above until you arrive at the necessary workflow (shown below in Clip Editor, List view).

The Output type selected for each action will determine what you see in test Results.

Database Items in Result Details

Database Actions appear in the Result Details dashboard just as they do for any clip element.

In the Result Details widget, the General and SQL Command sections, as well as the Result section, all will indicate the status of a Database Action. For a given selection database action, such as our Create Table action from above, the General section of the dashboard provides the duration, response time, message bytes, and message throughput common to all messages and actions.

To examine the specific events of your executed query, select the Events List tab with a given database action selected.

By inspecting the events, you can see all the same responses you would expect from your database if SQL queries were executed by some other tool or the command line.

Firewall Settings of Database Networks

In order to allow CloudTest to access to a user's database, the public IP address(es) of CloudTest instance(s) needs to be added to the firewall settings of the network on which the database instance is running.

This pertains to the public IP address(es) of the CloudTest instance(s). The command nslookup may show an internal IP address. There are ways to find the public IP address of a server. If a browser can be launched from the server, opening a page of www.whatismyip.org can get the public IP address. If no browser is available, some commands (i.e., curl www.whatismyip.com can help find the public IP address by decoding the ASCII values in the returned HTML source.

Override Database Settings

You can override database settings at runtime from within the Composition Editor.

Right-click and select Override Database Settings... on the context menu. This command is shown whenever a database clip is selected. When you do so the Override Database Target Settings dialog box appears.

Provide alternate database connection parameters (i.e. Database URL, User Name, Password, Use SSL checkbox) and then OK.

Database Action XML and JSON Format Outputs

Direct to Database testers should note the following schemas for XML and JSON formats.

TIP: For an introduction to database testing, see Direct to Database Testing.

In the case of XML, a Result tag exists underneath the existing root Results tag, as well as a new Output tag as the root.

For JSON, the top level tag is "output" for an JSON object represented by the second level tag "results", which is in turn a JSON array for multiple results. Each result is a JSON object consisting of the "summary" and "data" objects or just the "data" object.

TIP: Support for multiple results using the CSV output option is not available at this time. This is due to the limitations of the CSV format, Since we can't know this ahead of time, there will be a runtime error if a given Database Action ends up returning multiple result sets and the output format requested was CSV.

Summary of XML and JSON Output Formats

For examples of output formats, see Database Query Results in Result Details.

xml

Retrieved rows will be formatted as XML. Applies to "SELECT FROM" statements only. The root node of the XML document is Output, which contains the Results element that contains one or more Result elements for multiple results. Each result contains one Data  element.

 xml-with-summary

Retrieved rows will be formatted as XML with summary element. Applies to "SELECT FROM" statements only. The root node of the XML document is Output, which contains the Results element that contains one or more Result elements for multiple results. Each result contains the Summary and Data elements.
The summary element contains some information about the columns specified in the "SELECT FROM" statement, including column name, column (data) type, and approximate column size.

 json

Retrieved rows will be formatted as JSON. Applies to "SELECT FROM" statements only. The top level tag is output. The tag at the next level is results, which for a JSON object contains one or more results represented by a JSON array. Each result contains one JSON object with the data tag.

 json-with-summary

Retrieved rows will be formatted as JSON with summary. Applies to "SELECT FROM" statements only. The top level tag is output. The tag at the next level is results which for a JSON object contains one or more results represented by a JSON array. Each result contains two JSON objects with the summary and data
The summary value contains some information about the columns specified in the "SELECT FROM" statement, including column name, column (data) type, and approximate column size.

 

Attachments

    Outcomes