TestComplete samples (both built-in and additional) are located in the <Users>\Public\Public Documents\TestComplete 14 Samples folder.

Some file managers display the Public Documents folder as Documents.

When testing database-aware applications, you may need to verify that a database contains relevant data. The common way to perform this kind of verification in TestComplete is to use database checkpoints. If for some reason you cannot use database checkpoints for verification in your tests, try one of the following alternative approaches:

Use the DBTables.DBTableName.Compare Scripting Method

Create a Custom Verification Procedure

Use the DBTables.DBTableName.Compare Scripting Method

In script tests, to check data retrieved from a database against the baseline copy of the data stored in your project, you can use the DBTables.DBTableName.Compare method. It allows comparing actual values of the retrieved recordset with expected values stored in your project as a DBTable element of the Stores > DBTables collection. To call this method in keyword tests, you can use the Call Object Method, the Run Code Snippet and Run Script Routine operations.

The method is similar to the database checkpoint, but it posts less detailed verification results. It also has additional MessageType and ReportDifference parameters. The ReportDifference parameter specifies whether the method should log notifications about differences that were found in case the comparison fails. The MessageType parameter specifies what kind of message (an error, warning, informative message or no message at all) will be posted to the test log if the comparison fails.

The following code example demonstrates how to use the method:

JavaScript, JScript

function TestProc()
{

  // Compares the DBTable element with the data stored in a database
  if (! DBTables.DBTable1.Compare())
    Log.Error("The database tables are not equal.");

}

Python

def TestProc():

  # Compares the DBTable element with the data stored in a database
  if not DBTables.DBTable1.Compare():
    Log.Error("The database tables are not equal.")

VBScript

Sub TestProc

  ' Compares the DBTable element with the data stored in a database
  If Not DBTables.DBTable1.Compare Then
    Log.Error("The database tables are not equal.")
  End If

End Sub

DelphiScript

procedure TestProc();
begin

  // Compares the DBTable element with the data stored in a database
  if not DBTables.DBTable1.Compare then
    Log.Error('The database tables are not equal.');

end;

C++Script, C#Script

function TestProc()
{

  // Compares the DBTable element with the data stored in a database
  if (! DBTables["DBTable1"]["Compare"]())
    Log["Error"]("The database tables are not equal.");

}

Similar to the database checkpoints, the method supports database data update. If the Update DBTable elements option is enabled, the method will update the baseline data stored in the corresponding DBTable element with the actual data retrieved from the database.

Create a Custom Verification Procedure

In tests, to check whether the database stores relevant data, perform the following steps:

Connect to desired database tables. You can do this in one of the following ways:
- By using the ADO DB functionality included in Microsoft Windows. For information on how to do that, see Using ADO Components.
- By using methods and properties of ADO or BDE program objects provided by TestComplete. For information on how to do that, see Working With Databases Using ADO and BDE Objects.
Obtain desired data from the database and verify the data. To verify the data, you can compare it with the baseline data by using the if … then … else statement and standard comparison operations, like =, < or >. Note that to store baseline values in your project, you can use table variables, DB table variables or DB Table elements.
Report the results.

The custom verification procedure lets you perform very specific verification actions.

The following example demonstrates how you can use the ADO program object to connect to a database and retrieve data from it. The sample routine iterates through table records of the database, checks the expiration dates of the cards, and posts warnings for the orders with expired card dates to the test log.

Notes:

This example uses the OrdersDB.mdb file that is part of the additional sample package. To use it, download this package from support.smartbear.com/downloads/testcomplete/samples/ and install it. After the installation is over, you can find the database in the <TestComplete 14 Samples>\Desktop\Checkpoints\XML\DataGridViewSample folder.
Using the Microsoft.Jet.OLEDB.4.0 provider requires that you run your script in the 32-bit version of TestComplete.

TestComplete 32-bit executable is located in the <TestComplete>\Bin folder.

JavaScript, JScript

function Test()
{
  var tbl, expDate, id;
  // Create an IAQAADOTable object
  tbl = ADO.CreateADOTable();
  // Specify the connection string
  tbl.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  tbl.TableName = "orders";
  // Open the table
  tbl.Open();
  // Iterate the records and checks the data
  tbl.First();
  while (! tbl.EOF)
  {
    expDate = tbl.FieldByName("exp").Value;
    if (expDate < aqDateTime.Today())
    {
      id = tbl.FieldByName("id").Value;
      Log.Warning("The expiration date of the card specified in order #" +
                  aqConvert.VarToStr(id) +
                  " is " + aqConvert.VarToStr(expDate) +
                  ". The card is expired.");
    }
    tbl.Next();
  }
  tbl.Close();
}

Python

def Test():
  # Create an IAQAADOTable object
  tbl = ADO.CreateADOTable()
  # Specify the connection string
  tbl.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+ \
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  tbl.TableName = "orders"
  # Open the table
  tbl.Open()
  # Iterate the records and checks the data
  tbl.First()
  while not tbl.EOF:
    expDate = tbl.FieldByName("exp").Value
    if expDate < aqDateTime.Today():
      id = tbl.FieldByName("id").Value
      Log.Warning("The expiration date of the card specified in order #" + \
                  aqConvert.VarToStr(id) + \
                  " is " + \
                  aqConvert.VarToStr(expDate) + \
                  ". The card is expired.")
    tbl.Next()
  tbl.Close()

VBScript

Sub Test
  ' Create an IAQAADOTable object
  Set tbl = ADO.CreateADOTable
  ' Specify the connection string
  tbl.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
  "Data Source=C:\Users\Public\Documents\TestComplete 14 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb"
  tbl.TableName = "orders"
  ' Open the table
  tbl.Open
  ' Iterate the records and checks the data
  tbl.First
  While Not tbl.EOF
    expDate = tbl.FieldByName("exp").Value
    If expDate < aqDateTime.Today Then
      id = tbl.FieldByName("id").Value
      Log.Warning("The expiration date of the card specified in order #" & _
                  aqConvert.VarToStr(id) & _
                  " is " & _
                  aqConvert.VarToStr(expDate) & _
                  ". The card is expired.")
    End If
    tbl.Next
  WEnd
  tbl.Close
End Sub

DelphiScript

procedure Test();
var tbl, expDate, id : OleVariant;
begin
  // Create an IAQAADOTable object
  tbl := ADO.CreateADOTable();
  // Specify the connection string
  tbl.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'Data Source=C:\Users\Public\Documents\TestComplete 14 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb';
  tbl.TableName := 'orders';
  // Open the table
  tbl.Open();
  // Iterate the records and checks the data
  tbl.First();
  while not tbl.EOF do
  begin
    expDate := tbl.FieldByName('exp').Value;
    if expDate < aqDateTime.Today then
    begin
      id := tbl.FieldByName('id').Value;
      Log.Warning('The expiration date of the card specified in order #' +
                  aqConvert.VarToStr(id) +
                  ' is ' +
                  aqConvert.VarToStr(expDate) +
                  '. The card is expired.');
    end;
    tbl.Next();
  end;
  tbl.Close();
end;

C++Script, C#Script

function Test()
{
  var tbl, expDate, id;
  // Create an IAQAADOTable object
  tbl = ADO.CreateADOTable();
  // Specify the connection string
  tbl["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;"+
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  tbl["TableName"] = "orders";
  // Open the table
  tbl["Open"]();
  // Iterate the records and checks the data
  tbl["First"]();
  while (! tbl["EOF"])
  {
    expDate = tbl["FieldByName"]("exp")["Value"];
    if (expDate < aqDateTime["Today"]())
    {
      id = tbl["FieldByName"]("id")["Value"];
      Log["Warning"]("The expiration date of the card specified in order #" +
                  aqConvert["VarToStr"](id) +
                  " is " +
                  aqConvert["VarToStr"](expDate) +
                  ". The card is expired.");
    }
    tbl["Next"]();
  }
  tbl["Close"]();
}

Alternatives to Database Checkpoints

Use the DBTables.DBTableName.Compare Scripting Method

Create a Custom Verification Procedure

See Also