aspose file tools*
The moose likes JDBC and the fly likes Newbie jdbc sql problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Newbie jdbc sql problem" Watch "Newbie jdbc sql problem" New topic
Author

Newbie jdbc sql problem

Mike Rosser
Greenhorn

Joined: Sep 15, 2008
Posts: 8
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 = "jdbcdbc:gc";
String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
String data1 = "jdbcdbc: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
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39409
    
  28
Welcome to JavaRanch

You are selecting details from database 1, from table b_pass, then trying to put the same data into database 2, which probably hasn't got a b_pass table. Try qualifying b_pass with the name of the database it belongs to. Or try retaining the data in the Java application until you are ready for the 2nd insert.
Mike Rosser
Greenhorn

Joined: Sep 15, 2008
Posts: 8
I've tried qualifying b_pass in every way imaginable, based on other threads I'd read here, but always with the same result.

Not sure how to go about "retaining" the info. Can you just point me in the right direction and I'll go dig up the details?

Thanks
Mike Rosser
Greenhorn

Joined: Sep 15, 2008
Posts: 8
OK - Did some research and think we're talking about ResultSet here, which could really shorten my second statement into an INSERT...SELECT...FROM. But I'm not sure how to reference the ResultSet rec1 in the sql statement. It didn't like it in the FROM clause.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39409
    
  28
Who needs a reference to the ResultSet in the SQL "insert" statement?

What you can do is extract the data from it, and put them into the insert. You might do that via a specialised class as an intermediate stage.
Mike Rosser
Greenhorn

Joined: Sep 15, 2008
Posts: 8
I understand the part about doing it as a separate class, but as a self-taught newbie, I'm not getting the rest of the suggestion. Could you possibly give me a couple sample lines of using Insert with variables? FYI, my code above only pulls 24 records, but the actual select and insert will apply to ~65,000 records.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39409
    
  28
A SELECT statement which you would execute from the Java code will return a ResultSet.
Create some class which has the same fields as the ResultSet.
Then use those fields to populate your subsequent INSERT statement.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Newbie jdbc sql problem