3 Replies Latest reply on Feb 20, 2008 12:32 PM by 807601

    synchronizing Access tables with SQL

    807601
      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
          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
            Dude, repost your code between some [code] code tags [/code].
            • 3. Re: synchronizing Access tables with SQL
              807601
              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.