Solved

selecting cross - database servers in java

Posted on 2006-10-24
8
286 Views
Last Modified: 2006-11-28

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
0
Comment
Question by:dalecon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 86

Accepted Solution

by:
CEHJ earned 34 total points
ID: 17794728
Just use two queries and join them 'manually'
0
 

Author Comment

by:dalecon
ID: 17794793
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 17794808
Get the key field from the first and use it to lookup in the 2nd recordset
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 92

Assisted Solution

by:objects
objects earned 33 total points
ID: 17794884
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
 

Author Comment

by:dalecon
ID: 17794893
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
 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 33 total points
ID: 17795421
   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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question