Thread: updating database

import java.sql.*;
 
public class database
{Connection con;
 int count;  
 number_sequence nsq;
 public database() throws ClassNotFoundException
 {nsq=new number_sequence();
  try
  {Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  con=DriverManager.getConnection("jdbc:odbc:customerBase");
  Statement st=con.createStatement();
  ResultSet res = st.executeQuery("SELECT COUNT(*) FROM customerbase");
  while (res.next())
  {count = res.getInt(1);
  }
  System.out.println("Number of Records"+count);
 
  }catch(SQLException e)
  {System.out.println(e);
  }
  catch(Exception e)
  {System.out.println(e.getMessage());
  }
 }
 
 
public void add_to_database(customer c)
{try
 {PreparedStatement ps=con.prepareStatement("insert into customerbase values(?,?,?)");
  int random=nsq.integer_generator_upto(10000);
  c.set_acc_no(random);
  ps.setInt(1,random );
  ps.setString(2, c.get_name());
  ps.setInt(3,c.get_no_of_rentals());
  int x=ps.executeUpdate();
  System.out.println(x);
  System.out.println("ID generated for customer is "+random);
 }
 catch(SQLException e)
 {System.out.println(e);
  System.out.println('*');
 }
 catch(Exception e)
 {System.out.println(e.getMessage());
 }
 }
 
public void copy_database_into(LinkedList<customer> list) throws SQLException
{customer temp;
 try
 {PreparedStatement ps=con.prepareStatement("select * from customerbase");
 ResultSet rs=ps.executeQuery();
 while(rs.next())
 {temp=new customer(rs.getInt("ID"),rs.getString("cust_name"),rs.getInt         ("no_of_rentals"));
  list.add(temp);
 }
 }
 catch(SQLException e)
 {System.out.println(e);
  System.out.println("this is an SQL error");
 }
 catch(Exception e)
 {System.out.println(e.getMessage());
 }
 System.out.println("database has been successfully copied to list");
}
 
public void update_rentals_for(int id,int by_)
{
 try
 {PreparedStatement ps=con.prepareStatement("select no_of_rentals from customerbase where ID=id");
  ResultSet rs=ps.executeQuery();
  rs.next();
  int rentals=rs.getInt("no_of_rentals");
  System.out.println("no of rentals is "+rentals);
  System.out.println("the rentals is to be updated by "+by_);
  rentals+=by_;
  PreparedStatement ps2=con.prepareStatement("update customerbase set no_of_rentals=? where ID=?");
  ps2.setInt(1, rentals);
  ps2.setInt(2,id);
  int r=ps2.executeUpdate();
  System.out.println("updated "+rentals);
  }
 catch(SQLException e)
 {System.out.println(e);
 }
 catch(Exception e)
 {}
}

The output is

run:
Number of Records23
1
ID generated for customer is 3070
BUILD SUCCESSFUL (total time: 10 seconds)



This means there is no error in constructor....

as it gives the no of records in the database...

the update statement returns 1

means there was no database access error..etc.

no SQLException or general exception was thrown....

n the last line has been displayed

means the control passed through all the statements...

bt the problem is no update gets done in either of the function....

Of the 23 records that it mentioned,the function worked fine initilly bt then even though i didn't changed the code...it literally stopped responding....

How come this happen without any error?

plz help i am stuck up............

Hi gurpreetm13,

Try giving a shot to the program i gave you below. Its your program only. But i did make some changes in it. See carefully i have added three lines:

st.close();
con.commit();
con.close();
 
public class database
{
    Connection con;
    int count; 
    number_sequence nsq;
    public database() throws ClassNotFoundException
   {
       nsq=new number_sequence();
       try
      {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con=DriverManager.getConnection("jdbc:odbc:custome rBase");
        Statement st=con.createStatement();
        ResultSet res = st.executeQuery("SELECT COUNT FROM customerbase");
        while (res.next())
        {
          count = res.getInt(1);
         }
         System.out.println("Number of Records"+count);
 
         st.close();
         con.commit();
         con.close();
     }
     catch(SQLException e)
     {
       System.out.println(e);
      }
      catch(Exception e)
     {
       System.out.println(e.getMessage());
      }
}

the database doesnot gets updated untill you commit it.

Regards,
guptapr

You should actually put that in a try finally block I'd say because if an exception is thrown before you reach the call to the close method the connection will still stay open.

// Json

Thanks so much...u were right buddy..
thank u so much...