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"]();
}