Modifying DBTable Elements

Applies to TestComplete 15.47, last modified on January 20, 2023

You use the DBTables collection to compare database contents in tests. Each element of the collection (a DBTable element) stores values and connection settings of the database table to compare.

You can edit an element’s data manually in the DBTable Element editor or from tests.

Note: To update all the data stored in a DBTable element automatically, see Updating DBTable Elements.

Modifying Data in the Editor

You can view and edit an element’s data using the DBTable Element editor. To modify data in the editor, click the needed cell and type a new value in the in-place editor. To confirm the change, press Enter. To discard the change, press Esc.

To set an empty value, delete the current value in the cell and press Enter. TestComplete will change the column's type to string, set an empty value as the cell’s data and display a message informing you about it. Confirm the changes.

If you specify any other value in the empty cell later, TestComplete will treat the value as a string and compare it by using string comparison operations.

To edit connection settings, click Edit and follow the instructions of the Select Database Table wizard.

For more information on working with the editor, see About DBTable Element Editor.

Modifying Data From Tests

To edit data of DBTable elements from scripts, use methods and properties of the DBTable object. You can change both stored values and connection settings.

The DBTable object provides access to a copy of the stored data. In other words, changes that you make in tests will not apply to values and connection settings stored in the project. They exist only during the test run and will be lost after the test run is over.

To edit stored values, use the DBTable.Values property. The property takes two parameters that specify the row and the column of the needed cell. The following code shows how you can use this property to edit the values:

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

JavaScript

DBTables.DBTable1.$set("Values", 2, 0, "My new text");
DBTables.DBTable1.$set("Values", 2, 1, 123.45);

JScript

DBTables.DBTable1.Values(2, 0) = "My new text";
DBTables.DBTable1.Values(2, 1) = 123.45;

Python

DBTables.DBTable1.Values[2, 0] = "My new text"
DBTables.DBTable1.Values[2, 1] = 123.45

VBScript

DBTables.DBTable1.Values(2, 0) = "My new text"
DBTables.DBTable1.Values(2, 1) = 123.45

DelphiScript

DBTables.DBTable1.Values[2, 0] := 'My new text';
DBTables.DBTable1.Values[2, 1] := 123.45;

C++Script, C#Script

DBTables["DBTable1"]["Values"](2, 0) = "My new text";
DBTables["DBTable1"]["Values"](2, 1) = 123.45;

The row and column indexes are zero-based. They coincide with the row’s and the column’s position of the cell in the DBTable Element editor. The leftmost column of the editor contains the row index.

The DBTable.RowCount property returns the total number of stored rows. To get the column index, you can also use the ColumnIndex property of the DBTable object. This property returns the column’s index by the column name.

You can also use the DBTable.ValuesSelected property to specify whether TestComplete will use a value for comparison on not. The property has two parameters that specify the row and column indexes of the desired value:

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

JavaScript

function Test()
{

  // Get column indexes
  let Col1 = DBTables.DBTable1.ColumnIndex("Author");
  let Col2 = DBTables.DBTable1.ColumnIndex("Year Born");

  // Iterate through stored rows
  for(let i = 0; i < DBTables.DBTable1.RowCount; i++)
  {
    if(DBTables.DBTable1.Values(i, Col2) != "")
    {
      DBTables.DBTable1.$set("ValuesSelected", i, Col1, false )
      DBTables.DBTable1.$set("ValuesSelected", i, Col2, false)
    }
  }

  // Compare values
  if(DBTables.DBTable1.Compare(false))
    Log.Message("OK !");
  else 
    Log.Error("Comparison failed.");
}

JScript

function Test()
{
  var Col1, Col2, i;

  // Get column indexes
  Col1 = DBTables.DBTable1.ColumnIndex("Author");
  Col2 = DBTables.DBTable1.ColumnIndex("Year Born");

  // Iterate through stored rows
  for(i = 0; i < DBTables.DBTable1.RowCount; i++)
  {
    if(DBTables.DBTable1.Values(i, Col2) != "")
    {
      DBTables.DBTable1.ValuesSelected(i, Col1) = false 
      DBTables.DBTable1.ValuesSelected(i, Col2) = false
    }
  }

  // Compare values
  if(DBTables.DBTable1.Compare(false))
    Log.Message("OK !");
  else 
    Log.Error("Comparison failed.");
}

Python

def Test():
  # Get column indexes
  Col1 = DBTables.DBTable1.ColumnIndex("Author")
  Col2 = DBTables.DBTable1.ColumnIndex("Year Born")

  # Iterate through stored rows
  for i in range(0, DBTables.DBTable1.RowCount): 
    if DBTables.DBTable1.Values[i, Col2] != "":
      DBTables.DBTable1.ValuesSelected[i, Col1] = False 
      DBTables.DBTable1.ValuesSelected[i, Col2] = False

  # Compare values
  if DBTables.DBTable1.Compare(False):
    Log.Message("OK !")
  else:
    Log.Error("Comparison failed.");

VBScript

Sub Test
  ' Get column indexes
  Col1 = DBTables.DBTable1.ColumnIndex("Author")
  Col2 = DBTables.DBTable1.ColumnIndex("Year Born")

  ' Iterate through stored rows
  For i = 0 To DBTables.DBTable1.RowCount - 1
    If DBTables.DBTable1.Values(i, Col2) <> "" Then
      DBTables.DBTable1.ValuesSelected(i, Col1) = False
      DBTables.DBTable1.ValuesSelected(i, Col2) = False
    End If
  Next 

  ' Compare values
  If DBTables.DBTable1.Compare(False) Then
    Call Log.Message("OK !")
  Else 
    Call Log.Error("Comparison failed.")
  End If
End Sub

DelphiScript

procedure Test;
var
  Col1, Col2, i : OleVariant;
begin
  // Get column indexes
  Col1 := DBTables.DBTable1.ColumnIndex('Author');
  Col2 := DBTables.DBTable1.ColumnIndex('Year Born');

  // Iterate through stored rows
  for i := 0 to DBTables.DBTable1.RowCount - 1 do
  begin 
    if DBTables.DBTable1.Values[i, Col2] <> '' then
    begin
      DBTables.DBTable1.ValuesSelected[i, Col1] := False;
      DBTables.DBTable1.ValuesSelected[i, Col2] := False;
    end;
  end;

  // Compare values
  if DBTables.DBTable1.Compare(False) then
    Log.Message('OK !')
  else 
    Log.Error('Comparison failed.');
end;

C++Script, C#Script

function Test()
{
  var Col1, Col2, i;

  // Get column indexes
  Col1 = DBTables["DBTable1"]["ColumnIndex"]("Author");
  Col2 = DBTables["DBTable1"]["ColumnIndex"]("Year Born");

  // Iterate through stored rows
  for(i = 0; i < DBTables["DBTable1"]["RowCount"]; i++)
  {
    if(DBTables["DBTable1"]["Values"](i, Col2) != "")
    {
      DBTables["DBTable1"]["ValuesSelected"](i, Col1) = false 
      DBTables["DBTable1"]["ValuesSelected"](i, Col2) = false
    }
  }

  // Compare values
  if(DBTables["DBTable1"]["Compare"](false))
    Log["Message"]("OK !");
  else 
    Log["Error"]("Comparison failed.");
}

To edit the connection settings, use the following properties of the DBTable object:

  • ConnectionString - Specifies the string holding the database connection settings.

  • Login - Specifies the user name for connecting to the database.

  • Password - Specifies the password that will be used to connect to the database.

To call methods and properties of the DBTable object in keyword tests, use the Run Script Routine or Run Code Snippet operation.

See Also

About DBTables Collection
About Database Checkpoints

Highlight search results