Using ADO Components

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

If your tested application works with data stored in a database, you can access the database directly from your tests to verify that the application updates the data correctly. For example, you can create a test that verifies that the New Client dialog adds a new record to a database table and does not leave the new record in a local buffer.

To work with databases from tests, you can use the ADO object provided by TestComplete. It allows accessing ADO objects created with Microsoft and Borland applications.

Supported VCL interfaces

The ADO object provides interfaces similar to VCL ADO objects:

Interfaces in ADO Support Plugin VCL Analogue
IAQAADOConnection TADOConnection
IAQAADOCommand TADOCommand
IAQAADODataset TADODataset
IAQACustomADODataset TCustomADODataset
IAQAADOTable TADOTable
IAQAADOQuery TADOQuery
IAQAADOStoredProc TADOStoredProc

These interfaces implement the same methods and properties as their VCL analogues, using the same names. For more information, see VCL documentation on ADODB classes.

Native ADO objects

The ADO object also has methods that allow getting native ADO objects, for example, Connection, Command, and Recordset. For a description of ADO objects and their functionality, see the MSDN article on ADO objects.

Example

The following example demonstrates how you can instantiate Microsoft ADO DB objects in your scripts and use them to iterate through table records.

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/testcomplete/downloads/samples and install it. After the installation is over, you can find the database in the <TestComplete 15 Samples>\Desktop\Checkpoints\XML\DataGridViewSample folder.

  • The syntax of the command’s query depends on the SQL provider you use. For instance, if you work through the Microsoft.Jet.OLEDB.4.0 provider, you can specify parameters by their names. Some other providers may use question marks (?) as parameter placeholders (the first question mark corresponds to the first parameter, the second - to the second and so on). Some providers may support both techniques, or use their own syntax. For more information on the command syntax, see the database provider's documentation.

  • Using the Microsoft.Jet.OLEDB.4.0 provider requires that you run your script in the 32-bit version of TestComplete.

JavaScript

function TestADO()
{
  // Create and open a connection to the OrdersDB.mdb database
  var Conn = getActiveXObject("ADODB.Connection");
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 15 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  Conn.Open();

  // Create a recordset
  var Rs = getActiveXObject("ADODB.Recordset");
  // Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open("orders", Conn, 3 /* adOpenStatic */,
                           1 /* adLockReadOnly */, 2 /* adCmdTable */);

  // Read customer names from the Names column of the Orders table
  Log.AppendFolder("Customer names");
  Rs.MoveFirst();
  while(! Rs.EOF)
  {
    Log.Message(Rs.Fields.Item("name").Value);
    Rs.MoveNext();
  }

  // Close the recordset and connection
  Rs.Close();
  Conn.Close();
}

JScript

function TestADO()
{
  // Create and open a connection to the OrdersDB.mdb database
  var Conn = new ActiveXObject("ADODB.Connection");
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 15 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  Conn.Open();
 
  // Create a recordset
  var Rs = new ActiveXObject("ADODB.Recordset");
  // Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open("orders", Conn, 3 /* adOpenStatic */,
                           1 /* adLockReadOnly */, 2 /* adCmdTable */);
  // Read customer names from the Names column of the Orders table
  Log.AppendFolder("Customer names");
  Rs.MoveFirst();
  while(! Rs.EOF)
  {
    Log.Message(Rs.Fields.Item("name").Value);
    Rs.MoveNext();
  }
  
 // Close the recordset and connection
  Rs.Close();
  Conn.Close();
}

Python

def TestADO(): 
  # Create and open a connection to the OrdersDB.mdb database
  Conn = Sys.OleObject["ADODB.Connection"]
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + \
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb"
  Conn.Open()
                              
  # Create a recordset
  Rs = Sys.OleObject["ADODB.Recordset"]
  # Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open("orders", Conn, adOpenStatic, adLockReadOnly, adCmdTable);
                              
  # Read customer names from the Names column of the Orders table
  Log.AppendFolder("Customer names")
  Rs.MoveFirst()
  while not Rs.EOF:
    Log.Message(Rs.Fields.Item["name"].Value)
    Rs.MoveNext()    
                                
  # Close the recordset and connection
  Rs.Close()
  Conn.Close()

VBScript

Sub TestADO
  Dim Conn, Rs
  
  ' Create and open a connection to the OrdersDB.mdb database
  Set Conn = CreateObject("ADODB.Connection")
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
  "Data Source=C:\Users\Public\Documents\TestComplete 15 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb"
  Conn.Open

  ' Create a recordset
  Set Rs = CreateObject("ADODB.Recordset")
  ' Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open "orders", Conn, 3, 1, 2 ' adOpenStatic, adLockReadOnly, adCmdTable

  ' Read customer names from the Names column of the Orders table
  Log.AppendFolder "Customer names"
  Rs.MoveFirst
  While Not Rs.EOF
    Log.Message Rs.Fields.Item("name").Value
    Rs.MoveNext
  WEnd

  ' Close the recordset and connection
  Rs.Close
  Conn.Close

End Sub

DelphiScript

procedure TestADO;
var
  Conn, Rs : OleVariant;
begin
  
  // Create and open a connection to the OrdersDB.mdb database
  Conn := Sys.OleObject['ADODB.Connection'];
  Conn.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'Data Source=C:\Users\Public\Documents\TestComplete 15 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb';
  Conn.Open;

  // Create a recordset
  Rs := Sys.OleObject['ADODB.Recordset'];
  // Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open('orders', Conn, 3 {adOpenStatic},
                            1 {adLockReadOnly}, 2 {adCmdTable});
  
  // Read customer names from the Names column of the Orders table
  Log.AppendFolder('Customer names');
  Rs.MoveFirst;
  while not Rs.EOF do
  begin
    Log.Message(Rs.Fields.Item('name').Value);
    Rs.MoveNext;
  end;

  // Close the recordset and connection
  Rs.Close;
  Conn.Close;
end;

C++Script, C#Script

function TestADO()
{
  // Create and open a connection to the OrdersDB.mdb database
  var Conn = Sys["OleObject"]("ADODB.Connection");
  Conn["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 15 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  Conn["Open"]();
  
  // Create a recordset
  var Rs = Sys["OleObject"]("ADODB.Recordset");
  // Open the recordset and get the Orders table from the OrdersDB database
  Rs["Open"]("orders", Conn, 3 /* adOpenStatic */,
                              1 /* adLockReadOnly */, 2 /*adCmdTable*/);
 
  // Read customer names from the Names column of the Orders table
  Log["AppendFolder"]("Customer names");
  Rs["MoveFirst"]();
  while(! Rs["EOF"])
  {
    Log["Message"](Rs["Fields"]["Item"]("name")["Value"]);
    Rs["MoveNext"]();
  }
  
 // Close the recordset and connection
  Rs["Close"]();
  Conn["Close"]();
}

See Also

Working With Databases

Highlight search results