Comparing Migrated Database Tables
Many people have to migrate databases during the course of their testing. Perhaps they're upgrading to a new version of SQL Server, or perhaps their table structure has changed. Either way, they want to make sure that the information that was in a table in the old database exists in the corresponding table in the new database. This article provides you with the information you need to perform this type of verification.
Here's my scenario. I have a database called BooksSamples, which has been migrated to a database called migratedSamples. I want to make sure that all the information that was in the Authors table of the original BookSamples made it over to the Authors table in the migratedSamples.
To do this, I'm going to get all the records from both the original Authors table and the migrated Authors table and store them off as xml files. Then I'm going to have TestComplete compare the two files via an XML checkpoint and log any discrepancies.
Let's start out by adding an empty XML checkpoint to our project. We'll use this checkpoint later to perform the actual comparison between the two record sets. To do this, expand the Stores node in the project explorer tree, right click on the XML node and select Add>New Item. In the Create Project Item window, enter a name for the checkpoint (in my case I took the default of XMLCheckpoint1) and click OK.
I've also created ODBC datasources for both my databases. I could use full connection strings, but I find that ODBC datasources are easier to read & reference. I named my data sources BooksSamples and migratedSamples, respectively. If you don't know how to create datasources, click here
Now for the code. I started out by writing this queryRunner function. It does the heavy lifting for this test, meaning it gathers all the records from a database table and saves those records off as xml. The code is fully commented, so you can see what each line is doing.
With that code written, I created this main function that pulls the test together.
So to summarize, we're running a query against two separate databases via ODBC data source. We take the output of those queries and then compare them via XML checkpoint. If they match, a success message is written to the log. If they fail, an error will be written to the log, along with information on what nodes between the two files were different.
Until next time, onward automation!