Newbie - read AS400 file and write it to a .txt file

[rjw]

Hi, this *seems* like it should be simple. I am trying to read an AS400 file and write every record in it to a .txt file on my "C" drive. My SQL statement reads every record in the AS400 file (confirmed by the log entry for "kount"), but for some reason unknown to this newbie it is not writing all of the records to the .txt file. There are 150 records in the AS400 file but only the first 128 records are being written to the .txt file. I'm guessing it is some type of buffering issue but I have no idea how to handle it. My code is below - cna you tell me what I am doing wrong? Thanks!

FileWriter outFile = new FileWriter(downloadPath + positiveFile + ".txt");
PrintWriter output = new PrintWriter(outFile);
String fileDetail = "";

Class.forName("com.ibm.as400.access.AS400JDBCDrive r");
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
conn1 = DriverManager.getConnection("jdbc:as400://" + datasource, Uid, Pw);

String sqlString1 = "SELECT * FROM SYSSPEC.TESTFILE1";
PreparedStatement stmt1 = conn1.prepareStatement(sqlString1,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs1 = stmt1.executeQuery();
int kount = 0;
while (rs1.next()) {
fileDetail = rs1.getString("DETAIL1");
//-- Write record to the ".txt" file, then loop to read next record
output.println(fileDetail);
kount++;
}
stmt1.close();
logger.debug(kount + " records read.");

[JosAH]

Does it help if you play with the setFetchSize( ... ) on your ResultSet?

kind regards,

Jos

[rjw]

Does it help if you play with the setFetchSize( ... ) on your ResultSet?

kind regards,

Jos

I tried that, thanks. But no luck. Still the same issue. I do want to point out also the original input file I was working with had 300,000+ records. I went ahead and reduced the number of records in the file to 150 to help in dwebugging this problem but that made no difference.

[JosAH]

I tried that, thanks. But no luck. Still the same issue. I do want to point out also the original input file I was working with had 300,000+ records. I went ahead and reduced the number of records in the file to 150 to help in dwebugging this problem but that made no difference.

There's also a 'setMaxRows( ... )' method on the Statement where you get your ResultSet from; does that help? Are you positively, absolutely sure that you have (at least) 150 rows in your table?

kind regards,

Jos

[rjw]

Yes, I'm sure about the number of records in the input AS400 file. I went ahead and did a STRDFU of that file to put 65 records into it. I ran my program in debug and watched it read all 65 records and execute the "output.println" statement 65 times. But when I looked in the output .txt file only the 1st 64 records were written. I went ahead and removed the 65th record from the input file, ran the program again and it read all 64 records, executed the "output.println" 64 times, but when I checked the .txt file it had no records in it this time.

I tried putting "stmt1.setfetchSize(1)" just before the "executeQuery" but that did not work. I put "rs1.setfetchSize(1)" just after the "executeQuery" and that did not help either. I don't know if that is the correct way to format that though. I toyed with the setMaxRows several times - not sure if I formatted it properly - but no change in the results. I even tried commenting out the "stmt1.close" statement to see if that was causing the process to close before all the records in the buffer could be written but no luck there.

Somebody suggested trying java i-o read next - I will have to look that up and see how to do it (although it sounds like it might be slow..).

[JosAH]

I vaguely remember, running on VM CMS, that I had to specify the number of records when I worked with a file (by using JCL); does that ring a bell?

kind regards,

Jos

[Tolls]

Please use [code] tags [/code] when posting code.

Are you saying kount prints out as 150 in your logs, yet you are only getting 128 in the file?

All I can say is, there's no sign of you closing/flushing the file...so I bet, since 128 is a nice binary number, that the other 22 rows are still in the buffer.

[rjw]

That's IT!!!!!!!

I used bufferedWriter, then bufferedWriter.flush() and bufferedWriter.close() and that solved the problem!

Thank you josAH and Tolls for helping me out on this!

[Tolls]

That's IT!!!!!!!

I used bufferedWriter, then bufferedWriter.flush() and bufferedWriter.close() and that solved the problem!

Thank you josAH and Tolls for helping me out on this!

You can just use close(), as that does a flush first (IIRC).