JAVA SQL Joining 2 tables from 2 different connections with one single statement

[michelle hassil]

Please help me URGENT!!!

I would like to know how to conenct two different tables from 2 different connections using JDBC ODBC:
Normally what we do is
Connection conn = Driver Manger.getConnection(..jdbc odbc..)
and
Statement st =conn.execute query

But now i need to pass
connection conn2 =Driver.Manager.Getconnection(.jdbc odbc..)
conn2 also to statement st
Since i need to merge these two conn1 and conn2 .How DO I DO THIS?
My query will be something like select.....from table 1 (conn1),table 2(conn2) where.....
but i dont know how to pass these two objects to same statement and execute it once with same object.

Thanks in advance

[Rob Spoor]

michelle hassil wrote:Please help me URGENT!!!

Please EaseUp. There is no such thing as urgent around here.

I would like to know how to conenct two different tables from 2 different connections using JDBC ODBC:
Normally what we do is
Connection conn = Driver Manger.getConnection(..jdbc odbc..)
and
Statement st =conn.execute query

But now i need to pass
connection conn2 =Driver.Manager.Getconnection(.jdbc odbc..)
conn2 also to statement st
Since i need to merge these two conn1 and conn2 .How DO I DO THIS?
My query will be something like select.....from table 1 (conn1),table 2(conn2) where.....
but i dont know how to pass these two objects to same statement and execute it once with same object.

Thanks in advance

You can't do this with two different connections. However, you may be able to do this slightly differently, if both databases are on the same database server*. You use one single connection, and use the fully qualified table name from the other database. For example, in MS SQL Server:
-- connected to the database where table1 is in ... FROM table1 INNER JOIN MyOtherDatabase..table2 table2 ON ... ...
* In MS SQL Server, you can "import" a database from another MS SQL Server machine by using linked servers. The full database name then becomes LINKED_SERVER_NAME.DATABASE_NAME.SCHEMA_NAME.TABLE_NAME. For example, MyOtherServer.MyDatabase.dbo.MyTable. You won't be able to leave out the schema name as in my earlier example. Other database servers like Oracle and MySQL may have similar techniques.

[michelle hassil]

I am doing it using JDBC ODBC Bridge driver.
THIS IS MY CODE

import java.sql.*;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class retrieveexcel1 {

	public static void connectToDatabase() throws SQLException, InstantiationException, IllegalAccessException, IOException 
	{
		// Attempt to load database driver
		try
		{
			// Load Sun's jdbc-odbc driver
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
			System.out.println("loaded");
		}
		catch (ClassNotFoundException cnfe) // driver not found
		{
			System.err.println ("Unable to load database driver");
			System.err.println ("Details : " + cnfe);
			System.exit(0);
		}
	}
	public static Connection setconnection() throws SQLException
	{
		Connection conn1 = DriverManager.getConnection("jdbc:odbc:trialchr1mergedhg18andhg19","","");
		return conn1;
	}
	public static Connection getconnection() throws SQLException
	{
		return setconnection();
	}
	public static Connection setconnection2() throws SQLException
	{
		Connection conn2 = DriverManager.getConnection("jdbc:odbc:chr1tobemapped","","");
		return conn2;
	}
	public static Connection getconnection2() throws SQLException
	{
		return setconnection2();
	}
	//return conn1;

	//}
	public static  void retrieve( Connection c,Connection c1) throws SQLException, IOException {
		Connection conn1=c;
		Connection conn2= c1;

MY PROBLEM STARTS HERE,HOW TO GIVE THESE TWO CONNECTIONS INTO SHEET1 AND SHEET 2 A,HOW TO GIVE THIS OBJECT CONN1 AND CONN2 TO STATEMENT.COULD YOU PLEASE HELP ME CORRECT THE CODE A LITTLE PLEASEEE

		String query="Select chrom,hg18strt,hg18end,hg19strt,hg19end from [sheet1$],[sheet2$]"
		+"where hg18strt='491'";

		Statement st = (Statement) conn1.createStatement();
	
	
		ResultSet rs=((java.sql.Statement) st).executeQuery(query);
		System.out.println("done");
		String home=System.getProperty("user.dir")+"/";
		System.out.println(home);
		File f = new File(home+"merged.txt");
		System.out.println("created file");
		FileWriter fstream = new FileWriter(f);
		BufferedWriter out = new BufferedWriter (fstream);

	
		while(rs.next()){
			out.write(rs.getString("hg18strt"));
			out.newLine();
		}

		out.close();
		rs.close();
		st.close();
	}


	public static void closeConnection(java.sql.Connection c,java.sql.Connection c1) {
		try {
			c.close();
			c1.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws SQLException, IOException, InstantiationException, IllegalAccessException {
		retrieveexcel1 re = new retrieveexcel1();
		re.connectToDatabase();
		java.sql.Connection c =getconnection();
		java.sql.Connection c1 =getconnection2();
		System.out.println("connecting");
		retrieve(c,c1);
		closeConnection(c,c1);
		System.out.println("connected");
	}

}

Thanks in advance

[michelle hassil]

ALSO I WOULD LIKE TO MENTION THAT i am quite new to java,its been only a few months that i started coding(2-3 months). So please tell me if theres somethings i should refer and learn before i do this if its a lot.

[Rob Spoor]

I already told you, you cannot join tables from two different connections. Believe me, I tried once. I got some nasty SQL errors.

Also, could you please UseCodeTags? Thanks. Also, please KeepItDown. There's no need for all-uppercase in posts.

[michelle hassil]

import java.sql.*;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class retrieveexcel1 {
//connecting to database using driver jdbc odbc
public static void connectToDatabase() throws SQLException, InstantiationException, IllegalAccessException, IOException
{
// Attempt to load database driver
try
{
// Load Sun's jdbc-odbc driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
System.out.println("loaded");
}
catch (ClassNotFoundException cnfe) // driver not found
{
System.err.println ("Unable to load database driver");
System.err.println ("Details : " + cnfe);
System.exit(0);
}
}
//setting up and getting connection to excel sheets
public static Connection setconnection() throws SQLException
{
Connection conn1 = DriverManager.getConnection("jdbc:odbc:trialchr1mergedhg18andhg19","","");
return conn1;
}
public static Connection getconnection() throws SQLException
{
return setconnection();
}
public static Connection setconnection2() throws SQLException
{
Connection conn2 = DriverManager.getConnection("jdbc:odbc:chr1tobemapped","","");
return conn2;
}
public static Connection getconnection2() throws SQLException
{
return setconnection2();
}
//return conn1;

//}
//querying and writing result set to a new file
public static void retrieve( Connection c,Connection c1) throws SQLException, IOException {
Connection conn1=c;
Connection conn2= c1;
String query="Select chrom,hg18strt,hg18end,hg19strt,hg19end from [sheet1$],[sheet2$]"
+"where hg18strt='491'";

Statement st = (Statement) conn1.createStatement();


ResultSet rs=((java.sql.Statement) st).executeQuery(query);
System.out.println("done");
String home=System.getProperty("user.dir")+"/";
System.out.println(home);
File f = new File(home+"merged.txt");
System.out.println("created file");
FileWriter fstream = new FileWriter(f);
BufferedWriter out = new BufferedWriter (fstream);


while(rs.next()){
out.write(rs.getString("hg18strt"));
out.newLine();
}

out.close();
rs.close();
st.close();
}

//closing both connecton
public static void closeConnection(java.sql.Connection c,java.sql.Connection c1) {
try {
c.close();
c1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//main class
public static void main(String[] args) throws SQLException, IOException, InstantiationException, IllegalAccessException {
retrieveexcel1 re = new retrieveexcel1();
re.connectToDatabase();
java.sql.Connection c =getconnection();
java.sql.Connection c1 =getconnection2();
System.out.println("connecting");
retrieve(c,c1);
closeConnection(c,c1);
System.out.println("connected");
}

}
DEar Rob,
I understood what you tried telling me that i cant do this way but i really don't get it how to do it.Can you explain me step by step starting with my 2 excel files. as these files some of it was experimentally generated and not just from database. I did'nt get the part of mysql server because here i did not try connecting it to mysql server

[Wendy L Gibbons]

What you will have to do is
1, run a select against 1 connection, The select will be only based upon the tables on that database.
2, get a row from the result set
3, use the results from the first select in the select using the second connection.

here is psuedo code

connection1 = getConnectionToDatabase1();
connection2 = getConnectionToDatabase2();

Statement st1 = connect1.getStatement("select from 1 table on 1 database");
resultSet1 = st1.select();

while(rs1.hasNext(){
    Statement st2 = connection2.getStatement("selelct from 1 table on database 2, where column = value from rs1");
    ResultSet2 = st2.select();
    while(ResultSet2.hasNext(){
           // use the results
    }
}

[michelle hassil]

Thank you so much Rob.i am working on code.

I also have another doubt, when we use excel files using JDBC ODBC bridge driver , and query it should we call the table as sheet1$??? because when i tried using the name of my excel sheet it does not work gives some errors.and i am confused since now i have 2 excel files , can i call it sheet2$ and sheet1$ , or by names .

Please help

[michelle hassil]

Is there something called HasNext???
When i give resultset object.hasnext it is asking me to change to next !!!
also after getting to connection while giving connect1.statement(query), it is asking me to cast statement , change type to cast. I dont understand this.

public static void retrieve() throws sqlexception,iOEXCEPTION
{
connection conn1 =getconnection() //i already have a method of set n get where get method has a return statement n it will get connection
connection conn1 =getconnection2()
Statement st1=conn1.getStatement(""my query"")//but over here it asked me to cast to prepare statement n i avoided errors thpough i dont get it why.
Resultset rs1=st1.select() //unavoidable errror , it says to cast st1/select is undefined statement for statement
and then rs1.hasnext...it says change to next
}

[Martin Vashko]

Please read Oracle's JDBC tutorial. You don't need to read it in a specific order, there is a section on ResultSets, for example, but in the end, you should read all of it if you want to use JDBC.

[Rob Spoor]

I wouldn't use JDBC to read an Excel file. I'd use a proper Excel reading library like Apache POI or JExcelAPI.