Results 1 to 2 of 2
  1. #1
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Question oracle update statement within the resultset is not working.pls see code.

    Hi

    This code below is not updating the table with the date created from the ID obtained from the select clause.

    I have created 2 statement objects one for select query and one for update ...can some one help me with updating the table for every new date created using the ID.
    Any help is apprecaited.Thanks in advance.

    Java Code:
    try {
    			statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE );
    			statementIUD = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
    		} catch (SQLException e1) {
    			// TODO Auto-generated catch block
    			e1.printStackTrace();
    		}	
    		try {
    			ResultSet rs= statement.executeQuery("select schoolID from school where year = 2005 and county_id = 1");
    		
    			while(rs.next()){
    			    count = rs.getRow();
    			    ID = rs.getInt(1);	
    				System.out.println("the ID value is: "+ID);
    		
    				int x,y, z;
    				int second,third,fourth;
    				x = ID;
    				y=x/10000000;
    				int year= 2000+y;
    
    				//for the 2 nd digit from left.
    				z= x%10000000;
    				y= z/1000000;
    				second= y;
    		  
    				//for the 3 rd digit from left.
    				z= z%1000000;
    				y= z/100000;
    				third= y;
    		  
    				//for the 4 th digit from left.
    				z= z%100000;
    				y= z/10000;
    				fourth= y;
    		  
    				int julianday = second*100+third*10+fourth;
    //				System.out.println("the julian day is: "+julianday);
    				int completeVal = year*1000+julianday;
    //				System.out.println("the value of julian date is: "+completeVal);
    		  
    				String test = String.valueOf(completeVal);
    //				System.out.println("the string value of completeVal is: "+test);
    				Date d = LoadDateReceived.getDateFromJulian7(test);
    				System.out.println(d);
    				DateFormat df = new SimpleDateFormat("dd-MMM-yy");
    				String d1 = df.format(d);
    				System.out.println(d1);				
    				String Sql = ("UPDATE school set DATE_RECEIVED = '"+d1+"' where (year = 2005 and county_id = 1) and schoolID = "+ID);
    				System.out.println("the statement is: "+Sql);					
    //				Execute the update Statement
    				Count_updatedLevel = statementIUD.executeUpdate(Sql); 
    				System.out.println("the count is: "+Count_updatedLevel);			
    //				System.out.println(LoadDateReceived.getJulian7FromDate(d));
    			}//end of while loop
    			statement.close();
    			statementIUD.close();
    			rs.close();
    			System.out.println("the count of updated records are: "+Count_updatedLevel);
    			System.out.println("the count is: "+count);
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    }
    The results look like this:-
    10-May-07
    10-May-07
    11-May-07
    14-May-07
    14-May-07
    14-May-07
    14-May-07
    14-May-07
    14-May-07
    14-May-07
    14-May-07
    14-May-07
    15-May-07
    15-May-07
    15-May-07
    16-May-07
    16-May-07
    the count of updated records are: 1
    the count is: 219

    If u see , for every date ..one record should be updated , but only one record is updated for the first date .

    Any help is appreciated..how to perform all the updates for every date ?
    Last edited by renu; 05-16-2011 at 09:29 PM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    A few pointers first.
    This:
    Java Code:
    String Sql = ("UPDATE school set DATE_RECEIVED = '"+d1+"' where (year = 2005 and county_id = 1) and schoolID = "+ID);
    should be part of a PreparedStatement, so statementIUD should be declared as a PreparedStatement. Then you can assign the DATE (and school id) properly, and not as a String, assuming that DATE_RECEIVED is correctly declared as a DATE in your db of course. In addition you can use addBatch() instead of executeUpdate and, after the loop has finished, you can then executeBatch() to execute all the updates in one go. This is the usual way of handling this sort of thing.

    Also your resources (the two statements and the result set) should be closed in a finally block. If this code were to throw an exception these resources would not get closed, so you could get a resource leak.

    Right, that lot aside, what do you mean by "perform all the updates for all the records"?

    You've not shown us what you are expecting to see compared with what you are seeing.

    That code (looking at it briefly) seems to loop over your result set and do the update you want, setting the DATE_RECEIVED to whatever it is you've calculated.

Similar Threads

  1. Replies: 2
    Last Post: 07-28-2010, 05:14 PM
  2. Replies: 4
    Last Post: 06-27-2010, 07:03 PM
  3. Update statement not working.
    By OMFGITSROHIT in forum JDBC
    Replies: 5
    Last Post: 04-08-2010, 02:03 PM
  4. Replies: 2
    Last Post: 07-18-2008, 02:13 PM
  5. Oracle Resultset slow
    By Peter in forum JDBC
    Replies: 2
    Last Post: 07-04-2007, 02:56 PM

Posting Permissions

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