This following code example illustrates how you can use the CreateADOCommand
method in your scripts. It uses the Biblio.mdb database shipped with Microsoft Visual Basic. The TestProc
routine uses the IAQADOCommand
object to execute a query with parameters. Query results are then posted to the test log.
Notes:
-
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.
TestComplete 32-bit executable is located in the <TestComplete>\Bin folder.
JavaScript
function TestProc()
{
var RecSet, Cmd;
// Create a new object
Cmd = ADO.CreateADOCommand();
// Specify the connection string
Cmd.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Microsoft Visual Studio\\VB98\\biblio.mdb";
// Specify the command text (the SQL expression)
Cmd.CommandText = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam";
// Specify the command type
Cmd.CommandType = cmdText;
// Specify the parameter of the query
Cmd.Parameters.Items(0).Value = 1960;
// Execute the command
RecSet = Cmd.Execute();
// Process the table records
RecSet.MoveFirst();
while (! RecSet.EOF)
{
if (RecSet.Fields.Item("Year Born").Value > 0)
Log.Message(RecSet.Fields.Item("Author").Value, RecSet.Fields.Item("Year Born").Value);
RecSet.MoveNext();
};
}
JScript
function TestProc()
{
var RecSet, Cmd;
// Create a new object
Cmd = ADO.CreateADOCommand();
// Specify the connection string
Cmd.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Microsoft Visual Studio\\VB98\\biblio.mdb";
// Specify the command text (the SQL expression)
Cmd.CommandText = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam";
// Specify the command type
Cmd.CommandType = cmdText;
// Specify the parameter of the query
Cmd.Parameters.Items(0).Value = 1960;
// Execute the command
RecSet = Cmd.Execute();
// Process the table records
RecSet.MoveFirst();
while (! RecSet.EOF)
{
if (RecSet.Fields("Year Born").Value > 0)
Log.Message(RecSet.Fields("Author").Value, RecSet.Fields("Year Born").Value);
RecSet.MoveNext();
};
}
Python
def TestProc():
# Create a new object
Cmd = ADO.CreateADOCommand()
# Specify the connection string
Cmd.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + \
"Data Source=C:\\Microsoft Visual Studio\\VB98\\biblio.mdb"
# Specify the command text (the SQL expression)
Cmd.CommandText = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam"
# Specify the command type
Cmd.CommandType = cmdText
# Specify the parameter of the query
Cmd.Parameters.Items[0].Value = 1960
# Execute the command
RecSet = Cmd.Execute()
# Process the table records
RecSet.MoveFirst()
while not RecSet.EOF:
if RecSet.Fields.Item["Year Born"].Value > 0:
Log.Message(RecSet.Fields.Item["Author"].Value, RecSet.Fields.Item["Year Born"].Value);
RecSet.MoveNext()
VBScript
Sub TestProc
' Create a new object
Set Cmd = ADO.CreateADOCommand
' Specify the connection string
Cmd.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=C:\Microsoft Visual Studio\VB98\biblio.mdb"
' Specify the command text (the SQL expression)
Cmd.CommandText = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam"
' Specify the command type
Cmd.CommandType = cmdText
' Specify the parameter of the query
Cmd.Parameters.Items(0).Value = 1960
' Execute the command
Set RecSet = Cmd.Execute
' Process the table records
RecSet.MoveFirst
While Not RecSet.EOF
If RecSet.Fields("Year Born").Value > 0 Then
Log.Message RecSet.Fields("Author").Value, RecSet.Fields("Year Born").Value
End If
RecSet.MoveNext
Wend
End Sub
DelphiScript
procedure TestProc;
var
RecSet, Cmd: OleVariant;
begin
// Create a new object
Cmd := ADO.CreateADOCommand;
// Specify the connection string
Cmd.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;'+
'Data Source=C:\Microsoft Visual Studio\VB98\biblio.mdb';
// Specify the command text (the SQL expression)
Cmd.CommandText := 'SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam';
// Specify the command type
Cmd.CommandType := cmdText;
// Specify the parameter of the query
Cmd.Parameters.Items(0).Value := 1960;
// Execute the command
RecSet := Cmd.Execute;
// Process the table records
RecSet.MoveFirst;
while not aqConvert.VarToBool(RecSet.EOF) do
begin
if RecSet.Fields('Year Born').Value > 0 then
Log.Message(RecSet.Fields('Author').Value, RecSet.Fields('Year Born').Value);
RecSet.MoveNext;
end;
end;
C++Script, C#Script
function TestProc()
{
var RecSet, Cmd;
// Create a new object
Cmd = ADO["CreateADOCommand"]();
// Specify the connection string
Cmd["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Microsoft Visual Studio\\VB98\\biblio.mdb";
// Specify the command text (the SQL expression)
Cmd["CommandText"] = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam";
// Specify the command type
Cmd["CommandType"] = cmdText;
// Specify the parameter of the query
Cmd["Parameters"]["Items"](0)["Value"] = 1960;
// Execute the command
RecSet = Cmd["Execute"]();
// Process the table records
RecSet["MoveFirst"]();
while (!RecSet["EOF"])
{
if (RecSet["Fields"]("Year Born")["Value"] > 0)
Log["Message"](RecSet["Fields"]("Author")["Value"], RecSet["Fields"]("Year Born")["Value"]);
RecSet["MoveNext"]();
};
}