Running a .sql file against a database during playback
I have a SQL operation that generates content on a database tied to a system that I am testing. I know that I can fire off single lines of SQL but the script is a bit more complex that that. Ba...
- You can even do it in code. Here's the extent of a whole unit of code that I have written, one function within that actually executes the script code from a file. FYI, this is encapsulated in a TestComplete ScriptExtension that I created in TestComplete 8 a while back.... if you want, I can make it available to you.
var lDatabaseName;
var lSQLServerName;
var lConnectionString;
var lConnectionObject;
function GetDatabaseName()
{
return aqConvert.VarToStr(lDatabaseName);
}
function GetSQLServerName()
{
return aqConvert.VarToStr(lSQLServerName);
}
function GetConnectionObject()
{
return lConnectionObject;
}
function SQLUtilities_Initialize()
{
if (!Project.Variables.VariableExists("DatabaseName"))
{
Project.Variables.AddVariable("DatabaseName", "String")
}
if (!Project.Variables.VariableExists("SQLServerName"))
{
Project.Variables.AddVariable("SQLServerName", "String")
}
lDatabaseName = Project.Variables.DatabaseName;
lSQLServerName = Project.Variables.SQLServerName;
lConnectionObject= Sys.OleObject("ADODB.Connection");
}
function SQLUtilities_Finalize()
{
try
{
if (lConnectionObject.State != 0)
{
lConnectionObject.Close()
}
lConnectionObject = null
}
catch (e)
{
Log.Warning(e.description)
}
}
function RefreshSettings()
{
lDatabaseName = Project.Variables.DatabaseName;
lSQLServerName = Project.Variables.SQLServerName;
}
function GetConnectionString()
{
RefreshSettings();
lConnectionString = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=';
lConnectionString = lConnectionString + lDatabaseName;
lConnectionString = lConnectionString + ';Data Source=' + lSQLServerName;
return lConnectionString;
}
function ExecSQLQueryFile(FileName)
{
try
{
RefreshSettings();
var RowCount;
lConnectionObject.ConnectionString = GetConnectionString();
var SQLQuery = aqFile.ReadWholeTextFile(aqConvert.VarToStr(FileName), aqFile.ctANSI)
lConnectionObject.Open()
lConnectionObject.Execute(SQLQuery, RowCount);
lConnectionObject.Close()
return RowCount
}
catch (e)
{
Log.Error("Error executing Query file " + aqConvert.VarToStr(FileName) + ": " + e.description)
return -1
}
}
function ExecSQLQueryFromString(QueryString)
{
try
{
RefreshSettings();
var RowCount;
lConnectionObject.ConnectionString = GetConnectionString();
lConnectionObject.Open()
lConnectionObject.Execute(aqConvert.VarToStr(QueryString), RowCount);
lConnectionObject.Close()
return RowCount
}
catch (e)
{
Log.Error("Error executing QueryString. See Additional Information for QueryString value: " + e.description, aqConvert.VarToStr(QueryString))
return -1
}
}
function ReturnQueryRecordSetFromString(QueryString)
{
try
{
RefreshSettings();
lConnectionObject.ConnectionString = GetConnectionString();
lConnectionObject.Open()
var RecordSet = lConnectionObject.Execute(aqConvert.VarToStr(QueryString));
return RecordSet;
}
catch (e)
{
Log.Error("Error running QueryString. See Additional Information for QueryString value: " + e.description, aqConvert.VarToStr(QueryString))
return aqObject.EmptyVariant;
}
}
function ExecuteStoredProcedure(ProcedureName)
{
try
{
var RowCount
RefreshSettings();
lConnectionObject.ConnectionString = GetConnectionString();
lConnectionObject.Open()
lConnectionObject.Execute("EXEC " + aqConvert.VarToStr(ProcedureName), RowCount);
lConnectionObject.Close()
return RowCount
}
catch (e)
{
Log.Error("Error running Stored procedure " + aqConvert.VarToStr(ProcedureName) + ": " + e.description)
return -1
}
}
function FormatDateForSQL(OffsetDays)
{
try
{
OffsetDays = aqConvert.VarToInt(OffsetDays)
}
catch (e)
{
Log.Warning("Value of OffsetDays is non-numeric, using zero (0) instead")
OffsetDays = 0
}
var DateValue = aqDateTime.Today();
DateValue = aqDateTime.AddDays(DateValue, OffsetDays);
return aqConvert.DateTimetoFormatStr(DateValue, '%Y-%m-%d');
}
