• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Newbie jdbc sql problem

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Marshal
Posts: 79180
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 79180
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 79180
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic