Forum Discussion

smccafferty's avatar
smccafferty
Occasional Contributor
11 years ago

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


    }

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    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');


    }

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    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.
  • smccafferty's avatar
    smccafferty
    Occasional Contributor
    WoW.

    That's brilliant thanks.



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



    Top notch chaps!
  • smccafferty's avatar
    smccafferty
    Occasional Contributor
    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.
  • smccafferty's avatar
    smccafferty
    Occasional Contributor
    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.


     

  • smccafferty's avatar
    smccafferty
    Occasional Contributor
    Part 2: Running the Batch file





    function AddDummyContent()

    {

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

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



    }



    WORKS!



    Woot!
  • smccafferty's avatar
    smccafferty
    Occasional Contributor
    "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!




  • smccafferty's avatar
    smccafferty
    Occasional Contributor
    Absolute gentleman, thank you!



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



    Cheers!