This topic explains how to configure and apply the DB tool in SOAtest and Virtualize. This tool allows you to query databases for validating SQL statements with SOAtest.

Sections include:

Understanding the DB Tool

This tool sends an inquiry to the database you specify, and the results from the query will be returned. SOAtest and/or Virtualize formats the results into XML; this allows XML based tools (XML Data Bank, XSLT Tool, etc.) to be chained to the output.

The DB tool can be used to call stored procedures (e.g., using PL./SQL) as described in Support for PL/SQL and Stored Procedures.

A Traffic Viewer is automatically attached to each DB tool. It lets you see the end result of the query that was sent to the server (taking into consideration parameterizations, variables, and so forth). In the response, JDBC-related objects are shown. 

Configuring the DB Tool

The following options can be configured in the DB tool:

General

Connection Tab

There are several options for connecting the tool to the database. By default, you can configure a local connection using a file or by specifying the database driver settings. 

Enable the File option and browse for the connection configuration file in your file system or workspace.

Enable the Local option and specify the DriverURLUsername, and Password of the database to be queried. For more information on configuring database connection settings, see  Database Configuration Parameters in SOAtest or Database Configuration Parameters in Virtualize.

You can also enable the Close connection option to close the connection if you are querying the database once and do not need to wait for another command. Do not enable this option when you plan to connect multiple DB tools to the same DB so that all tools can share a connection. Sharing the connection improves resource efficiency, as opposed to using resources on a new connection for each DB tool.

If your test suite has a Database Account shared property, you can connect  Use Local Settings,Use Shared Property, or Use Both. These options enable you to use only settings within the corresponding DB tool, settings from a shared global database account (global database settings that you added to the test suite as a Global Property), or both. For more information on how to define shared properties and export them to a file, see Global Database Account Properties in SOAtest or Adding Global Properties in Virtualize.

The Traffic tool shows a connection ID. Checking this ID  will help you ensure that your DB tools are using the same connection.

SQL Query Tab

Statement Separation Behavior

With Separate statements by semicolon (;), the tool will divide the contents of the SQL query editor by semicolon. With Separate statements by Magic Token (typically used for stored procedures), the tool will divide the contents of the SQL query editor by the provided magic token string. A magic token is a string that starts at the beginning of a line and contains only the magic token (with optional white space after the token).

The separators you have selected (either semicolons or magic tokens) are not sent to the server. The DB tool will send each separated sub-string to the server as a separate JDBC call with the same connection. This allows a single DB tool to use multiple queries. Depending on the SQL server JDBC driver, it may be possible for multiple queries to be sent in the same JDBC call:

If multiple statements need to be used by a single DB tool, they must be separated by a magic token (this is due to the restrictions in DB3 and Oracle). For example, if you choose to separate by a magic token of "GO" (the default) and you enter the following query string, two JDBC calls will be sent—each containing two SQL statements:

create table pr88467(dkey int, svalue varchar(30));
insert into pr88467 (dkey,svalue) values (1,"A"),(2,"B"),(3,"C"); 
GO
select * from pr88467;
drop table pr88467;

When creating or calling stored procedures, separation by magic token must be used and the stored procedure must be the only statement in its group. When creating procedures, this is required because procedures will usually contain required nested semicolons that would be removed if using the "separate by semicolon" option. When calling procedures, they must be separated because the DB tool needs to be able to detect them and send them as a CallableStatement to the JDBC driver (with the optional out parameters). For example:

create or replace function pr83185_func2(s NUMBER) return SYS_REFCURSOR as mycursor SYS_REFCURSOR;
begin
open mycursor for select * from EMPLOYEES where SALARY > s;
return mycursor;
end;
GO
? = call pr83185_func2(15000)

The above will issue two JDBC calls:

Options Tab

Single output format:

<updatedRows>0</updatedRows>

Multi-output format:

<results>
	<updatedRows>0</updatedRows>
</results> 

With multiple results: 

<results>
	<updatedRows>0</updatedRows>
	<updatedRows>0</updatedRows>
	<updatedRows>0</updatedRows>
</results>

JDBC OUT Parameter Types for Stored Procedures

The JDBC OUT parameter types for stored procedures field allows you to invoke stored procedures and functions that take OUT parameters or return a value. You can leave this field empty if you are executing a regular SQL query or a stored procedures that does not have any OUT parameters.

The types you list in that field can be separated with spaces or commas, and the type names correspond to the field names in the class java.sql.Types (http://docs.oracle.com/javase/8/docs/api/java/sql/Types.html) or oracle.jdbc.OracleTypes class fields (http://download.oracle.com/docs/cd/A97329_03/web.902/q20224/oracle/jdbc/OracleTypes.html). will look for the type you provide in java.sql.Types first. If it is not found there, it will look for it in oracle.jdbc.OracleTypes. The type name you provide is passed to the JDBC CallableStatement.registerOutParameter().

Examples

Oracle Type Extensions

Oracle databases support various proprietary data types. If your database utilizes these data types, you must add the appropriate jar(s) to  classpath in the Preferences panel (under Parasoft> JDBC Drivers).

For example, Oracle supports oracle.xdb.XMLType. This is available with xdb.jar and xmlparserv2.jar, which are shipped with specific Oracle applications. It is also available from the XML Developer's Kit (XDK) Java, which is available at http://www.oracle.com (click Downloads, click the link for the XML Developer Kit, then select the kit for any platform). Note that this kit contains other files, but you need only the 2 listed above. 

Both of these jars should be added to the JDBC classpath in the Preferences panel (under Parasoft> JDBC Drivers).

Support for PL/SQL and Stored Procedures

Since stored procedures can drive the business logic of today’s systems, they need validation—just like any other component in the system. Procedures (also known as functions, procedures, packages, and triggers) to an Oracle database can also be stored. It can also call those procedures over and over. This is especially useful for complex queries that need to be executed frequently. 

PL/SQL (Procedural Language/Structured Query Language), Oracle’s proprietary procedural extension to the SQL database language, is used in the Oracle database. Other SQL database management systems offer similar extensions to the SQL language.

The following image shows an example of how to configure a DB tool query (in the SQL Query tab) to create or replace a stored procedure:



The following image shows an example of how to configure a DB tool query to call a stored procedure:


Viewing Query Results in the Traffic Viewer

When a Traffic Viewer tool  is attached to a DB tool and a "SELECT" statement is executed by the DB tool, the Traffic Viewer tool’s Response area will show a table that contains the results of the DB query.

When a DB  tool executes multiple SQL statements and the XML response includes multiple result sets, you can use the combo box (above the table when there are multiple result sets) to determine which result set’s traffic is shown.

Here, the traffic viewer is showing the results for result set 2: