ResultSet with 4162 rows only iterates on the first line

Question

ResultSet videoFilenames should have 4162 lines (I tested the query in the database), but while while while (videoFilenames.next ()) only executes once - after the first file name in the ResultSet, the while loop ends! Can someone help me understand why?

/* * Run: java -cp .:ojdbc6.jar FindVideosWithoutTranscodes */ import java.io.*; import java.sql.*; public class FindVideosWithoutTranscodes { public static void main(String[] args) throws Exception { //connect to database Class.forName("oracle.jdbc.OracleDriver"); String serverName = "***.***.***.***"; String portNumber = "****"; String sid = "*****"; String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid; String username = "*****"; String password = "*****"; Connection conn = DriverManager.getConnection(url, username, password); Statement stmt = conn.createStatement(); //create output file BufferedWriter out = new BufferedWriter(new FileWriter("mp2_files_without_transcodes.txt")); //get ResultSet of mp2 filenames ResultSet videoFilenames = stmt.executeQuery("select filename from archivefile where filename like '%.mp2'"); System.out.println("Making list of mp2 files without transcodes..."); //for each mp2 file String filename; ResultSet smallTranscode; ResultSet largeTranscode; while (videoFilenames.next()) { filename = videoFilenames.getString(1); //check for -700.mov file System.out.println(filename.substring(0, filename.length() - 4) + "-700.mov"); //test smallTranscode = stmt.executeQuery("select * from archivefile where filename='" + filename.substring(0, filename.length() - 4) + "-700.mov'"); if (!smallTranscode.next()) out.write(filename + "\n"); else { //check for -6500.mov file System.out.println(filename.substring(0, filename.length() - 4) + "-6500.mov"); //test largeTranscode = stmt.executeQuery("select * from archivefile where filename='" + filename.substring(0, filename.length() - 4) + "-6500.mov'"); if (!largeTranscode.next()) out.write(filename + "\n"); largeTranscode.close(); } smallTranscode.close(); } System.out.println("Done."); out.close(); videoFilenames.close(); } } 

Answer 1

only one ResultSet can be opened for each Statement statement time

http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html

So, as soon as you execute your second request inside the while loop, using the same instance of Statement, you close your videoFilenames ResultSet file.

Answer 2

The Java Statement documentation says:

By default, only one ResultSet can be opened for a Statement. in the same time. Therefore, if reading one ResultSet object alternates with reading another, each must be generated by different Statement objects. All execution methods in the Application interface implicitly closes the current ResultSet if one exists.

In other words, you cannot skip one ResultSet and create another using the same stmt object, which will close the first ResultSet and end the loop.