Applies to TestComplete 14.10, last modified on May 13, 2019

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

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:

  • 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:

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"]();
}

See Also

Database Checkpoints
About Database Checkpoints
How the Database Verification Procedure Works
Database Table Checkpoint Operation
Check Method
Working With Databases

Highlight search results