The following Direct to Database sample scenario details a Database Commit and Rollback using CloudTest. This use case presumes that the showcases an example of how to use If-Then-Else to check any database issues and then commit or rollback the database actions if any of them fail. In the eventuality of failure, CloudTest will not simply exit the clip but will execute the rollback. This scenario includes an intentional error that demonstrates the rollback potential of the feature.

Use Case: Database Commits/Rollbacks

In this section, we will detail a sample scenario in which a Database Commit and Rollback is performed. This use case showcases an example of how to use If-Then-Else to check any database issues and then commit or rollback the database actions if any of them fail. In the eventuality of failure, CloudTest will not simply exit the clip but will execute the rollback. This scenario includes an intentional error that demonstrates the rollback potential of the feature.

• Before you begin, create your database target (use the steps in Creating a Database Target above).
• Create a new test clip, include your database target, and add the first database action to the new clip (all using the steps in Creating a Clip for Database Actions above).

Steps for Commit and Rollback using If-Then-Else

1. First, add and setup a database action for BEGIN TRANSACTION.

• Double-click the database action to open it in the Clip Editor lower panel, General tab.
• Rename the database action: Database Action - begin transaction and change its Output type to Summary.

• Click the SQL Editor tab and enter BEGIN on line 1.
• Save the test clip and give it a name.

2. Next, we will create a group, add three database actions to it, one each for creating a table, insert into, and the last to select all data.

• On the Clip Editor toolbar, click the Add Menu drop-down and select Add a Group. Accept the default group name, Group1.
• Next, add three new database actions to Group1 and then rename and edit these actions with the following properties.

1. Database Action – create table, output type Summary, and a create table SQL statement (added to the SQL Editor tab)

  Database Action 1 - create table Example:

CREATE TABLE __database_testing__
(column_int smallint, column_varchar character varying(255), column_numeric numeric(12,3), column_double float8, column_boolean bool, column_binary bytea, column_date date, column_time time, column_timestamp timestamp);


2. Database Action – insert four rows of data, output type Summary, and an insert into statement (added to the SQL Editor tab).

Database Action 2 - insert into Example:

Note:  In the following example, the table name, __database_testing__***, is intentionally set incorrectly in order to make the completion type of this group "failed".

INSERT INTO __database_testing__*** (column_int, column_varchar, column_numeric, column_double, column_boolean, column_binary, column_date, column_time, column_timestamp)
VALUES (1, 'Oracle 11g', 123456.78, 123456.78, TRUE, '0x0102030405060708', '2013-06-25', '01:02:03', '2013-06-25 01:01:01 PDT');
INSERT INTO __database_testing__ (column_int, column_varchar, column_numeric, column_double, column_boolean, column_binary, column_date, column_time, column_timestamp)
VALUES (2, 'PostgreSQL "9.3"', 234567.89, 234567.89, TRUE, '0x0203040506070809', '2013-06-25', '02:02:02', '2013-06-25 02:02:02 EDT');
INSERT INTO __database_testing__ (column_int, column_varchar, column_numeric, column_double, column_boolean, column_binary, column_date, column_time, column_timestamp)
VALUES (3, 'SQL Server 2012', 345678.90, 345678.90, FALSE, '0x0304050607080910', '2013-06-25', '03:03:03', '2013-06-25 03:03:03 UTC');
INSERT INTO __database_testing__ (column_int, column_varchar, column_numeric, column_double, column_boolean, column_binary, column_date, column_time, column_timestamp)
VALUES (4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);


3. Database Action - select all data, output type Summary, and a select all statement (added to the SQL Editor tab)

Database Action 3 - Select From Example:

Note: Because of the error in the previous database action, this query is not expected to be executed.

SELECT * FROM __database_testing__; 


3. For each database action in Group1, expand the action and then hover over the action title to show the Add menu for the given action. Then, click the green checkmark to add a new validation for that action. Once the validation form appears, add the following text to the Exact Match field: The SQL statement was executed successfully. (0)

4. Next, we will create an If-Then-Else container, add an embedded script to the IF section, a new database action (for rollback) in the THEN section, and a group, and the last to select all data.
   • On the Clip Editor toolbar, click the Add Menu drop-down and select Add an If-Then-Else. Accept the default group name, IfThenElse1.
   • Expand and then revise IfThenElse1 to the following:
     • Change IF "Property" to IF "Embedded Script"
     • After Embedded Script, enter:

$context.currentItem.previousItem.completionType

• Change "Constant" to "Text"
• Enter "Failed" (in the field after Text)

The resulting statement is shown below.

5. Add a database action to the Then section, rename it to Database Action – rollback, and then in the expanded SQL field (in list view).
   • Change the output to Summary and in the SQL Editor tab enter 1 line: ROLLBACK. This ROLLBACK is expected to be executed due to the intentionally incorrect table name in the previous database action (or SQL statement).

Note: You can enter SQL directly into the expanded action in List view; however, in this example we also need to change the output type, which requires opening the lower panel.

6. Add a second Group to the ELSE section (and rename it Group 2 - commit, query, and drop). This group is not expected to execute.
7. Add three new database actions to Group2 (in the ELSE section of IfThenElse1) and then rename and edit them with the following properties

1. Database Action – commit, output type Summary, and a commit table SQL statement (added to the SQL Editor tab)

2. Database Action – query, output type CSV with header, and a select statement (added to the SQL Editor tab)

Select From Example:

SELECT * FROM __database_testing__

3. Database Action – drop table, output type Summary, and a drop table statement (added to the SQL Editor tab)

Drop Table Example:

DROP TABLE __database_testing__;

The final form of the ELSE, Group2 section should look like this:

8. As the final clip building step, add a database action after IfThenElse1, and then rename it to Database Action – select. Open this action in the lower panel and change the output type to XML with summary, and in the SQL Editor, add the following: SELECT * FROM __database_testing__.

Note: This database action is expected to fail because the table does not exist when it is executed.

Add the clip to a new composition using Open in Test Composition. Make any composition-level changes such as server location assignment that are unique to your environment.

9. Once ready, play the composition.

Note: For more information about what database query responses that will result in playing the test composition, see Database Query Results in Result Details.