Compare result sets from different database

[kalyan chakravarthi kotamarthi]

Hi all,

I have two development databases
1.QA database
2.Dev database
I need to compare certain tables from the both databases and see the results, if anything is missing. is there any way to do this.

Thanks,
kalyan.

[Paul Clapham]

Yes, there is.

Did you have a particular question about that?

[kalyan chakravarthi kotamarthi]

Can you explain the solution?

[Paul Clapham]

It's what you said, basically. You read the tables from the two databases, compare the data you read in, and report on records which are missing. Did you have a specific question about that?

But I asked you that already. Let's try something else: what have you done so far?

[kalyan chakravarthi kotamarthi]

i can query the database but i want to compare the result sets dynamically with out storing the data in list.

[Jeanne Boyarsky]

Your choices are to export the two tables as csv and do a diff. Or to query both and loop through simultaneously so you aren't storing data.

[Paul Clapham]

Whenever I see the word "dynamically" I assume it to mean "In a complicated way which I don't understand". Nobody has ever contradicted me and provided a different meaning, either. So that's what I'm going to assume here.

What you do is this: Read the rows in sequence from the two databases and compare them. "In sequence" is important because if you read them in a random order it's going to be much more difficult. So they had better have keys. Here's the process:

(1) Read the first row from each database. (Call the databases X and Y.)

(2) If key X = key Y then compare the two rows and report differences; read the next row from both databases; repeat from (2).

(3) If key X < key Y then report "Key X missing from Y"; read the next row from X; repeat from (2).

(4) If key X > key Y then report "Key Y missing from X"; read the next row from Y; repeat from (2).

Actually this is a simplified version because (as you should have noticed) it doesn't tell you how to handle end-of-file in the two databases. But really, this is such an easy-to-understand process that you should be able to add on that little detail.

[Hebert Coelho]

You could do in a simple way like this:

select * from car order by name

After the select in both databases you could do:

boolean notEqual = false;
boolean isEqual = true;

if (databaseA.size() <> databaseB.size()){
    return notEqual;
}

for (int i = 0; i < databaseA.size(); i++){
    if(!databaseA.get(i).equals(databaseB.get(i))){
        return notEqual;
    }
}

return isEqual;

I know the code is not good, but it will give an idea! =D