Connecting to a Microsoft SQL Server Database Using ADO

Applies to TestComplete 14.30, last modified on November 21, 2019

This example illustrates how to connect to a database on Microsoft SQL Server using the ActiveX Data Objects (ADO). The following code retrieves data from the Products table of the Northwind sample database that comes with Microsoft SQL Server and then writes this data to the test log. To make the sample work, we created a DSN link to the Northwind database. To create a DSN link, go to Control Panel | Administrative Tools | Data Sources (ODBC) and use the ODBC Data Source Administrator.

The TestComplete ADO object includes methods of two kinds:

  • Some methods return objects that are analogues to Delphi ADO objects,

  • Others return “native” ADO objects.

The script example below demonstrates both approaches: the TestSQL_ADO routine accesses the database table via analogues of Delphi ADO objects; the TestSQL_ADO2 routine demonstrates access via native ADO objects.

Note: You can create ADO objects (connections, recordsets, commands, etc.) via OLE and then call their methods in scripts For more information, see Working With Databases.

The following code illustrates access to the database table using analogues to Delphi ADO objects:

JavaScript, JScript

// Access via analogues of Delphi ADO objects
function TestSQL_ADO()
{
  var aTable, S, i;
  // Creates a table
  aTable = ADO.CreateADOTable();
  // Specifies the database name
  aTable.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN";
  // Specifies the table name
  aTable.TableName = "Products";
  // Opens the table
  aTable.Open();
  aTable.First();
  // Retrieves field names
  S = "";
  for (i = 0; i < aTable.FieldCount; i++)
    S = S + aTable.Field(i).FieldName + "\t";
  S = S + "\r\n";
  // Scans dataset records
  while (! aTable.EOF)
  {
    for (i = 0; i < aTable.FieldCount; i++)
      S = S + aTable.Field(i).AsString + "\t";
    S = S + "\r\n";
    aTable.Next();
  }
  // Outputs results
  Log.Message("Products", S);
  // Closes the table
  aTable.Close();
}

Python

# Access via analogues of Delphi ADO objects
def TestSQL_ADO():
  # Creates a table
  aTable = ADO.CreateADOTable()
  # Specifies the database name
  aTable.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN"
  # Specifies the table name
  aTable.TableName = "Products"
  # Opens the table
  aTable.Open()
  aTable.First()
  # Retrieves field names
  S = ""
  for i in range (0, aTable.FieldCount):
    S = S + aTable.Field[i].FieldName + "\t"
  S = S + "\r\n"
  # Scans dataset records
  while not aTable.EOF:
    for i in range (0, aTable.FieldCount):
      S = S + aTable.Field[i].AsString + "\t"
    S = S + "\r\n"
    aTable.Next()
  # Outputs results
  Log.Message("Products", S)
  # Closes the table
  aTable.Close()

VBScript

' Access via analogues of Delphi ADO objects
Sub TestSQL_ADO
  ' Creates a table
  Set aTable = ADO.CreateADOTable
  ' Specifies the database name
  aTable.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN"
  ' Specifies the table name
  aTable.TableName = "Products"
  ' Opens the table
  aTable.Open
  aTable.First
  ' Retrieves field names
  S = ""
  For i = 0 To aTable.FieldCount - 1
    S = S & aTable.Field(i).FieldName &vbTab
  Next
  S = S & vbCrLf
  ' Scans dataset records
  Do While Not aTable.EOF
    For i = 0 To aTable.FieldCount - 1
      S = S & aTable.Field(i).AsString & vbTab
    Next
    S = S & vbCrLf
    aTable.Next
  Loop
  ' Outputs results
  Log.Message "Products", S
  ' Closes the table
  aTable.Close
End Sub

DelphiScript

// Access via analogues of Delphi ADO objects
procedure TestSQL_ADO;
var
  aTable, S, i : OleVariant;
begin
  // Creates a table
  aTable := ADO.CreateADOTable;
  // Specifies the database name
  aTable.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN';
  // Specifies the table name
  aTable.TableName := 'Products';
  // Opens the table
  aTable.Open;
  aTable.First;
  // Retrieves field names
  S := '';
  for i := 0 to aTable.FieldCount - 1 do
    S := S + aTable.Field(i).FieldName + #9;
  S := S + #13#10;
  // Scans dataset records
  while not aqConvert.VarToBool(aTable.EOF) do
  begin
    for i := 0 to aTable.FieldCount - 1 do
      S := S + aTable.Field(i).AsString + #9;
    S := S + #13#10;
    aTable.Next;
  end;
  // Outputs results
  Log.Message('Products', S);
  // Closes the table
  aTable.Close;
end;

C++Script, C#Script

// Access via analogues of Delphi ADO objects
function TestSQL_ADO()
{
  var aTable, S, i;
  // Creates a table
  aTable = ADO["CreateADOTable"]();
  // Specifies the database name
  aTable["ConnectionString"] = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN";
  // Specifies the table name
  aTable["TableName"] = "Products";
  // Opens the table
  aTable["Open"]();
  aTable["First"]();
  // Retrieves field names
  S = "";
  for (i = 0; i < aTable["FieldCount"]; i++)
    S = S + aTable["Field"](i)["FieldName"] + "\t";
  S = S + "\r\n";
  // Scans dataset records
  while (! aTable["EOF"])
  {
    for (i = 0; i < aTable["FieldCount"]; i++)
      S = S + aTable["Field"](i)["AsString"] + "\t";
    S = S + "\r\n";
    aTable["Next"]();
  }
  // Outputs results
  Log["Message"]("Products", S);
  // Closes the table
  aTable["Close"]();
}

The following code illustrates access to the database table using “native” ADO objects:

JavaScript, JScript

// Access via "native" ADO objects
function TestSQL_ADO2()
{
  var aCon, aCmd, aRecSet, s, i;
  // Creates ADO connection
  aCon = ADO.CreateConnection();
  // Sets up the connection parameters
  aCon.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN";
  // Opens the connection
  aCon.Open();
  // Creates a command and specifies its parameters
  aCmd = ADO.CreateCommand();
  aCmd.ActiveConnection = aCon; // Connection
  aCmd.CommandType = adCmdTable; // Command type
  aCmd.CommandText = "Products"; // Table name
  // Opens a recordset
  aRecSet = aCmd.Execute();
  aRecSet.MoveFirst();
  // Obtains field names
  s = "";
  for (i = 0; i < aRecSet.Fields.Count; i++)
    s = s + aRecSet.Fields.Item(i).Name + "\t";
  s = s + "\r\n";
  // Scans recordset
  while (! aRecSet.EOF)
  {
    for (i = 0; i < aRecSet.Fields.Count; i++)
      s = s + aRecSet.Fields.Item(i).Value + "\t";
    s = s + "\r\n";
    aRecSet.MoveNext();
  }
  // Outputs results
  Log.Message("Products", s);
  // Closes the recordset and connection
  aRecSet.Close();
  aCon.Close();
}

Python

# Access via "native" ADO objects
def TestSQL_ADO2():
  # Creates ADO connection
  aCon = ADO.CreateConnection()
  # Sets up the connection parameters
  aCon.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN"
  # Opens the connection
  aCon.Open()
  # Creates a command and specifies its parameters
  aCmd = ADO.CreateCommand()
  aCmd.ActiveConnection = aCon # Connection
  aCmd.CommandType = adCmdTable # Command type
  aCmd.CommandText = "Products" # Table name
  # Opens a recordset
  aRecSet = aCmd.Execute()
  aRecSet.MoveFirst()
  # Obtains field names
  s = ""
  for i in range (0, aRecSet.Fields.Count):
    s = s + aRecSet.Fields.Item[i].Name + "\t"
  s = s + "\r\n"
  # Scans recordset
  while not aRecSet.EOF:
    for i in range (0, aRecSet.Fields.Count):
      s = s + str(aRecSet.Fields.Item[i].Value) + "\t"
    s = s + "\r\n"
    aRecSet.MoveNext()
  # Outputs results
  Log.Message("Products", s)
  # Closes the recordset and connection
  aRecSet.Close()
  aCon.Close()

VBScript

' Access via "native" ADO objects
Sub TestSQL_ADO2
  ' Creates ADO connection
  Set aCon = ADO.CreateConnection
  ' Sets up the connection parameters
  aCon.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN"
  ' Opens the connection
  aCon.Open
  ' Creates a command and specifies its parameters
  Set aCmd = ADO.CreateCommand
  Set aCmd.ActiveConnection = aCon ' Connection
  aCmd.CommandType = adCmdTable ' Command type
  aCmd.CommandText = "Products" ' Table name
  ' Opens a recordset
  Set aRecSet = aCmd.Execute
  aRecSet.MoveFirst
  ' Obtains field names
  s = ""
  For i = 0 To aRecSet.Fields.Count - 1
    s = s & aRecSet.Fields.Item(i).Name & vbTab
  Next
  s = s & vbCrLf
  ' Scans recordset
  Do While Not aRecSet.EOF
    For i = 0 To aRecSet.Fields.Count - 1
      s = s & CStr(aRecSet.Fields.Item(i).Value) & vbTab
    Next
    s = s & vbCrLf
    aRecSet.MoveNext
  Loop
  ' Outputs results
  Log.Message "Products", s
  ' Closes the recordset and connection
  aRecSet.Close
  aCon.Close
End Sub

DelphiScript

// Access via "native" ADO objects
procedure TestSQL_ADO2;
var
  aCon, aCmd, aRecSet, S, i : OleVariant;
begin
  // Creates ADO connection
  aCon := ADO.CreateConnection;
  // Sets up the connection parameters
  aCon.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN';
  // Opens the connection
  aCon.Open;
  // Creates a command and specifies its parameters
  aCmd := ADO.CreateCommand;
  aCmd.ActiveConnection := aCon; // Connection
  aCmd.CommandType := adCmdTable; // Command type
  aCmd.CommandText := 'Products'; // Table name
  // Opens a recordset
  aRecSet := aCmd.Execute;
  aRecSet.MoveFirst;
  // Obtains field names
  s := '';
  for i := 0 to aRecSet.Fields.Count - 1 do
    s := s + aRecSet.Fields.Item(i).Name + #9;
  s := s + #13#10;
  // Scans recordset
  while not aRecSet.EOF do
  begin
    for i := 0 to aRecSet.Fields.Count - 1 do
      s := s + aqConvert.VarToStr(aRecSet.Fields.Item(i).Value) + #9;
    s := s + #13#10;
    aRecSet.MoveNext;
  end;
  // Outputs results
  Log.Message('Products', s);
  // Closes the recordset and connection
  aRecSet.Close;
  aCon.Close;
end;

C++Script, C#Script

// Access via "native" ADO objects
function TestSQL_ADO2()
{
  var aCon, aCmd, aRecSet, s, i;
  // Creates ADO connection
  aCon = ADO["CreateConnection"]();
  // Sets up the connection parameters
  aCon["ConnectionString"] = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=NameOfMyDSN";
  // Opens the connection
  aCon["Open"]();
  // Creates a command and specifies its parameters
  aCmd = ADO["CreateCommand"]();
  aCmd["ActiveConnection"] = aCon; // Connection
  aCmd["CommandType"] = adCmdTable; // Command type
  aCmd["CommandText"] = "Products"; // Table name
  // Opens a recordset
  aRecSet = aCmd["Execute"]();
  aRecSet["MoveFirst"]();
  // Obtains field names
  s = "";
  for (i = 0; i < aRecSet["Fields"]["Count"]; i++)
    s = s + aRecSet["Fields"]["Item"](i)["Name"] + "\t";
  s = s + "\r\n";
 // Scans recordset
  while (! aRecSet["EOF"])
  {
    for (i = 0; i < aRecSet["Fields"]["Count"]; i++)
      s = s + aRecSet["Fields"]["Item"](i)["Value"] + "\t";
    s = s + "\r\n";
    aRecSet["MoveNext"]();
  }
  // Outputs results
  Log["Message"]("Products", s);
  // Closes the recordset and connection
  aRecSet["Close"]();
  aCon["Close"]();
}

Highlight search results