Variables of the DB Table Type

Applies to TestComplete 14.40, last modified on April 22, 2021

DB Table variables provide serial access to tabular data stored outside of your TestComplete project. The variables support the following storages:

  • Database tables or queries

  • Excel files

  • CSV (comma-separated values) files

Each DB Table variable contains the link to the data storage and provides access to rows and columns. You can use DB Table variables in data-driven loops.

DB Table variables provide read-only access to data. You cannot use them to change values stored in data storages.

You can only create temporary project and project suite variables of the DB Table type. Persistent variables of the DB Table type are not supported.

Requirements

To access data in Excel files, you must have Microsoft Office 12.0 Access Database Engine OLE DB Provider installed on your computer. Make sure the “bitness” of this data provider (32- or 64-bit) is the same the TestComplete “bitness”, otherwise, you will get the “The needed data source driver is not installed...” error message. To learn more, see Microsoft Office Excel Data Connectivity.

Creating and Editing DB Table Variables

You can create DB Table variables and set their properties on the Variables page of the Project, ProjectSuite or Keyword Test editor:

  1. Right-click somewhere within the Variables page and then click New item. This will add a new variable to the list.

    By default, the variable will be of the String type.

  2. Change the variable type. To do this, select DB Table from the Type drop-down list.

To set or edit the variable’s initial value:

  1. Click the ellipsis button in the Default Value column of the Variables page. This will invoke the Edit DB Table Variable Value wizard.

  2. Use the dialog to specify the data link settings and select the rows to be retrieved by the test engine when it works with the variable.

Working With DB Table Variables

The created variable stores the link to the data storage, but it does not get data from it. It will get the data when you access the variable in a test for the first time.

Note: The data the DB Table variable gets is static: if the data storage is modified, the variable value will not be updated. There is no designated way to update the variable data. For a possible workaround, see below.

To address variables in tests, use the following syntax:

Project.Variables.Variable_Name
ProjectSuite.Variables.Variable_Name
KeywordTests.Test_Name.Variables.Variable_Name
 – or –
Project.Variables.VariableByName(Variable_Name)
ProjectSuite.Variables.VariableByName(Variable_Name)
KeywordTests.Test_Name.Variables.Variable_Name

To access data that the DB Table variables retrieve, use the properties and methods of the variable:

  • To iterate through data rows, use the DBTableVariable.Next method. It forwards the iterator to the next row of the storage.

  • To set the iterator to the initial position, call the DBTableVariable.Reset method. Call this method from your tests every time before you start iterating through rows.

  • To access an individual entry, use the DBTableVariable.Value property. The property has one parameter that specifies the name of the index of the column that stores the needed entry.

    The Value property provides read-only access to the data. You cannot use it to set the entry value.

The following code demonstrates how you can work with DB Table variables from scripts:

JavaScript, JScript
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript, JScript

function Test()
{
  var value;

  // Initialize the iterator
  Project.Variables.DBVar1.Reset();
  
  // Iterate through rows
  while(! Project.Variables.DBVar1.IsEOF() )
  {
    // Retrieve a value
    value = Project.Variables.DBVar1.Value("ColumnName");
  
    // ...
    // Use the value in the needed way
    // ...

    // Forward the iterator to the next row
    Project.Variables.DBVar1.Next();
  }
}

Python

def Test():
    # Initialize the iterator
  Project.Variables.DBVar1.Reset()
  
  # Iterate through rows
  while not Project.Variables.DBVar1.IsEOF():
    # Retrieve a value
    value = Project.Variables.DBVar1.Value["ColumnName"]
  
    # ...
    # Use the value in the needed way
    # ...

    # Forward the iterator to the next row
    Project.Variables.DBVar1.Next()

VBScript

Sub Test
  ' Initialize the iterator
  Project.Variables.DBVar1.Reset
  ' Iterate through rows
  While Not Project.Variables.DBVarVar1.IsEOF
    ' Retrieve a value
    value = Project.Variables.DBVarVar1.Value("ColumnName")
  
    ' ...
    ' Use the value in the needed way
    ' ...
 
    ' Forward the iterator to the next row
    Project.Variables.DBVarVar1.Next
  WEnd
End Sub

DelphiScript

procedure Test;
var
  value : OleVariant;
begin
  // Initialize the iterator
  Project.Variables.DBVar1.Reset();
  
  // Iterate through rows
  while not Project.Variables.DBVar1.IsEOF() do
  begin
    // Retrieve a value
    value := Project.Variables.DBVar1.Value['ColumnName'];
  
    // ...
    // Use the value in the needed way
    // ...

    // Forward the iterator to the next row
    Project.Variables.DBVar1.Next();
  end;
end;

C++Script, C#Script

function Test()
{
  var value;

  // Initialize the iterator
  Project["Variables"]["DBVar1"]["Reset"]();
  
  // Iterate through rows
  while(! Project["Variables"]["DBVar1"]["IsEOF"]() )
  {
    // Retrieve a value
    value = Project["Variables"]["DBVar1"]["Value"]("ColumnName");
  
    // ...
    // Use the value in the needed way
    // ...

    // Forward the iterator to the next row
    Project["Variables"]["DBVar1"]["Next"]();
  }
}

You can also use variables of this type to specify parameters of keyword test operations. You can do this in the Operation Parameters dialog used to view and modify operation parameters. For detailed information about this, see Specifying Operation Parameters.

Updating DB Table Variable Values

A DB Table variable gets data from the specified source when you first access the variable in your test. However, the obtained data is static. If the source is modified, the variable value will not be updated.

To update a DB Table variable value during the test run anyway, you can use the following workaround:

  1. Create another variable of the DB Table type. You can do this manually from the TestComplete IDE, or you can do this directly in your test by using the Variables.AddVariable method.

  2. In your test, after your data source is updated, assign the value of the initial DB Table variable to the new variable.

  3. Assign the value of the new variable back to the initial variable.

TestComplete will update the data the initial variable stores.

The code below demonstrates how to update a DB Table variable value in tests:

JavaScript, JScript
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript, JScript

function UpdateDBTableVar()
{
  // Get the initial DB Table variable value
  var v = Project.Variables.initialDBTableVar.Value("Column_Name");
  …

  // Update the data source
  …

  // Create a new DB Table variable and assign the value of the initial DB Table variable to it
  Project.Variables.AddVariable("newDBTableVar", "DB Table");
  var newDBTableVar = Project.Variables.VariableByName("newDBTableVar");
  newDBTableVar = Project.Variables.initialDBTableVar;

  // Assign the new variable's value back to the initial variable
  // The initial variable value will be updated
  Project.Variables.initialDBTableVar = newDBTableVar;

  …
}

Python

def UpdateDBTableVar():
  # Get an initial DB Table variable value
  v = Project.Variables.initialDBTableVar.Value["Column_Name"];
  # ...

  # Update the data source
  # ...

  # Create a new DB Table variable and assign the initial DB Table variable's value to it
  Project.Variables.AddVariable("newDBTableVar", "DB Table");
  newDBTableVar = Project.Variables.VariableByName["newDBTableVar"];
  newDBTableVar = Project.Variables.initialDBTableVar;

  # Assign the new variable's value back to the initial variable
  # Initial variable value will be updated
  Project.Variables.initialDBTableVar = newDBTableVar;

  # ...

VBScript

Sub UpdateDBTableVar
  ' Get the initial DB Table variable value
  v = Project.Variables.initialDBTableVar.Value("Column_Name")
  …

  ' Update the data source
  …

  ' Create a new DB Table variable and assign the value of the initial DB Table variable to it
  Call Project.Variables.AddVariable("newDBTableVar", "DB Table")
  Set newDBTableVar = Project.Variables.VariableByName("newDBTableVar")
  Set newDBTableVar = Project.Variables.initialDBTableVar

  ' Assign the new variable's value back to the initial variable
  ' The initial variable value will be updated
  Project.Variables.initialDBTableVar = newDBTableVar

  …
End Sub

DelphiScript

procedure UpdateDBTableVar();
var v, newDBTableVar;
begin
  // Get the initial DB Table variable value
  v := Project.Variables.initialDBTableVar.Value('Column_Name');
  …

  // Update the data source
  …

  // Create a new DB Table variable and assign the value of the initial DB Table variable to it
  Project.Variables.AddVariable('newDBTableVar', 'DB Table');
  newDBTableVar := Project.Variables.VariableByName('newDBTableVar');
  newDBTableVar := Project.Variables.initialDBTableVar;

  // Assign the new variable's value back to the initial variable
  // The initial variable value will be updated
  Project.Variables.initialDBTableVar := newDBTableVar;

  …
end;

C++Script, C#Script

function UpdateDBTableVar()
{
  // Get the initial DB Table variable value
  var v = Project["Variables"]["initialDBTableVar"]["Value"]("Column_Name");
  …

  // Update the data source
  …

  // Create a new DB Table variable and assign the value of the initial DB Table variable to it
  Project["Variables"]["AddVariable"]("newDBTableVar", "DB Table");
  var newDBTableVar = Project["Variables"]["VariableByName"]("newDBTableVar");
  newDBTableVar = Project["Variables"]["initialDBTableVar"];

  // Assign the new variable's value back to the initial variable
  // The initial variable value will be updated
  Project["Variables"]["initialDBTableVar"] = newDBTableVar;

  …
}

Note: Access the needed DB Table variable directly rather than by reference, otherwise, its value will not be updated.

See Also

Variable Data Types
Using Variables
Project And Project Suite Variables
Working With Project and Project Suite Variables in Scripts
Keyword Test Variables
Working With the Table Variable Editor

Highlight search results