Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Compare result sets from different database

 
kalyan chakravarthi kotamarthi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, there is.

Did you have a particular question about that?
 
kalyan chakravarthi kotamarthi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you explain the solution?
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i can query the database but i want to compare the result sets dynamically with out storing the data in list.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 754
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could do in a simple way like this:



After the select in both databases you could do:


I know the code is not good, but it will give an idea! =D
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic