After first update in oracle loop ends

[Arul Narayanan]

I am using a loop to update a table(Oracle) multiple times, but after the first update it exits the loop. If i print the statement, loop does not end. What am i missing?

[Knute Snortum]

Welcome to the Ranch!

Could you post the loop and use code tags. Thanks.

[Arul Narayanan]

package urlchecker;

import java.net.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class URLReader {


	public static void main(String[] args) throws Exception {

		URL url;
		URLConnection uc;
	    int i = 5000;
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.10:1521/xxxxx", "xxxxxx",
				"yyyyyyyyy");
		Statement stmt = con.createStatement();

		ResultSet rs = stmt.executeQuery("select empno from	emp where empno > 50");

		while (rs.next()) {	
      
			try{
			stmt.executeQuery("update emp set sal = "+ i +"where empno = "+rs.getInt(1) );
			i=i+1;
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			 }
		
			}
		}
	
	}

[Arul Narayanan]

CREATE TABLE EMP ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) ; Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (51,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (55,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (53,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (54,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (60,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (61,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (62,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (63,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (64,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (65,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (66,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (67,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (68,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (69,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (70,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (59,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (58,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (57,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (56,'Adam',null,null,null,null,null,null); Insert into ORANGELOGIX1.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (52,'Adam',null,null,null,null,null,null);

[Dave Tolls]

Do you get any errors?

You are re-using the statement object while looping over a result set that was created in the original statement.

Re-using the statement will kill the result set of the previous statement and should have resulted in an exception.

[Arul Narayanan]

Thanks a lot Guys. I solved by using PreparedStatement.

public class URLReader {


	public static void main(String[] args) throws Exception {

		URL url;
		URLConnection uc;
	    int i = 5000;
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.10:1521/xxxx", "yyyyy",
				"rrrrr");
		Statement stmt = con.createStatement();

		ResultSet rs = stmt.executeQuery("select empno from	emp where empno > 50");
        PreparedStatement preparedStmt = con.prepareStatement("update emp set sal = ? where empno = ?"); 
		while (rs.next()) {	
      
			try{
			//stmt.executeQuery("update emp set sal = "+ i +"where empno = "+rs.getInt(1) );
			preparedStmt.setInt(1, i);
			preparedStmt.setInt(2, rs.getInt(1));
			preparedStmt.executeUpdate();
			i=i+1;
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			 }
		
			}
		}
	
	}

[khadar valli]

Instead of using loops on the statement(Query), Its better if you can use batch update for this.

[Paul Clapham]

Even better if you just use a single update statement which applies to all of the affected rows:

update emp set sal = ? where empno > 50

[Knute Snortum]

I'm not sure how the OP can set sal = 5000 for the first emp > 50, then 5001 for the second emp > 50, and so on, with the SQL you are suggesting.

[Paul Clapham]

Okay, so I missed the "i = i + 1" bit there. Can't imagine why you would want to add sort of random amounts to people's salaries like that, but I've given up being surprised at the homework assignments that people describe here.