Adding an SQL Command action to a test

The SQL Command action runs an SQL command (insert, update, or delete rows) against a selected database. As you add this action, you can validate or store into a tag the number of rows that are affected.

Before you begin

Ensure that the database resource is properly configured in the Architecture School perspective. For more information about configuring a database resource, see Database resources, connections, and bindings.

You must have a test. For more information about creating tests, see Test Factory. Tests include steps and actions. For more information about test steps and actions, see Test actions.

About this task

Selecting a test

Procedure

  1. In Rational Integration Tester’s Test Factory perspective, double-click the test where the SQL Command action is to be added.
  2. In the Test Editor window, click General > SQL Command.
  3. To edit the action, double-click SQL Command action.

    The SQL Command dialog box opens where you can add the SQL statements.

Adding SQL statements

About this task

To insert, update, or delete rows against the selected database, add SQL statements.

Procedure

  1. Select a database resource from the available resources in the project by clicking Browse.

    The initial list of databases is derived from the information that is provided in the Architecture School perspective. If the operation that you are creating a test for has a dependency on a database, then that database shows in the list when Obey References is set to Yes. If you want to work with a database where there is no dependency link to the current operation, all databases in the project can be viewed by setting Obey Referencesto No.

  2. Enter a statement (INSERT, UPDATE, or DELETE) in the Statement field. You can add multiple SQL statements and separate them by a semi-colon (;).
    SQL Command Action dialog box
  3. If you are entering a stored procedure, click the Is stored procedure? check box and modify the syntax of the query, for example, {call procedure (var, var)}.
    Tip: If you are using a stored procedure call, you might want to use the Stored Procedure action instead (see Adding a Stored Procedure action to a test). This action presents the available functions and input and output parameters more clearly.
  4. To commit the changes to the database after each statement runs, click the Auto-commit? check box. If you clear this option, the changes are saved to the database only after all the statements are successfully run.
  5. Save the SQL Command action by clicking OK.

Testing the SQL statements

About this task

Before you run the SQL statements against a selected database, it might be a good idea to test the SQL statements.

Procedure

To test the statements without running the test, click Execute Now. The SQL Command Test dialog box shows the results of the statements (that is, success or failure, and the number of rows that were modified by the SQL (INSERT, UPDATE, or DELETE) command.
SQL Command Test

Validating return values

About this task

Important: When the action is run within a test, it returns the number of rows that are modified by the SQL command. Records are updated, inserted, or deleted in the database. The return value can then be validated and stored.

To specify a condition that relates to the return value, such as validating the number of rows that are affected by the command, use the Validate tab under the Actions tab.

Procedure

  1. Click the Actions tab.
    Actions tab in the SQL Command Action dialog box

    In the example above, the return value is validated by the condition that it equals zero. Hence, this test step fails if any rows are updated by the specified command.

  2. Click the Validate tab and choose an option. For more information about each option, see The Field Editor.

Storing the return value in to a tag

About this task

To store the number of database rows that are affected into a tag for later use, use the Store tab under the Actions tab. For example, you might want to insert a record into a database, by using tags instead of hardcoding the values to be inserted into the database. With tags, you gain flexibility to provide different values in the test and to reuse the test.

Procedure

  1. Click the Actions tab.

    Store tab

  2. Click the Store tab and choose an option. For more information about each option, see The Field Editor.
Feedback