Solved
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.
Basically, I'd like to know if TestComplete, using c#, can fire off SQL against a database when it's contained in a SQL file.
I have a script that can connect and run single, simple queries. Just wondering if it can grab that .sql file and execute as it would make my life that little bit easier.
Cheers.
PS a big thanks to the support team at Smartbear. Excellent.
Basically, I'd like to know if TestComplete, using c#, can fire off SQL against a database when it's contained in a SQL file.
I have a script that can connect and run single, simple queries. Just wondering if it can grab that .sql file and execute as it would make my life that little bit easier.
Cheers.
PS a big thanks to the support team at Smartbear. Excellent.
- 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');
}
