selecting cross - database servers in java


hi all

can anyone show me a way around my problem?

i have 2 database servers, postgresql and mysql

i need to do a select query on the postgresql database and use the recordset set returned to do another select on a mysql database

something like this

select id from postgresqltable inner join mysql table

is this possibel.  i able to connect using jdbc to both database but i don't know how to use the select result from the postgresql table and join them with the mysql table

any help greatly appreciated
daleconAsked:
Who is Participating?
 
CEHJCommented:
Just use two queries and join them 'manually'
0
 
daleconAuthor Commented:
if i use this


// go off to postgresql
conn = Connection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, rsPS.TYPE_SCROLL_INSENSITIVE, rsPS.CONCUR_READ_ONLY);
rsPS = ps.executeQuery();

// go off to mysql
conn = ConnectionMySQL.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, rsMS.TYPE_SCROLL_INSENSITIVE, rsMS.CONCUR_READ_ONLY);
rsMS = ps.executeQuery();


How do 'join' the 2 recordset?
0
 
CEHJCommented:
Get the key field from the first and use it to lookup in the 2nd recordset
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
objectsCommented:
loop thru the first result set, and for each record make a select on the other database by the the foreign key

conn = Connection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, rsPS.TYPE_SCROLL_INSENSITIVE, rsPS.CONCUR_READ_ONLY);
rsPS = ps.executeQuery();
while (rsPS.next()) {

   Object key = rsPS.getObject(...);
   String sql = ...

   // go off to mysql
   conn = ConnectionMySQL.getConnection();
   PreparedStatement ps = conn.prepareStatement(sql, rsMS.TYPE_SCROLL_INSENSITIVE, rsMS.CONCUR_READ_ONLY);
   rsMS = ps.executeQuery();

   // loop thru rsMS
}
0
 
daleconAuthor Commented:
no the query is too complex it's full of left outer, inner and union joins. with a massive where clause using an old version of mysql so i can do any sub queries

i also need the results as a recordset and send as xml using jdom

i'll have to copy the data in a sql job from one server to the other and do it that way

0
 
Tomas Helgi JohannssonCommented:
   Hi!

You should use this method as CEHJ says : http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/JoinRowSet.html
Example (taken from the above link) :

    JoinRowSet jrs = new JoinRowSetImpl();
 
     ResultSet rs1 = stmt.executeQuery("SELECT * FROM EMPLOYEES");
     CachedRowSet empl = new CachedRowSetImpl();
     empl.populate(rs1);
     empl.setMatchColumn(1);
     jrs.addRowSet(empl);
 
     ResultSet rs2 = stmt.executeQuery("SELECT * FROM ESSP_BONUS_PLAN");
     CachedRowSet bonus = new CachedRowSetImpl();
     bonus.populate(rs2);
     bonus.setMatchColumn(1); // EMP_ID is the first column
     jrs.addRowSet(bonus);

Regards,
  Tomas Helgi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.