SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Brampton, Ontario
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    New to JSP/Java. Need help with this please.

    Hey guys,

    I know I'm not an avid poster because I'm usually browsing, but I was wondering if anyone can help me out with a little issue:

    I need to create a file that basically retrieves data from an Oracle DB, compares it to the data stored in a MySQL DB, and if the data already exists in the mySQL DB, then UPDATE it, if it doesn't, then INSERT it.

    This is to be made into a class file that a cron job will be set to run it once a day...

    anyway, my file "importIVR.java" seems to be fine (I'm new to this), and it compiles without errors. When I run the class importIVR.class, it appears to have worked correctly but I do not see any entries added or updated in the MySQL DB from the Oracle DB. I've viewed the Data in the Oracle DB to see if there is any and data does exist there.

    Can anyone with experience with java review my code and shed some light on this. maybe I have coded this wrong (seeing as i'm a novice at this)

    Thanks for your help!

    code (I've removed the db username and password from this code for obvious reasons):
    --------------------------------------------------------
    Code:
    import java.sql.*;
    import java.text.SimpleDateFormat;
    import java.text.StringCharacterIterator;
    import java.util.Calendar;
    import java.util.GregorianCalendar;
    import java.text.DateFormat;
    
    class importIVR {
    
    public static String translateResponses(String IVRresponse){
    
    	String writeresponse="D";
    	if (IVRresponse.equals("1")) { writeresponse="N";}
    	if (IVRresponse.equals("2")) { writeresponse="Y";}
    	return writeresponse;
      }
    
    
    public static void main (String args[]) throws SQLException {
    
    
    /* Production Connection :
    String MM_PSEXT_DRIVER = "oracle.jdbc.driver.OracleDriver";
    String MM_PSEXT_USERNAME = "******";
    String MM_PSEXT_PASSWORD = "******";
    String MM_PSEXT_STRING = "jdbc:oracle:thin:@bcdbs116.corp.ads:1521:VHRWFI";
    */
    
    /* Development Connection: */
    
    String MM_PSEXT_DRIVER = "oracle.jdbc.driver.OracleDriver";
    String MM_PSEXT_USERNAME = "*****";
    String MM_PSEXT_PASSWORD = "*****";
    String MM_PSEXT_STRING = "jdbc:oracle:thin:@bcdev114.corp.ads:1521:SUDEV";
    
    String MM_PSEXT_MySQL_DRIVER = "org.gjt.mm.mysql.Driver";
    String MM_PSEXT_MySQL_USERNAME = "rickster";
    String MM_PSEXT_MySQL_PASSWORD = "********";
    String MM_PSEXT_MySQL_STRING = "jdbc:mysql://fedora01q.tmi.telus.com/eccrs";
    
    
    try {Driver Driverresultset_sched = (Driver)Class.forName(MM_PSEXT_DRIVER).newInstance();}
     catch (IllegalAccessException e2) {}
     catch (InstantiationException e2) {}
     catch (ClassNotFoundException e) {}
    
    
    Connection Connresultset_sched = DriverManager.getConnection(MM_PSEXT_STRING,MM_PSEXT_USERNAME,MM_PSEXT_PASSWORD);
    PreparedStatement Statementresultset_sched = Connresultset_sched.prepareStatement("SELECT * FROM PSEXT.EMPLOYEE_EQUITY WHERE ENTERED > SYSDATE-1 AND ENTERED < SYSDATE  AND SOURCE='IVR' ORDER BY ENTERED DESC",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY );
    Statementresultset_sched.setFetchSize(100); 
    ResultSet resultset_sched = Statementresultset_sched.executeQuery();
    boolean resultset_sched_isEmpty = !resultset_sched.next();
    boolean resultset_sched_hasData = !resultset_sched_isEmpty;
    Object resultset_sched_data;
    int resultset_sched_numRows = 0;
    
    int Repeat1__numRows = -1;
    int Repeat1__index = 0;
    resultset_sched_numRows += Repeat1__numRows;
    
    try {Driver Driversql_command = (Driver)Class.forName(MM_PSEXT_MySQL_DRIVER).newInstance();}
     catch (IllegalAccessException e2) {}
     catch (InstantiationException e2) {}
     catch (ClassNotFoundException e) {}
    
    Connection Connsql_command = DriverManager.getConnection(MM_PSEXT_MySQL_STRING,MM_PSEXT_MySQL_USERNAME,MM_PSEXT_MySQL_PASSWORD);
    PreparedStatement StatementMySQL_query = Connsql_command.prepareStatement("SELECT *  FROM employee",ResultSet.TYPE_FORWARD_ONLY );
    StatementMySQL_query.setFetchSize(100); 
    ResultSet MySQL_query = StatementMySQL_query.executeQuery();
    boolean MySQL_query_isEmpty = !MySQL_query.next();
    boolean MySQL_query_hasData = !MySQL_query_isEmpty;
    Object MySQL_query_data;
    int MySQL_query_numRows = 0; 
    
    int Repeat1__numRows2 = -1;
    int Repeat1__index2 = 0;
    MySQL_query_numRows += Repeat1__numRows2;
    
    String emp_sk="", nom_date="", start_moment="", stop_moment="", code="",rank="",insertstatement="" ,updatestatement="",employee_no="";
    
    System.out.println("Loading new IVR entries");
    
     while ( (resultset_sched_hasData) && (Repeat1__numRows-- != 0)) { 
     
    insertstatement = "INSERT INTO employee_historical (employee_no, dte_created, Aboriginal, Visible_Minority, Disabled)  VALUES ('"+resultset_sched.getString("EMPNO")+"','"+resultset_sched.getObject("ENTERED").toString()+"','"+translateResponses(resultset_sched.getString("QUESTION1"))+"','"+translateResponses(resultset_sched.getString("QUESTION2"))+"','"+translateResponses(resultset_sched.getString("QUESTION3"))+"' ) ";
    StatementMySQL_query.addBatch(insertstatement);
    
    employee_no = MySQL_query.getString("employee_no");
    
    if (employee_no.equals(resultset_sched.getString("EMPNO"))) {
    	updatestatement = "UPDATE employee SET dte_modified=NULL,employee_no='"+resultset_sched.getString("EMPNO")+"',dte_created='"+resultset_sched.getObject("ENTERED").toString()+"',Aboriginal='"+translateResponses(resultset_sched.getString("QUESTION1"))+"',Visible_Minority='"+translateResponses(resultset_sched.getString("QUESTION2"))+"',Disabled='"+translateResponses(resultset_sched.getString("QUESTION3"))+"'";
    	StatementMySQL_query.addBatch(updatestatement);
    	}
    else {
    	insertstatement = "INSERT INTO employee (employee_no, dte_created, Aboriginal, Visible_Minority, Disabled)  VALUES ('"+resultset_sched.getString("EMPNO")+"','"+resultset_sched.getObject("ENTERED").toString()+"','"+translateResponses(resultset_sched.getString("QUESTION1"))+"','"+translateResponses(resultset_sched.getString("QUESTION2"))+"','"+translateResponses(resultset_sched.getString("QUESTION3"))+"' ) ";
    	StatementMySQL_query.execute(insertstatement);
    	}
    
    if (Repeat1__index%100 == 0) {
    	StatementMySQL_query.executeBatch();
    }
    
      Repeat1__index++;
      resultset_sched_hasData = resultset_sched.next();
      MySQL_query.next();
    }
    
    StatementMySQL_query.executeBatch(); // run the batched clean up job
    
    resultset_sched.close();
    Statementresultset_sched.close();
    Connresultset_sched.close();
    
    MySQL_query.close();
    StatementMySQL_query.close();
    Connsql_command.close();
    
    	}
    }
    --------------------------------------------------------

  2. #2
    SitePoint Member chellbelle's Avatar
    Join Date
    Jun 2006
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try putting some stack trace outputs in your catch statements. Yours are empty at the moment. Perhaps the driver you are using is not on your classpath?
    For example:

    try {Driver Driversql_command = (Driver)Class.forName(MM_PSEXT_MySQL_DRIVER).newInstance();}
    catch (IllegalAccessException e2) { e2.printStackTrace(); }
    catch (InstantiationException e2) { e2.printStackTrace(); }
    catch (ClassNotFoundException e) { e.printStackTrace(); }

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Brampton, Ontario
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestion chellbell, but that didn't really do anything. I added that to my code, created my class file. Ran the file. It seems to have worked fine. But then I checked the mySQL data for any new entries and it's still the same. Nothing was added. I don't get it because there is data in the Oracle DB.

    Can anyone help me out please?

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Brampton, Ontario
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ...

    I've figured out the problem, (incase anyone was curious).

    One of the DB's were passing a null value for a field that I never figured would have a null value ("employee # field).. and this was causing some problems. I redid the code from scratch and made sure it checked for any null values and skip any rows that had this.

    Anyway, thanks to those who tried to help me out. Much appreciated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •