This code is intended to pull records from one SQL db and insert them into another, unrelated SQL db.
The first part of this, down to "Done Reading!" println, works fine. In the 2nd sql statement, I continually get a sql error that B_PASS is an invalid object name in the FROM statement, even though it worked fine in the first statement. Both db's are sql. All cap/lower case is correct. What am I missing? TIA for help.
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
import java.sql.*;
/**
*
* @author mr
*/
public class WriteToGC {
/**
* @param args the command line arguments
*/
public static void main(
String[] args) {
// TODO code application logic here
String data = "jdbc:odbc:gc";
String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
String data1 = "jdbc:odbc:B_PASS";
try {
Class.forName(driverName);
Connection conn1 = DriverManager.getConnection(data1,"me","mypw");
Statement st1 = conn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rec1 = st1.executeQuery(
"SELECT B_PASS.RLAST_NAME, B_PASS.RFIRST_NAME, " +
"B_PASS.REXPIRATION_DATE, MASTER_ACCOUNT.RADDRESS_1, " +
"MASTER_ACCOUNT.RADDRESS_2, MASTER_ACCOUNT.RCITY, " +
"MASTER_ACCOUNT.RSTATE, MASTER_ACCOUNT.RPOSTCODE, " +
"MASTER_ACCOUNT.RDAY_PHONE " +
"FROM B_PASS INNER JOIN MASTER_ACCOUNT " +
"ON B_PASS.RACCOUNT = MASTER_ACCOUNT.RACCOUNT " +
"WHERE B_PASS.RLAST_NAME LIKE 'Wai%' AND " +
"B_PASS.rexpiration_date>'2008-08-22'");
rec1.beforeFirst();
rec1.last();
int size = rec1.getRow();
System.out.println(size);
String lastname = rec1.getString("RLAST_NAME");
String firstname = rec1.getString("RFIRST_NAME");
String phone = rec1.getString("RDAY_PHONE");
rec1.beforeFirst();
while (rec1.next()) {
// for (int i=1;i<=size;i++) {
System.out.print(rec1.getString("RLAST_NAME") + "\t"
+rec1.getString("RFIRST_NAME") + "\t"
+rec1.getString("RDAY_PHONE"));
System.out.println();
// }
}
System.out.print(lastname);
System.out.println("Done reading");
// everything runs fine at this point; I see all the info it�s pulled from the B_PASS database and it�s correct.
// the connection is not closed
String update = "INSERT INTO gc.Guest (gst_last_name, gst_first_name, " +
"gst_phone) " +
"SELECT B_PASS.RLAST_NAME, B_PASS.RFIRST_NAME, " +
"MASTER_ACCOUNT.RDAY_PHONE " +
"FROM B_PASS INNER JOIN MASTER_ACCOUNT " + //this is where it throws the error that B_PASS is invalid object name
"ON B_PASS.RACCOUNT = MASTER_ACCOUNT.RACCOUNT " +
"WHERE B_PASS.RLAST_NAME LIKE 'Wai%' AND " +
"B_PASS.REXPIRATION_DATE>'2008-09-11'";
// opening connection to 2nd database
Connection conn = DriverManager.getConnection(data,"me","mypw");
Statement st = conn.createStatement();
st.executeUpdate(update);
System.out.println("Done writing");
st.close();
st1.close();
conn1.close();
conn.close();
//)
}catch (SQLException s) { //end try
System.out.println("SQL Error: " + s.toString() + " "
+ s.getErrorCode() + " " + s.getSQLState());
} //end catch
catch (ClassNotFoundException classNotFound)
{
classNotFound.printStackTrace();
} //end catch
} //end main
} //end class