Problem with accessing result set with postgresql

Hi friends,

I have successfully installed the JDBC driver for PostgreSQL DB. I have checked by connecting woth DB. It is running successfully. Now I have two tables namely TABLE1 and TABLE2. TABLE1 has columns as IP and count. TABLE2 has IP only as column. Now i want to insert each IP values from TABLE1 into TABLE2 with its maximum count value since TABLE1 will have same IP with different count values.

I have written the code for that.

Code java:

---

 
Class.forName("org.postgresql.Driver"); //load the driver
db = DriverManager.getConnection("jdbc:postgresql:"+database,username,password);
sql=db.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); 
ResultSet ip_list = sql.executeQuery("select ip,count from TABLE1 where count in(select max(count) from TABLE1 group by src_ip);");
 
    while(ip_list.next())
    {
	src_ip=ip_list.getString("src_ip");
	count=ip_list.getInt("count");
 
	sqlText = "insert into TABLE2 values('" + src_ip + "'," + hop_count + ")";
	//System.out.println(sqlText);
	sql.executeUpdate(sqlText);
	//System.out.println(src_ip+"  "+hop_count);
	}

---

It gives the following errror "Exception in thread "main" org.postgresql.util.PSQLException: This ResultSet is closed"
The result set ip_list has three rows, but only one row is inserted in TABLE2 and then the above error has been thrown. When i comment the sql.executeUpdate() statement and uncomment the printing statement all the 3 row values get printed.

Where is the problem? What mistake i have made? Please clarify this.

With regards,
Raja Pandi

A close read of the API for Statement reveals the following line "All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists." So the execute in the loop should be performed with a different Statement than the one it loops over

Ya, I understood and solved my problem using two separate 'statements'. Thanks for help brother. :-)