cancel
Showing results for 
Search instead for 
Did you mean: 

TestComplete and SQL databases - Restoring per test

SOLVED
Occasional Contributor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Occasional Contributor

Re: TestComplete and SQL databases - Restoring per test

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

 

View solution in original post

14 REPLIES 14
Highlighted
Community Leader

Re: TestComplete and SQL databases - Restoring per test

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

Occasional Contributor

Re: TestComplete and SQL databases - Restoring per test

 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.

Community Leader

Re: TestComplete and SQL databases - Restoring per test

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

Occasional Contributor

Re: TestComplete and SQL databases - Restoring per test

Thanks, will look into it this week as a PoC

Super Contributor

Re: TestComplete and SQL databases - Restoring per test

Hi

I do slightly the same thing but I use a batch file that drives 2 .sql files

 

this is an example with mysql or mariadb :

 

first .sql file just drop the existing database if exists (sqlfileone) :

 

drop database if exists ****;
create database ****;
use ****;

 

The second SQL file is the dump of your database (don't forget to export everything to include eventual database objects as triggers, ... and include create schema)

 

The 2 sql files are launched by this batch file :

c:
cd C:\Program Files\MySql\bin\
mysql -ulogin -ppassword mysql < pathtosqlfileone.sql
c:
cd C:\Program Files\MySql\bin\
mysql -ulogin -ppassword mysql < pathtosqlfiletwo.sql
Occasional Contributor

Re: TestComplete and SQL databases - Restoring per test

@m_essaid do you have a link to how to run batch files? I am quite new to Test Complete and looking towards sorting out the environment at the moment before I get training under way.

Occasional Contributor

Re: TestComplete and SQL databases - Restoring per test

@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!

Occasional Contributor

Re: TestComplete and SQL databases - Restoring per test

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

Community Hero

Re: TestComplete and SQL databases - Restoring per test

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


Robert Martin
[Community Expert Group]
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
Extensions available
New Here?
Join us and watch the welcome video:
Watch the new Interview
Top Kudoed Authors