Database Testing With TestComplete: Typical Tasks

Files

Author: SmartBear Software
Applies to: TestComplete 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

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.

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

Each DB Table store links to database tables and queries and provide serial read-only 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.

TestComplete includes the ADO scripting object that helps you automate populating databases with data. Some methods of this object (CreateCommand, CreateConnection and CreateRecordset) return references to the appropriate Microsoft ADO objects: ADO Command, Connection and Recordset. These are ADO object references, and in your TestComplete scripts and keyword tests, you can use the same methods and properties that are mentioned in the MSDN library. The ADO object also contains some more methods that return wrappers for data-aware VCL objects for ADO. So, if you know how to work with VCL ADO objects, you can use the same methods and properties in your tests.

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:

Name Cost Discount
MyMoney 100 8
FamilyAlbum 80 15
ScreenSaver 20 10

We will demonstrate how to test a database by using the sample OrdersDB.mdb database. This database is part of TestComplete samples. You can find it in the \Desktop\Checkpoints\Database\DataGridViewSample folder. You can also download this database directly from this page by clicking the Sample Database link in the menu above.

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. This guarantees that the test run will not fail due to data discrepancies.

VBScript:
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 
JScript:
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(); 

}
DelphiScript:
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; 
C#Script, C++Script:
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"](); 

} 

 

Creating a Simple Database Test

Now let’s create a simple test in which a database checkpoint will verify whether the database has changed. When creating the checkpoint, we will obtain data from the ‘orders’ table of the OrdersDB.mdb database. Then we will manually modify the content of the database table by using Microsoft Access. The test will compare the stored baseline copy with the actual content of the database table and display the results in the test log. It does not matter what type of application you will test: the database support is available for both desktop, web and mobile applications.

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.

      Creating New Project
      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.

      Test Items
      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 specify the name of a new stored item and click Next to command TestComplete to create the stored item.
      • On the second page, specify the connection string that will be used by TestComplete.

        Creating DataBase Table
        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:

      DB Stores
      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.

      Web Application
      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.

      Edit Window
      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:

      Results
      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.

    Visit our TestComplete Training and Certification center as well.

More Information