cancel
Showing results for 
Search instead for 
Did you mean: 

Running a .sql file against a database during playback

SOLVED
smccafferty
Occasional Contributor

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.
The Force Is Strong With This One.
1 ACCEPTED SOLUTION

Accepted Solutions
tristaanogre
Community Hero

RE: Running a .sql file against a database during playback

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');


}


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

8 REPLIES 8
smccafferty
Occasional Contributor

RE: Running a .sql file against a database during playback

I've tried by using sqlcmd inside a batch file too.

Having problems running that itself. I'll see if I can get it to work. If I can, I'll post a solution here.
The Force Is Strong With This One.
smccafferty
Occasional Contributor

RE: Running a .sql file against a database during playback

I'm doing this in two parts.



1. Create a batch file to execute the .sql file

2. Create a script to execute the batch file in testcomplete 10



Part 1.

Here's the contents of the batch file (obviously I've left my connection strings out):




sqlcmd -U username@servername -P ********** -S servername -d database -i "filepatchtoSQLfile\CreateDummyData_metalearningTest.sql"



This is for batch execution of an sql file against an azure database.



I'll post part 2 as soon as I get it automated.



Basically, what this means for me, is that I can begin each test with a backup of a clean database and then add the content in one go before continuing testing.


 

The Force Is Strong With This One.
smccafferty
Occasional Contributor

RE: Running a .sql file against a database during playback

Part 2: Running the Batch file





function AddDummyContent()

{

 var WScriptObj = Sys.OleObject("WScript.Shell");

 WScriptObj.Run("C:\BatchFileTitle.bat");



}



WORKS!



Woot!
The Force Is Strong With This One.
tristaanogre
Community Hero

RE: Running a .sql file against a database during playback

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');


}


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

smccafferty
Occasional Contributor

RE: Running a .sql file against a database during playback

WoW.

That's brilliant thanks.



I'll get that in and edit it to suit.



Top notch chaps!
The Force Is Strong With This One.
smccafferty
Occasional Contributor

RE: Running a .sql file against a database during playback

"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."



Perfect and that should work in TC10 too yeah?

That would be great thanks!




The Force Is Strong With This One.
tristaanogre
Community Hero

RE: Running a .sql file against a database during playback

The code itself is not dependant upon anything explicitly TC 8 so, yes, it is available for TC 10.  Attached.  All you need to do is change it from .zip to .tcx and you should be good to go.

Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
smccafferty
Occasional Contributor

RE: Running a .sql file against a database during playback

Absolute gentleman, thank you!



I'll give this a go and then get back to you.



Cheers!
The Force Is Strong With This One.
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors