Connecting to a Microsoft SQL Server Database Using BDE

Applies to TestComplete 14.0, last modified on January 23, 2019

The following example illustrates how to connect to a database on Microsoft SQL Server using the Borland Database Engine (BDE). The code below creates a BDE table that holds data from the Products table of the Northwind sample database that comes with Microsoft SQL Server.

To make the sample work, do the following:

  • Open the Control Panel | Administrative Tools | Data Sources (ODBC) window and create a DSN link in the Northwind database.

  • Use the BDE Administrator utility to create a BDE alias named MYSQL for that database (the alias should use the SQL Server driver).

Note: BDE may not recognize certain field types of MS SQL Server. For instance, it does not support the nvarchar and ntext types. Fields of these types will be unavailable to your scripts. To solve this problem, you can either change field types or (what is more recommended) work with SQL Server tables via ADO methods.

JavaScript, JScript

function TestSQL_BDE()
{
  var aTable, S, i;
  // Create a table
  aTable = BDE.CreateTable();
  // Specify the database name
  aTable.DatabaseName = "MYSQL"; // <-- BDE alias
  // Specify the table name
  aTable.TableName = "Products";
  // Open the table
  aTable.Open();
  aTable.First();
  // Retrieve field names
  S = "";
  for (i = 0; i < aTable.FieldCount; i++)
    S = S + aTable.Field(i).FieldName + "\t";
  S = S + "\r\n";
  // Scan through 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();
  };
  // Output results
  Log.Message("Products", S);
  // Close the table
  aTable.Close();
}

Python

def TestSQL_BDE():
  # Create a table
  aTable = BDE.CreateTable()
  # Specify the database name
  aTable.DatabaseName = "MYSQL" # <-- BDE alias
  # Specify the table name
  aTable.TableName = "Products"
  # Open the table
  aTable.Open()
  aTable.First()
  # Retrieve field names
  S = ""
  for i in range (0, aTable.FieldCount):
    S = S + aTable.Field.Item(i).FieldName + "\t"
  S = S + "\r\n"
  # Scan through dataset records
  while not aTable.EOF:
    for i in range (0, aTable.FieldCount):
      S = S + aTable.Field.Item(i).AsString + "\t"
    S = S + "\r\n"
    aTable.Next()
  # Output results
  Log.Message("Products", S)
  # Close the table
  aTable.Close()

VBScript

Sub TestSQL_BDE
  ' Create a table
  Set aTable = BDE.CreateTable
  ' Specify the database name
  aTable.DatabaseName = "MYSQL" ' <-- BDE alias
  ' Specify the table name
  aTable.TableName = "Products"
  ' Open the table
  aTable.Open
  aTable.First
  ' Retrieve field names
  S = ""
  For i = 0 To aTable.FieldCount - 1
    S = S + aTable.Field(i).FieldName + Chr(9)
  Next
  S = S + Chr(13) + Chr(10)
  ' Scan through dataset records
  Do While Not aTable.EOF
    For i = 0 To aTable.FieldCount - 1
      S = S + aTable.Field(i).AsString + Chr(9)
    Next
    S = S + Chr(13) + Chr(10)
    aTable.Next
  Loop
  ' Output results
  Log.Message "Products", S
  ' Close the table
  aTable.Close
End Sub

DelphiScript

procedure TestSQL_BDE;
var
  aTable, S, i : OleVariant;
begin
  // Create a table
  aTable := BDE.CreateTable;
  // Specify the database name
  aTable.DatabaseName := 'MYSQL'; // <-- BDE alias
  // Specify the table name
  aTable.TableName := 'Products';
  // Open the table
  aTable.Open;
  aTable.First;
  // Retrieve field names
  S := '';
  for i := 0 to aTable.FieldCount - 1 do
    S := S + aTable.Field(i).FieldName + Chr(9);
  S := S + Chr(13) + Chr(10);
  // Scan through dataset records
  while not aqConvert.VarToBool(aTable.EOF) do
  begin
    for i := 0 to aTable.FieldCount - 1 do
      S := S + aTable.Field(i).AsString + Chr(9);
    S := S + Chr(13) + Chr(10);
    aTable.Next;
  end;
  // Output results
  Log.Message('Products', S);
  // Close the table
  aTable.Close;
end;

C++Script, C#Script

function TestSQL_BDE()
{
  var aTable, S, i;
  // Create a table
  aTable = BDE["CreateTable"]();
  // Specify the database name
  aTable["DatabaseName"] = "MYSQL"; // <- BDE alias
  // Specify the table name
  aTable["TableName"] = "Products";
  // Open the table
  aTable["Open"]();
  aTable["First"]();
  // Retrieve field names
  S = "";
  for (i = 0; i < aTable["FieldCount"]; i++)
    S = S + aTable["Field"](i)["FieldName"] + "\t";
  S = S + "\r\n";
  // Scan through 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"]();
  };
  // Output results
  Log["Message"]("Products", S);
  // Close the table
  aTable["Close"]();
}

Highlight search results