ResultSet merge
Does anyone know if there is a way of creating one result set from two?
I need to write a query to return results from two databases that can not talk to each other (!). The results are only seen after some java fantasticness has turned them into an excel spreadsheet using POI. I'm using a class I wrote that takes the ResultSet data and turns this into an excel spreadsheet. As I already have this class, I'd rather use it than write something new.
Basically however I look at it I get two ResultSet objects. I have three options - either find a way to merge the ResultSets (which I like the sound of, but can find no easy method to do this), use one of the ResultSets to write the sheet (leaving gaps) and fill in the gaps with the data from the other ResultSet, or write lots of new stuff that does this in another way.
Incidentally, whilst looking through the API (java 1.3, so depressing to be so backwards!) I cannot find an implementing class of the ResultSet interface. Obviously one must exist... but where is it and what is it called (as while I'm happy to consider extending this as an option, implementing all the methods of the interface sounds like a time-consuming struggle and a tremendous bore to boot!)?
[1251 byte] By [
dallwooda] at [2007-11-27 6:20:56]
# 1
You can take the first ResultSet and create an array and create a second array with the second ResultSet and then combine the two arrays.The implementations of the ResultSet interface are found inside the JDBC Driver packages of the particular implementation you are using.
# 2
What flexibility does this method have?
Will it allow me to use a ResultSet that has columns A-G, and insert from a second ResultSet (with two columns, Y and Z) such that the combined ResultSet is ordered ABYCDEFZG for example?
Any chance of some quick example code?
To be honest if this method does not have the flexibility I'm looking for I think I'll just create a VO with all the relevant data to be fed to my old POI code and add a new method to that such that it takes a HashMap full of these VO's and does the relevant stuff from that instead. It's a long way around but it will be flexible enough and its easy (mondays are never good to the brain).
Message was edited by:
dallwood
# 3
>
> To be honest if this method does not have the
> flexibility I'm looking for I think I'll just create
> a VO with all the relevant data to be fed to my old
> POI code and add a new method to that such that it
> takes a HashMap full of these VO's and does the
> relevant stuff from that instead.
You should do this.
There is no method like what you are looking for in ResultSet or any other part of the JDBC API.
# 4
Of course it is significantly more efficient to just do the sql that returns the correct data in the first place.
# 5
> Of course it is significantly more efficient to just> do the sql that returns the correct data in the first> place.When that is possible that is true.However "I need to write a query to return results from two databases that can not talk to each other
# 6
> When that is possible that is true.> > However "I need to write a query to return results> from two databases that can not talk to each other "Ah yes....
# 7
> > When that is possible that is true.
> >
> > However "I need to write a query to return results
> > from two databases that can not talk to each other
> "
>
> Ah yes....
So, you can be a superstar and write join logic to bring together two data sources through the Miracle of Java (and we all like to to write something that gives us job security), or you can do the mundane and connect the two data sources together and then use standard SQL and significalty simpler Java / JDBC code.
Why is it impossible to connect the two databases?
Message was edited by:
WorkForFood
# 8
The dbs are of two different types (Sybase and Oracle) and are both huge.
Also the db team is based mainly in Pune, India, so while they are very good at what they do, we are seperated by their general belief that they understand what I'm saying. As we're miles from them (London) I can't tell them what I want them to do with any degree of confidence in their accuracy at following my instructions. Basically I'm preempting human errors, and also have no idea how you would start connecting the two different dbs (If I cared about that sort of thing, I'd be a DBA).
I don't know hwat your experience of dealing with dedicated DBA's is, but mine is that if I have to rewrite this part of the program (so far a month and a half has been spent on this part), it will still be quicker than getting any major change done to an existing and robust db, let alone two. The amount of other systems that would have to be altered (albiet in a minor way) would be significant too. Of course, not being a DBa I'm probably missing a very quick easy way of effecting this change without these problems, but I generally find it easier to stick to what I know.
# 9
Well, I cannot give any recommendations on what to do with the difficulties of working with an International DBA group.I will say that it is often practical to setup a database link between Oracle and Sybase to allow heterogeneous joins but in your situation that may not matter. If you are curious about using a database link for a solution you can read up on Oracle Heterogeneous services.
Good Luck.
# 10
Thanks for the tip - it sounds painful but I'll give it a look.
