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 bu...
  • 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

        Thanks, will look into it this week as a PoC

  • 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"]();
      }

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        6 years ago

        If you're using JavaScript, you need to double-up on all you \ characters.