Forum Discussion

twe69's avatar
twe69
Occasional Contributor
6 years ago

TestComplete and SQL databases - Restoring per test

Hi there,

 

Just looking at ways of implementing a way to restore a test SQL database back to it's original condition once a load of TestComplete tests run against it.

I have found this link but a little confused on how to implement it: https://community.smartbear.com/t5/TestComplete-Desktop-Testing/Restoring-SQL-db-quickly-through-TestComplete/m-p/120425/highlight/true#M6401

But then found this as well, again a little confused to implement it: http://raaviblog.com/how-to-restore-sql-database-in-testcomplete-using-javascript/

 

However another question I had was could we not run a BEGIN TRANSACTION before any test and then a ROLLBACK at the end of it? No tables are being created or dropped.

 

All help is appreciated!

  • twe69's avatar
    twe69
    6 years ago

    Unfortunately it didn't however I was able to get some help from a developer within our company and have got the following working:

     

    function TestProc()
{
var Qry;
var con;
// Create a query
Qry = ADO.CreateADOQuery();
con = ADO.CreateADOConnection();
con.ConnectionString = "Provider=MSOLEDBSQL.1;User ID=XX;Password=XX;Initial Catalog=XXXXDB;Data Server=computer\\sqlserverinstance,1433;"
con.CommandTimeout = 1000
con.LoginPrompt = false;
    con.Open()
Qry.ConnectionString = con.ConnectionString;
Qry.SQL = "SELECT * FROM Users";
queryResult = Qry.FieldCount;
// Closes the query
con.Close();
}
     

     

  • LinoTadros's avatar
    LinoTadros
    Community Hero
    6 years ago

    My recommendation is to take a snapshot of your database and export it (schema and data) to a .sql file

    Before the start of your test, use the ADO object in TestComplete to execute the .sql file to create the database with a known schema and data every time you run the test.

     

    Hope that helps

    Cheers

    Lino

    • twe69's avatar
      twe69
      Occasional Contributor
      6 years ago

       Thanks for the reply LinoTadros I have seen a few people mention that as a good solution so will look into applying that.

       

      I did have a few more questions however, there are several people who will use the tests and/or create them. I believe having their own version of the test database to work against is a good thing to stop issues from occurring such as one user changing something that affects the other.

       

      My thoughts were to deploy the database as per the script, but randomise the database name somehow. However I would then need to change the registry to our product to point to that particular database & restart the IIS. Could something like Powershell be utilised or am I over thinking things? Again we would look into things like overnight test runs but also possibly linking the tests into Zephyr on an indiviual basis.

      • LinoTadros's avatar
        LinoTadros
        Community Hero
        6 years ago

        The name of the database and the connection string can be added as Project Variables that can be accessed from outside of the Project itself so that they can be executed from the command line by passing the /var parameter.  

         

        Cheers

        Lino

    • twe69's avatar
      twe69
      Occasional Contributor
      6 years ago

      LinoTadros  Sorry I am a little confused on how to create/run this method.

       

      From the tutorials I should be making a 'Script' with the ADO Object that points to the sql file? is that correct? I think I am seeing to many options and variations that are confusing me. Would you have a very basic sample handy? Thanks!

      • twe69's avatar
        twe69
        Occasional Contributor
        6 years ago

        Can someone point me in the right direction with this script please, am not able to connect to the SQL Server.

         

        function TestADO()
        {
        var Conn = Sys["OleObject"]("ADODB.Connection");
        Conn["ConnectionString"] = "Provider=SQLOLEDB;SERVER=\\UXXXXXXX\MSSQLSERVER2017;Database=DBXXXXX;User Id=UserXXXX;Password=PassXXXXX;"
        Conn["Open"]();

        ADO.CreateADOQuery("Select * FROM Users")
        // Close the recordset and connection
        Conn["Close"]();
        }