Coversion of ResultSet

[Surya Indukuri]

Hi,
I have a project that need to connect to Access database, run a select and write the results to a text file.
The coloumns needs to be Pipe seperated in the text file.

I was able to work my program until getting the results in the ResultSet. I need to know how I need to convert each row attributes as pipe delimited and write to text file. can some one give me a sample code on how I can achieve this?

Thank You,

[Jeff Albertson]

Do you mean that you want to do this?

s = s.replace("|", "\\|");

[Surya Indukuri]

No, Here is a sample of my code. Please see my code and suggest the right way to do this.

public static void main (String[] args) {
String Query = new String();
StringBuffer queryResult = new StringBuffer();
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String url = "jdbc dbc:TracerDB";
Connection conn = DriverManager.getConnection(url,"TracerApp", "st0pd01ngth8t");

Query += "select * from Application ";
Statement db_statement = conn.createStatement();
ResultSet result = db_statement.executeQuery(Query);
while (result.next() )
{
queryResult.append(result.getInt(1)+"|");
queryResult.append(result.getString(2));v //and so on
//Here I need to send this converted Pipe delimited attribute rows to a text file. I do not know how to do this
System.out.println (queryResult);
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}

}
}

[Jeff Albertson]

So your question is "how do I write to a text file"?

have you read the tutorial: http://java.sun.com/docs/books/tutorial/essential/io/index.html

[Surya Indukuri]

Yes, I have read that. I do'nt seem to understand how I should read the ResultSet and write to a text file. I want to convert each row and write it to a file immediately in the same loop. I want to pass the queryResult String to write. There are no write methods for this type. Can you pl help me on how I can achieve this?

[Jeff Albertson]

It still seems to be that you are simply asking "how can I write text"?

Anyway, there are short cuts, but with all the options, openning a file can look like:

File file = new File("...");
String charsetName = "UTF-8"; 
boolean appendMode = false;
boolean autoFlush = true;
 
FileOutputStream fos = new FileOutputStream(file, appendMode);
BufferedOutputStream bos = new BufferedOutputStream(fos);
OutputStreamWriter osw = new OutputStreamWriter(bos, charsetName);
PrintWriter out = new PrintWriter(osw, autoFlush);

Then you have a loop where you construct strings, correct?

while (...) {
   ...
   String s = ...;
   out.peintln(s);
}

And finally, don't forget to out.close() when you are done!

[Scott Selikoff]

I think what you're missing is that you have some belief that there is a 1-to-1 mapping between a result set and a text file, there is no such mapping. You cannot write a result set to a file, although you can write the data contained in a result to set a file, but you must build the structure/format of the file yourself the same way you would write any text data at all to file.

[Stan James]

Following on with that, you can do something like:

for each row in the result set
   for each column in this row
      write a pipe character
      write one column to the file
   next column
   write a newline to end the line in the text file
next row

Is that the kind of logic you had in mind? My example makes the line in the text file start with a pipe. Can you see how to change it so the first column doesn't write a pipe character first?

=== more advanced idea ===

If you get that running, see if this makes sense. Separate the file writing out to a result set handler:

handler.startResultSet()
for each row in the result set
   handler.startRow()
   for each column in the row
      handler.handleColumn( column data )
   next column
   handler.endRow()
next row
handler.endResultSet()

Imagine how you could make other handlers to populate object structures or a Swing table model or whatever else you can think of.