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. Ba...
  • tristaanogre's avatar
    11 years ago
    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');


    }