Forum Discussion

Vec84's avatar
Vec84
Contributor
8 years ago

different Database table comparison

I have been attempting to database table comparison within testcomplete which is a fantastic feature however im finding it difficult to check tables from two seperate databases.

 

We have a Test environment,  Dev environment and Live environment. The tables are all named the same name except for them having test, dev or live in front of the table name to determine them. So when i go to do the database comparison it won' t allow two separate connections to two separate databases. We have worked around this by creating a standalone database and imported the tables and renamed them however this is a work around and not a complete solution.

 

EXAMPLE TABLE NAMES:

 

TEST_TABLE1

DEV_TABLE1

LIVE_TABLE1

 

I beleive in script this could be possible or could someone point me in the right direction as to other solutions

  • tristaanogre's avatar
    tristaanogre
    8 years ago

    To help me understand a bit... why are you executing table test comparisons between your different environments? If you're writing functional or integration tests for your AUT, I'd usually assume that the test environment is the environment for those operations.  Live environment I'd avoid because I wouldn't want to adversely affect either the functionality of the live environment or the customer experience (performance hits, etc).  Dev environment I'd also avoid because, by it's nature, the dev environment is under development and so the table structures, content, etc., aren't necessarily static and reliable.

     

    Now, if you're doing some other tests to ensure proper deployment, etc., that's a different story.

    One thing you could do is utilize a pair of DDT.ADODriver objects, one for each of the two enviroments you want to compare.  So... you'd end up with a while loop that you can then compare, row by row, the data in each side.  As just a POC of code (I wouldn't copy and paste and try to use):

    var testTable = DDT.ADODriver(testEnvironmentConnectionString, 'TEST_TABLE1');
var liveTable = DDT.ADODriver(liveEnvironmentConnectionString, 'LIVE_TABLE1');

while ((!testTable.EOF()) && (!liveTable.EOF())) {
    if (testTable.Value('Field1') != liveTable.Value('Field1') {
       Log.Warning('Field 1 values don't match')
    }
    testTable.Next();
    liveTable.Next();
}

DDT.CloseDriver(testTable.Name);
DDT.CloseDriver(liveTable.Name);

     

    Again... this is just a rough mockup... I'd actually put a for-loop around that if logic and iterate through the fields by index rather than by name and do the comparisons that way.

    You could also, potentially, use those DDT drivers to simply output the results of the table to CSV files and then do files.compare to compare the two outputs en masse rather than field by field...

    You could write custom SQL object code using the ADO objects to run SQL queries that dump their results to file rather than to record set and then do the files.compare.

    There are all SORTS of ways of doing this.... Hopefully, this gives you a starting point.

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    8 years ago

    To help me understand a bit... why are you executing table test comparisons between your different environments? If you're writing functional or integration tests for your AUT, I'd usually assume that the test environment is the environment for those operations.  Live environment I'd avoid because I wouldn't want to adversely affect either the functionality of the live environment or the customer experience (performance hits, etc).  Dev environment I'd also avoid because, by it's nature, the dev environment is under development and so the table structures, content, etc., aren't necessarily static and reliable.

     

    Now, if you're doing some other tests to ensure proper deployment, etc., that's a different story.

    One thing you could do is utilize a pair of DDT.ADODriver objects, one for each of the two enviroments you want to compare.  So... you'd end up with a while loop that you can then compare, row by row, the data in each side.  As just a POC of code (I wouldn't copy and paste and try to use):

    var testTable = DDT.ADODriver(testEnvironmentConnectionString, 'TEST_TABLE1');
var liveTable = DDT.ADODriver(liveEnvironmentConnectionString, 'LIVE_TABLE1');

while ((!testTable.EOF()) && (!liveTable.EOF())) {
    if (testTable.Value('Field1') != liveTable.Value('Field1') {
       Log.Warning('Field 1 values don't match')
    }
    testTable.Next();
    liveTable.Next();
}

DDT.CloseDriver(testTable.Name);
DDT.CloseDriver(liveTable.Name);

     

    Again... this is just a rough mockup... I'd actually put a for-loop around that if logic and iterate through the fields by index rather than by name and do the comparisons that way.

    You could also, potentially, use those DDT drivers to simply output the results of the table to CSV files and then do files.compare to compare the two outputs en masse rather than field by field...

    You could write custom SQL object code using the ADO objects to run SQL queries that dump their results to file rather than to record set and then do the files.compare.

    There are all SORTS of ways of doing this.... Hopefully, this gives you a starting point.

  • Vec84's avatar
    Vec84
    Contributor
    8 years ago
    Thank you, this is fantastic start and I will attempt to make a start on what you have mentioned.
    Yes, we are checking the built tables to confirm consistency between the environments. The Live database is the golden copy before it is built to our live environment. So we just do last checks before the builds go to live.

    Thanks Again