Forum Discussion

bach_ls's avatar
bach_ls
Occasional Contributor
5 years ago
Solved

Compare two SQL query results

Hi,

in my application we rely heavily on database operations and its contents, so data is key asset. I'd like to use testcomplete to compare two tables from db, and I wonder if there are some features, which can help me achieve my task. What I want:

 

- dynamically query the base and compare two result sets (one from production db, one from db under tests)

- optionally I can save query results into tables and compare two tables

 

What I've tried so far:

 

-DB checkpoint - it would be perfect, but for what I understand, DB checkpoint connects to db and checks whether data saved on testcomplete side is the same as data from SQL query result. I don't know how to convince Testcomplete to save data from one query and compare it with data from another query. 

 

- saving SQL query results to separate files and comparing the files - works kinda good, but I don't have any ideas how to remove column ids from xmls (I don't even have them in my query, yet they are still there: I use ADO.SaveToFile method) apart from creating my own file, where I'd pick only things I need. I fear that would be inefficient though, and if I'm forced to write my own code, I may as well write my own comparison:

 

- comparing two result sets in my own code - it's simple, I can do whatever I want with it, but it isn't very efficient (e.g. it takes 3 mins to check 6k rows). Besides if there is any other option to do it using TC features, I'd rather stick with that instead of my code.

  • as per as I know we only have DBTables check and compare which will fetch the values from the database and will compare it with the saved data in TestComplete which as you said will not help you.

     

    You can export the sql query to txt file and compare those using TC or any file comparison tool. 

2 Replies

  • anupamchampati's avatar
    anupamchampati
    Frequent Contributor

    as per as I know we only have DBTables check and compare which will fetch the values from the database and will compare it with the saved data in TestComplete which as you said will not help you.

     

    You can export the sql query to txt file and compare those using TC or any file comparison tool. 

    • bach_ls's avatar
      bach_ls
      Occasional Contributor

      Thank you for your reply.

      In the end I had no other choice, than to do just that. It isn't perfect, but at least TestComplete's file comparison is much faster that my scripted way of comparing every line in ADO resultset... :)