Alternatives to Database Checkpoints

Applies to TestComplete 12.60, last modified on September 17, 2018

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
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript, JScript

function Test()
{

  // 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 Test():

  # 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 Test

  ' 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 Test();
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 Test()
{

  // 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 to use the ADO program object to connect to a database and retrieve data from it. This example uses the OrdersDB.mdb file that is a part of of the additional sample package. To use it, download this package from https://support.smartbear.com/downloads/testcomplete/samples/ and install it. After the installation is over, you can find the database in the <TestComplete 12 Samples>\Desktop\Checkpoints\Database\DataGridViewSample folder. The sample routine iterates through records of the orders table of the database and checks the quantity of products of each order.

Note:

Using the Microsoft.Jet.OLEDB.4.0 provider requires that you run your script in the 32-bit version of TestComplete.
JavaScript, JScript
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript, JScript

function Test()
{

  var samplesPath = aqEnvironment.GetEnvironmentVariable("public") + "\\Documents\\TestComplete 12 Samples\\Desktop\\Checkpoints\\Database";
  var ordersDBWorkPath = samplesPath + "\\DataGridViewSample\\bin\\Release\\OrdersDB.mdb";

  var tbl = ADO.CreateADOTable();
  // Specifies the connection string
  tbl.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+
    "Data Source=" + ordersDBWorkPath;
  tbl.TableName = "orders";
  // Opens the table
  tbl.Open();

  // Iterates the records and checks the data
  tbl.First();
  while (! tbl.EOF)
  {

    var expDate = tbl.FieldByName("exp").Value;
    if (expDate < aqDateTime.Today())
    {

      var 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():
  samplesPath = aqEnvironment.GetEnvironmentVariable("public") + "\\Documents\\TestComplete 11 Samples\\Desktop\\Checkpoints\\Database"
  ordersDBWorkPath = samplesPath + "\\DataGridViewSample\\bin\\Release\\OrdersDB.mdb"

  tbl = ADO.CreateADOTable()
  # Specifies the connection string
  tbl.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+ \
    "Data Source=" + ordersDBWorkPath
  tbl.TableName = "orders"
  # Opens the table
  tbl.Open()

  # Iterates 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

  samplesPath = aqEnvironment.GetEnvironmentVariable("public") & "\Documents\TestComplete 12 Samples\Desktop\Checkpoints\Database"
  ordersDBWorkPath = samplesPath & "\DataGridViewSample\bin\Release\OrdersDB.mdb"

  Set tbl = ADO.CreateADOTable
  ' Specifies the connection string
  tbl.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ordersDBWorkPath
  tbl.TableName = "orders"
  ' Opens the table
  tbl.Open

  ' Iterates 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 samplesPath, ordersDBWorkPath, tbl, expDate, id : OleVariant;
begin

  samplesPath := aqEnvironment.GetEnvironmentVariable('public') + '\Documents\TestComplete 12 Samples\Desktop\Checkpoints\Database';
  ordersDBWorkPath := samplesPath + '\DataGridViewSample\bin\Release\OrdersDB.mdb';

  tbl := ADO.CreateADOTable();
  // Specifies the connection string
  tbl.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
    'Data Source=' + ordersDBWorkPath;
  tbl.TableName := 'orders';
  // Opens the table
  tbl.Open();

  // Iterates 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 samplesPath = aqEnvironment["GetEnvironmentVariable"]("public") + "\\Documents\\TestComplete 12 Samples\\Desktop\\Checkpoints\\Database";
  var ordersDBWorkPath = samplesPath + "\\DataGridViewSample\\bin\\Release\\OrdersDB.mdb";

  var tbl = ADO.CreateADOTable();
  // Specifies the connection string
  tbl["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;"+
    "Data Source=" + ordersDBWorkPath;
  tbl["TableName"] = "orders";
  // Opens the table
  tbl["Open"]();

  // Iterates the records and checks the data
  tbl["First"]();
  while (! tbl["EOF"])
  {

    var expDate = tbl["FieldByName"]("exp")["Value"];
    if (expDate < aqDateTime["Today"]())
    {

      var 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