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.
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.
How to
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);
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);
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__;
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)
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.
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.
Add a second Group to the ELSE section (and rename it Group 2 - commit, query, and drop). This group is not expected to execute.
Add three new database actions to Group2 (in the ELSE section of IfThenElse1) and then rename and edit them with the following properties
Database Action – commit
, output type Summary, and a commit table SQL statement (added to the SQL Editor tab)
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__
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:
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.
Once ready, play the composition.
What you should see
Note: For more information about what database query responses that will result in playing the test composition, see