Author: AutomatedQA
Applies to: TestComplete 7

Intro

Databases often contain mission-critical data that is processed by specific applications. Also, databases include methods (stored procedures, functions, triggers) that implement important functionality. In order to ensure the quality of these assets, as well as the integrity and consistency of the stored data, you should have a comprehensive automated testing tool that you can run regularly.

TestComplete offers you a number of specific techniques that you can use to test databases. This article describes the most common features for automating database testing with TestComplete.

The Sample Database

We will illustrate the described tasks using the sample OrdersDB.mdb database. This database is installed automatically with other TestComplete script samples and can be found in the <TestComplete Samples>\Open Apps\OrdersDemo\Web folder. The folder also contains the Web Orders application that we will also use during testing. To run the application, open the start.htm page in your web browser.

Database Testing Tasks

Typically, database testing consists of the following tasks:

• Database Initialization - Put your database into a known state before running tests to make sure that the tests will be executed correctly.
  
  This can be done in two ways. The most common practice is to rebuild the database for every test run. An even simpler way is to reinitialize the source data by erasing all existing data and inserting the initial data values.
• GUI Testing - Check that the application operates with the database correctly.
  
  You can use TestComplete’s features to test a database application the same way you would test any other application. You can record or manually create a sequence of test actions that are intended to detect errors and then replay the database test. All types of testing are supported by TestComplete and can be used for testing database applications. Choosing the test type depends on what you actually need to test.
• Data Verification - Check the structure and the actual content of a database.
  
  Data verification is useful, for instance, after converting a database from one type to another. You need to check the number of columns, records and their values, to make sure that no data was lost during the conversion. Another example is testing database consistency after your database application performs actions over the database. For example, you may check whether all fields were populated after the application added a new record to the database. You can also compare a copy of the database data with the actual data to find out which fields were changed or which records were added or removed.

Database Testing – TestComplete’s Tools

TestComplete has a number of specific program objects that you can use to establish a connection with a database, obtain database tables, execute queries, and perform data verification and other testing actions. TestComplete also provides database table checkpoints that compare values stored in a database with a baseline copy that is stored in TestComplete’s project.

Checkpoints are effective for verifying a set of values stored in a database. They provide you with a quick and simple way to perform a comparison of database tables. But they don’t give you any access to database records. While testing databases you may need to create custom comparisons that will perform very specific (and perhaps rather complex) testing actions.

To do this, write script code that interacts with the database using specific program objects. In order to interact directly with database components, use the ADO and BDE program objects. These objects implement the same methods and properties as data-aware VCL objects for ADO and BDE.

The Microsoft Access database (.mdb), that we use as an example in this article, works with the ADO program object. If you want to work with Borland databases, use the BDE program object. For more information on how to interact with databases via BDE, see Borland Delphi’s documentation.

Automation of Database Initialization and Populating Database Fields

Sometimes, the current state of a database application or actual database content may influence the behavior of your database tests. That is why, in order for database tests to execute correctly, the tested application and the database must be in the same state as they were before the first test run.

The initial state of a database application is based on the actual data of a corresponding database. That is, to make your tests more stable you need to make sure that the database contains certain content before the database test runs. Typically, this problem can be resolved by restoring a backup copy of the database.

In some cases, restoring a database from a backup copy may be unacceptable. You can work around this problem by initializing a database and erasing the current database content and repopulating the initial data. However, manually populating a database with data is out of the question, especially if the database contains a large amount of data.

To let you automate database populating, TestComplete provides a set of specific program objects that provide scripting access to certain database tables and records. The implementation of these objects coincides with those of data-aware VCL objects for ADO and Microsoft ADO objects. By using the provided program objects (IAQAADOConnection, IAQAADOCommand and others) in your script code, you can create rather complex tests for testing your database.

Actually, you can populate a database automatically using the data-driven testing technique (DDT). DDT allows you to iterate through rows of an external data storage sequentially. That is, you can store the initial database data in an external data source (Excel datasheet, for instance) and then repopulate the database with this data. We will use an Excel datasheet as an external data storage that will contain the initial content of the "products" table. Create an Excel 2007 datasheet TestSheet that will contain the following fields:

The following sample script connects to the OrdersDB.mdb database, erases all content of its "products" table and then automatically populates the table with data using the DDT driver object.

The above script demonstrates how to prepare the database for testing. It erases all of the content of the products table in our sample Web Orders application and populates it with the initial data used for our test. This guarantees that the test run will not fail due to data discrepancies. Note that by using the data-driven testing technology, you can avoid manually populating multiple data fields. For a step-by-step description of how to create a data-driven test with TestComplete, please read the "Introduction to Data-Driven Testing With TestComplete" technical paper.

Testing Application’s GUI and Automatic Data Verification

Now, we will create a simple database test that will operate with a sample web application and verify whether the database was changed. We will create a database table checkpoint that will store the data of the ‘orders’ table in the OrdersDB.mdb database and then manually modify the database table content using the Orders application’s Edit form. The test script will compare the stored baseline copy with the actual content of the database table and display the results in the test log.

Below are the instructions for creating the database test.

• First, create a new TestComplete project:
  • Select File | New | New Project from the TestComplete’s main menu. The Create New Project dialog is displayed.
  • In this dialog specify the project name, the scripting language that will be used by the project and the project location. For example:

    
    
    Creating New Project

  • Click Create.
• Switch to the Test Items page of the project editor and press the New Test Item button. This will automatically add the Main routine to the list of test items to be run:

  
  
  Test Items

• Create a new database table item that will store the baseline copy of the database data:
  • Right-click the DBTables project item in the Project Explorer panel and choose Add | New Item from the context menu. In the ensuing Select Database Table dialog select Create a new item in stores and then click Next.
  • Specify the name of the DBTable element to be created and the connection string that will be used for the connection.

    
    
    Creating DataBase Table

  • Make sure that the Table option button is selected and press Next.
  • In the Object box select "orders . This is the table whose content we will verify. Press Next.
  • We will store all data of the given database table, so you don’t need to uncheck any fields. Select the check box in the Key column next to the id field to mark it as the key column. This will influence the comparison. Press Next to review the stored data.
  • Press Finish.
• Your project now contains the OrdersDatabase DBTable item. Double-clicking it in the Project Explorer opens the DBTable Editor:

  
  
  DB Stores

  The DBTable Editor displays the data that will be used for the comparison. Check boxes that are displayed next to the values specify whether these values are included in comparison or not. We will compare all obtained datasets, but keep in mind that if necessary you can uncheck any value to exclude it from the comparison.
• Now switch to the Script Code Editor by double-clicking the script unit (Unit1) in the Project Explorer panel.
• Select Create Database Table Checkpoint from the Code Editor’s toolbar. In the ensuing dialog select OrdersDatabase in the list of available stores items. Then, press Next.

  
  
  Create Checkpoint

• The ensuing dialog contains the generated checkpoint code that is created automatically. Close the dialog by clicking the Copy button and then paste the obtained text into the Main script routine.

  
  
  Checkpoint Text

• To simulate working with the database and to perform the actions described below, you must turn off the Internet Explorer Information bar anytime it appears.
  • Launch your web browser and open the Start.htm page in it. This will launch the Web Orders application.

    
    
    WebApplication

  • In the Edit column of the first row of the table click the Edit hyperlink. This will show the Edit window for this row.

    
    
    Edit Window

  • In the Product combo box select MyMoney. In the State box type iGreat Britain instead of US. Then, press Update.
  • Close the Web orders application.
• Now, right-click the project in the Project Explorer and choose Run DatabaseTesting [Project] from the ensuing context menu. The log of a project run will look like this:

  
  
  Results

  As you can see, all of the changes that were made in the database are now displayed in the test log. Note that the "dt" field that stores the date of the last data modification was automatically changed by the application when you pressed Update.

Database table checkpoints provide you with a quick and easy way to perform data verification. You can store database tables in the DBTable project item and then compare certain fields of the stored table. Instead of storing the entire table, you can create a custom query that will extract only the data that you specified, and then use the obtained dataset for comparison.

Conclusion

In this article we describe the approaches that you can use to automate database testing with TestComplete. TestComplete offers a number of automated testing techniques that you can use to perform easier and faster database testing, while creating robust and flexible automated tests.

Specific program objects provided by TestComplete provide you with access to certain database tables and records, that is, you can process their data. TestComplete’s data-driven testing technology lets you automate the process of populating database fields and allows you to perform quick database re-initialization any time you need it and database table checkpoints provide you with an easy way to verify stored data. If you are interested in trying database testing or just want to see how TestComplete works, download and try TestComplete today.