TestComplete supports database access through ActiveX Data Objects (ADO). This approach allows your scripts to execute SQL queries or stored procedures directly from Oracle databases.
| Note: |
You can create ADO objects (such as connections, recordsets, and commands) through OLE and then call their methods in scripts. For more information, see Working With Databases. |
The following code illustrates access to a Stored Procedure in Oracle using ADO objects:
JavaScript, JScript
// Define the function
function CallOracleStoredProcedure() {
// Declare constants
var adCmdStoredProc = 4;
var adParamInput = 1;
var adParamOutput = 2;
var adVarChar = 200;
var adNumeric = 131;
// Create and configure connection
var conn = new ActiveXObject("ADODB.Connection");
conn.ConnectionString = "DSN=server;UID=user;PWD=password;";
conn.ConnectionTimeout = 30;
conn.Open();
conn.CommandTimeout = 1200;
// Create and configure command
var cmd = new ActiveXObject("ADODB.Command");
cmd.ActiveConnection = conn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = "APPS.MYSCHEMA.SOME_PACKAGE.STORED_PROC_NAME";
cmd.CommandTimeout = 1200;
cmd.Parameters.Append(cmd.CreateParameter("order_type", adVarChar, adParamInput, 3, "ANY"));
cmd.Parameters.Append(cmd.CreateParameter("order_is_active", adVarChar, adParamInput, 1, "Y"));
var param = cmd.CreateParameter("order_number", adNumeric, adParamOutput);
param.Precision = 18;
param.NumericScale = 0;
cmd.Parameters.Append(param);
cmd.Parameters.Append(cmd.CreateParameter("order_name", adVarChar, adParamOutput, 50));
// Execute
cmd.Execute();
// Output
Log.Message("Output Order Number: " + cmd.Parameters("order_number").Value);
Log.Message("Output VIN: " + cmd.Parameters("order_name").Value);
conn.Close();
}
Python
# Define the function
def CallOracleStoredProcedure():
import win32com.client
# Declare constants
adCmdStoredProc = 4
adParamInput = 1
adParamOutput = 2
adVarChar = 200
adNumeric = 131
# Create and configure connection
conn = win32com.client.Dispatch("ADODB.Connection")
conn.ConnectionString = "DSN=server;UID=user;PWD=password;"
conn.ConnectionTimeout = 30
conn.Open()
conn.CommandTimeout = 1200
# Create and configure command
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "APPS.MYSCHEMA.SOME_PACKAGE.STORED_PROC_NAME"
cmd.CommandTimeout = 1200
cmd.Parameters.Append(cmd.CreateParameter("order_type", adVarChar, adParamInput, 3, "ANY"))
cmd.Parameters.Append(cmd.CreateParameter("order_is_active", adVarChar, adParamInput, 1, "Y"))
param = cmd.CreateParameter("order_number", adNumeric, adParamOutput)
param.Precision = 18
param.NumericScale = 0
cmd.Parameters.Append(param)
cmd.Parameters.Append(cmd.CreateParameter("order_name", adVarChar, adParamOutput, 50))
# Execute
cmd.Execute()
# Output
Log.Message("Output Order Number: " + str(cmd.Parameters("order_number").Value))
Log.Message("Output VIN: " + str(cmd.Parameters("order_name").Value))
conn.Close()
VBScript
' Declare constants for ADO
Sub CallOracleStoredProcedure()
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adParamOutput = 2
Const adVarChar = 200
Const adNumeric = 131
' Create and configure connection
Dim conn, cmd, param
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "DSN=server;UID=user;PWD=password;"
conn.ConnectionTimeout = 30
conn.Open
conn.CommandTimeout = 1200
' Create and configure command
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "APPS.MYSCHEMA.SOME_PACKAGE.STORED_PROC_NAME"
cmd.CommandTimeout = 1200
Set param = cmd.CreateParameter("order_type", adVarChar, adParamInput, 3, "ANY")
cmd.Parameters.Append param
Set param = cmd.CreateParameter("order_is_active", adVarChar, adParamInput, 1, "Y")
cmd.Parameters.Append param
Set param = cmd.CreateParameter("order_number", adNumeric, adParamOutput)
param.Precision = 18
param.NumericScale = 0
cmd.Parameters.Append param
Set param = cmd.CreateParameter("order_name", adVarChar, adParamOutput, 50)
cmd.Parameters.Append param
' Execute the stored procedure
cmd.Execute
' Output the returned values
Log.Message "Output Order Number: " & cmd.Parameters("order_number").Value
Log.Message "Output VIN: " & cmd.Parameters("order_name").Value
' Clean up
conn.Close
Set cmd = Nothing
Set conn = Nothing
End Sub
DelphiScript
// Define the procedure
procedure CallOracleStoredProcedure();
const
adCmdStoredProc = 4;
adParamInput = 1;
adParamOutput = 2;
adVarChar = 200;
adNumeric = 131;
var
conn, cmd, param: OleVariant;
begin
// Create and configure connection
conn := CreateOleObject('ADODB.Connection');
conn.ConnectionString := 'DSN=server;UID=user;PWD=password;';
conn.ConnectionTimeout := 30;
conn.Open;
conn.CommandTimeout := 1200;
// Create and configure command
cmd := CreateOleObject('ADODB.Command');
cmd.ActiveConnection := conn;
cmd.CommandType := adCmdStoredProc;
cmd.CommandText := 'APPS.MYSCHEMA.SOME_PACKAGE.STORED_PROC_NAME';
cmd.CommandTimeout := 1200;
cmd.Parameters.Append(cmd.CreateParameter('order_type', adVarChar, adParamInput, 3, 'ANY'));
cmd.Parameters.Append(cmd.CreateParameter('order_is_active', adVarChar, adParamInput, 1, 'Y'));
param := cmd.CreateParameter('order_number', adNumeric, adParamOutput);
param.Precision := 18;
param.NumericScale := 0;
cmd.Parameters.Append(param);
cmd.Parameters.Append(cmd.CreateParameter('order_name', adVarChar, adParamOutput, 50));
// Execute
cmd.Execute;
// Output
Log.Message('Output Order Number: ' + VarToStr(cmd.Parameters['order_number'].Value));
Log.Message('Output VIN: ' + VarToStr(cmd.Parameters['order_name'].Value));
// Clean up
conn.Close;
end;
C++Script, C#Script
// Define the function
function CallOracleStoredProcedure() {
// Declare constants
var adCmdStoredProc = 4;
var adParamInput = 1;
var adParamOutput = 2;
var adVarChar = 200;
var adNumeric = 131;
// Create and configure connection
var conn = new ActiveXObject("ADODB.Connection");
conn.ConnectionString = "DSN=server;UID=user;PWD=password;";
conn.ConnectionTimeout = 30;
conn.Open();
conn.CommandTimeout = 1200;
// Create and configure command
var cmd = new ActiveXObject("ADODB.Command");
cmd.ActiveConnection = conn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = "APPS.MYSCHEMA.SOME_PACKAGE.STORED_PROC_NAME";
cmd.CommandTimeout = 1200;
cmd.Parameters.Append(cmd.CreateParameter("order_type", adVarChar, adParamInput, 3, "ANY"));
cmd.Parameters.Append(cmd.CreateParameter("order_is_active", adVarChar, adParamInput, 1, "Y"));
var param = cmd.CreateParameter("order_number", adNumeric, adParamOutput);
param.Precision = 18;
param.NumericScale = 0;
cmd.Parameters.Append(param);
cmd.Parameters.Append(cmd.CreateParameter("order_name", adVarChar, adParamOutput, 50));
// Execute
cmd.Execute();
// Output
Log.Message("Output Order Number: " + cmd.Parameters("order_number").Value);
Log.Message("Output VIN: " + cmd.Parameters("order_name").Value);
// Clean up
conn.Close();
}