Applies to: TestComplete 8.10

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.

This article contains the following sections:

• Database Testing Tasks
• Database Testing – TestComplete’s Tools Overview
• Automation of Database Initialization and Populating Database Fields
• Creating a Simple Database Test
• Conclusion

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.
• Functional Testing - Checks 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.

We will illustrate database testing using an Excel file (to perform the database initialization) and the sample OrdersDB.mdb database. This database is installed automatically with other TestComplete script samples and can be found in the \Checkpoints\Database Checkpoints\DataGridViewSample\bin\Debug folder. Or, as an alternative, you can download this database directly from this page by clicking the Sample Database link in the menu above.

Database Testing – TestComplete’s Tools Overview

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 a keyword-driven test 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.

You can also connect to the desired databases by using the ADO DB functionality included in Microsoft Windows. You can instantiate connections, record sets and commands as COM objects and then use their methods and properties in scripts.

Alternatively, you can use the DB Table variables to read data from the desired storage. These variables support reading data from any of the following data storages:

• Database tables or queries
• Excel files
• CSV (comma-separated values) files

The DB Table variables are only available in TestComplete 8 and provide you with read-only access to the desired database records. Each DB Table store links to database tables and queries and provide serial access to their records. DB Table variables are usually used by the Data-Driven Loop operation that allows you to iterate through the table records in keyword-driven tests. For more information on working with these variables, see Introduction to Data-Driven Testing in TestComplete.

For more information on the database testing with TestComplete, see Working With Databases in TestComplete on-line help.

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.

Sub TestProc
 Dim AConnection, Driver 

 ' Create a Connection object
 Set AConnection = ADO.CreateADOConnection

 ' Specify the connection string 

  AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+ "Data Source=../../OrdersDB.mdb" 

 ' Suppress the login dialog box 

  AConnection.LoginPrompt = False

  AConnection.Open 

 ' Delete all the content from the "products" table

  AConnection.Execute_("DELETE * FROM products") 

 ' Populate the "products" table
 Set Driver = DDT.ExcelDriver("../../ProductsDatasheet.xlsx","TestSheet",True) 
 Do 

    AConnection.Execute_("INSERT INTO products(name, cost, discount) VALUES ('" +_

    aqConvert.VarToStr(Driver.Value(0)) + "','" +  aqConvert.VarToStr(Driver.Value(1)) +_ 

    "', '" + aqConvert.VarToStr(Driver.Value(2)) + "')") 
 Call Driver.Next
 Loop Until Driver.EOF 

 ' Close the connection 

  AConnection.Close
 End Sub 

function TestProc() 

{ 
 var AConnection, Driver; 


  // Create a Connection object 

  AConnection = ADO.CreateADOConnection();


  // Specify the connection string

  AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+ "Data Source=..//..//OrdersDB.mdb";


  // Suppress the login dialog box 

  AConnection.LoginPrompt = false; 

  AConnection.Open();
 

  // Delete all the content from the "products" table 

  AConnection.Execute_("DELETE * FROM products"); 


  // Populate the "products" table 

  Driver = DDT.ExcelDriver("..//..//ProductsDatasheet.xlsx","TestSheet",true); 
 while (Driver.EOF()) 

  {

    AConnection.Execute_("INSERT INTO products(name, cost, discount) VALUES ('" +_

    aqConvert.VarToStr(Driver.Value(0)) + "','" + aqConvert.VarToStr(Driver.Value(1)) + _

    "', '" + aqConvert.VarToStr(Driver.Value(2)) + "')"); 

    Driver.Next(); 

  } 


  // Close the connection 

   AConnection.Close(); 

}

procedure TestProc(); 
 var

  AConnection, Driver: OleVariant; 
 begin 


  // Create a Connection object 

  AConnection := ADO.CreateADOConnection(); 


  // Specify the connection string 

  AConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;'+ 'Data Source=../../OrdersDB.mdb'; 


  // Suppress the login dialog box 

  AConnection.LoginPrompt := false; 

  AConnection.Open(); 


  // Delete all the content from the "products" table 

  AConnection.Execute_('DELETE * FROM products'); 


  // Populate the "products" table 

  Driver := DDT.ExcelDriver('../../ProductsDatasheet.xlsx','TestSheet',true); 
 while Driver.EOF() do begin 

    AConnection.Execute_('INSERT INTO products(name, cost, discount) VALUES (''' +_

    aqConvert.VarToStr(Driver.Value(0)) + ''',''' + aqConvert.VarToStr(Driver.Value(1)) +_ 
  
    ' '', ''' + aqConvert.VarToStr(Driver.Value(2)) + ''') '); 

    Driver.Next; 
 end; 


  // Close the connection 

  AConnection.Close(); 
 end; 

function TestProc() 

{ 
 var AConnection, Driver; 


  // Create a Connection object 

  AConnection = ADO["CreateADOConnection"](); 


  // Specify the connection string 

  AConnection["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;"+ "Data Source=..//..//OrdersDB.mdb"; 


  // Suppress the login dialog box 

  AConnection["LoginPrompt"] = false; 

  AConnection["Open"](); 


  // Delete all the content from the "products" table 

  AConnection["Execute_"]("DELETE * FROM products"); 


  // Populate the "products" table 

  Driver = DDT["ExcelDriver"]("..//..//ProductsDatasheet.xlsx","TestSheet",true); 
 while (Driver["EOF"]()) 

  {
 
    AConnection["Execute_" ]("INSERT INTO products(name, cost, discount) VALUES ('" +_

     aqConvert["VarToStr" ](Driver["Value"](0)) + "','" + aqConvert["VarToStr" ](Driver["Value" ](1)) +_

     "', '" + aqConvert["VarToStr" ](Driver["Value" ](2)) + "')");

   Driver["Next"](); 

  } 


  // Close the connection 

  AConnection["Close"](); 

} 

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.

Creating a Simple Database Test

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 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 wizard is displayed.
  • On the first page of the wizard, specify the project name and the project location and click Finish.

  

  Figure 1. Creating New Project

  TestComplete will create a project and display its contents in the Project Explorer panel.

• Switch to the KeywordTests editor by double clicking the Test1 item under the KeywordTests node in the Project Explorer panel.



Figure 2. Project Explorer panel

• Add the Database Table Checkpoint operation to your keyword-driven test:
  • Select the Checkpoints category in the Operation tree and drag the Database Table Checkpoint operation to your test. After you drop the operation, the Create Database Table Checkpoint dialog will appear. On the first page of this dialog click Next to command TestComplete to create a new stored item. On the second page, specify the connection string that will be used by TestComplete.

  

  Figure 3. Create Database Table Checkpoint dialog

  • 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 and the Tets1 test contains the Database Table Checkpoint operation. After adding the operation to your test, TestComplete automatically opens the DBTable Editor:



Figure 4. 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.
• To simulate working with the database and to perform the actions described below, launch Microsoft Access and open the OrdersDB.mdb in it.



Figure 5. Web Application

• Now, we’ll make some changes in the table’s first row to illustrate the Database Table checkpoint. In the Product combo box select FamilyAlbum. In the State box type Mexico instead of US. Then, click Save.



Figure 6. Edit Window

• Close Microsoft Access.
• Now, click the Run Test button on the KeywordTests editor’s toolbar to execute the test. The log of a test run will look like this:



Figure 7. Results

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 for free.