This discussion is archived
3 Replies Latest reply: Feb 19, 2008 6:12 AM by 800308 RSS

synchronizing Access tables with SQL

807601 Newbie
Currently Being Moderated
Hello, I am having some troubles when I try to update the tables ProductsShop and ProductsOnline, they are for a program that synchronizes the products sold on the internet with the ones sold in the shop.

The tables are made with the Access.

However the products are updated randomly. I use the loop while (resultSet.next()). It seems that somthing is wrong in this path.

I'd be very thankful for your help.

The code is:


import java.sql.*;

public class Synchronizer{
     
     private Connection connectionLocal;
     private Connection connectionOnline;
     
public Synchronizer( Connection c, Connection d )
{
connectionLocal = c;
connectionOnline = d;

}

public void DoSync()
{
     
try
{
     
Statement statementLocal = connectionLocal.createStatement();
Statement statementLocal2 = connectionLocal.createStatement();

Statement statementOnline = connectionOnline.createStatement();

     String query = "SELECT codProduct, amount, synchronized FROM ProductsShop WHERE synchronized = false";

System.out.println("\nSending query: " + connectionLocal.nativeSQL( query ) + "\n" );

ResultSet resultSet = statementLocal.executeQuery(query);
     
               int codPro;
               int amo;
               while(resultSet.next()){
                    codPro= resultSet.getInt("codProduct");
                    amo = resultSet.getInt("amount");
                    
     query = "UPDATE ProductsOnline SET amount1= " + amo +
               " WHERE codProduct1=" + codPro;
     statementOnline.executeUpdate( query );
     
     System.out.println("amount updated " + query);
     
     query = "UPDATE ProductsShop SET synchronized= true"+
               " WHERE codProduct = " +codPro;
     statementLocal2.executeUpdate(query);
     
System.out.println("Product: "+codPro+" synchronized");

}

}
catch ( SQLException sqlex )
{
sqlex.printStackTrace();
System.err.println(sqlex.toString());
}
}
}

-------------------------------------------

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.*;

public class SychronizerTest {

     public static void main(String[] args) {
          String urlLocal = "jdbc:odbc:LocalShop";
          String urlOnline = "jdbc:odbc:OnlineShop";
          
          try{
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
               
               Connection connectionLocal = DriverManager.getConnection(urlLocal, "", "");
               Connection conncetionOnline = DriverManager.getConnection(urlOnline, "", "");
               
               Synchronizer sync = new Synchronizer(connectionLocal, conncetionOnline);
               sync.DoSync();
          }
          catch(ClassNotFoundException cnfex){
               System.err.println("Failed to load JDBC/ODBC driver.");
               cnfex.printStackTrace();
               System.exit(1);
          }
          catch(SQLException sqlex){
               System.err.println("Unable to connect");
               sqlex.printStackTrace();
          }
     }
}
  • 1. Re: synchronizing Access tables with SQL
    mlk Newbie
    Currently Being Moderated
    1) Use code tags.
    2) Use PreparedStatements
    3) "It seems that somthing is wrong in this path." What is wrong? What is it doing, what should it be doing?
  • 2. Re: synchronizing Access tables with SQL
    800308 Newbie
    Currently Being Moderated
    Dude, repost your code between some [code] code tags [/code].
  • 3. Re: synchronizing Access tables with SQL
    807601 Newbie
    Currently Being Moderated
    Here the connection is stablished and the synchronizer class is called:
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.*;
    
    public class SychronizerTest {
    
         /**
          * @param args
          */
         public static void main(String[] args) {
              String urlLocal = "jdbc:odbc:LocalShop";
              String urlOnline = "jdbc:odbc:OnlineShop";
              
              try{
                   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                   
                   Connection connectionLocal = DriverManager.getConnection(urlLocal, "", "");
                   Connection conncetionOnline = DriverManager.getConnection(urlOnline, "", "");
                   
                   Synchronizer sync = new Synchronizer(connectionLocal, conncetionOnline);
                   sync.DoSync();
              }
              catch(ClassNotFoundException cnfex){
                   System.err.println("Failed to load JDBC/ODBC driver.");
                   cnfex.printStackTrace();
                   System.exit(1);
              }
              catch(SQLException sqlex){
                   System.err.println("Unable to connect");
                   sqlex.printStackTrace();
              }
    
         }
    
    }
    The folowing class should read a table with products from the shop database properly and then update a table with products on the internet shop.
    import java.sql.*;
    
    public class Synchronizer{
         
         private Connection connectionLocal;
         private Connection connectionOnline;
         
        public Synchronizer( Connection c, Connection d )
        {
            connectionLocal = c;
            connectionOnline = d;
           
        }
    
        public void DoSync()
        {
             
            try 
            {
                 
                Statement statementLocal = connectionLocal.createStatement();
                Statement statementLocal2 = connectionLocal.createStatement();
    
                Statement statementOnline = connectionOnline.createStatement();
                
                     String query = "SELECT codProduct, amount, synchronized FROM ProductsShop WHERE synchronized = false";
    
                    System.out.println("\nSending query: " + connectionLocal.nativeSQL( query ) + "\n" );
                    
                    ResultSet resultSet = statementLocal.executeQuery(query);
             
                       int codProduct;
                       int amount;
    
                                                    /* here the tables should be updated, 
    but it is done randomly, some rows are updated and others not. 
    It should update all of them.
     In the ProductsOnline's table the amount should be updated 
    and in the ProductsOnline's table the checkbox must be updated, it means, it must become true.*/
    
                       while(resultSet.next()){
                            codProduct= resultSet.getInt("codProduct");
                            amount =  resultSet.getInt("amount");
                            
                         query = "UPDATE ProductsOnline SET amount= " + amount +
                                   " WHERE codProduct=" + codProduct;
                         
                         statementOnline.executeUpdate( query );
                         
                         System.out.println("amount updated " + query);
                         
                         query = "UPDATE ProductsShop SET synchronized= true"+ 
                                   " WHERE codProduct = " +codProduct;
                         statementLocal2.executeUpdate(query);
                     
                       System.out.println("Product: "+codProduct+" synchronized");
                       
                   }
            
            }
            catch ( SQLException sqlex ) 
            {
                sqlex.printStackTrace();
              System.err.println(sqlex.toString());
            }
        }
    }
    Thank you very much for your help mates.