Win a copy of Practical SVG this week in the HTML/CSS/JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

File Formatting best practices ?

 
jay vas
Ranch Hand
Posts: 407
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys : Im working on a tool integration project involving multiple file formats.

We have run into an interesting issue- the need to create flat files as input to legacy tools, using data from a relational database.

Does anybody have any thoughts on "best practices" for such an operation?

I can think of 2 ways
1) have a database create a flat file for you (on demand) using a complex stored procedure, and retrieve it as a blob, or 2) retrieve the raw data in a bean from the database, and convert it to a file using Java's File writing API. That is , the bean would have a "getAsFile()" method along with traditional getters and setters.

Im leaning towards the scalable, object oriented approach of (2), but I was just wondering if anyone else had any thoughts on these issues ? My boss leans towards (1), being a database guy.

My thoughts: In defense of the second approach, the approach of 1 breaks down very quickly when a tool requires more than one flat file as its input... whereas (2) can easily be modified in a "clean" way to suit more complex scenarios.
Also, I would say that tool specific data conversions are more aptly handled in the application tier than in the relational tier, because a relational database's is job primarily to manage relations and rules about data, and not to manage different, non relationally pure "perspectives" on that data.
 
Magnus Palm�r
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would retreive the data from the database as an xml file that you then can convert to flat files as needed. (Using xslt is an option then).

There are some sql2xml frameworks and many RDB vendors have their own "standard" way of producing xml.

In J2SE 5.0 there is a javax.sql.rowset.WebRowSet where you can create an xmlfile with the writeXml(ResultSet rs, OutputStream oStream).

I have not tried this yet myself though, it is still on my todo list

I have coded a small program that I use sometimes where I enter a sql queary and use the ResultSet togheter with ResultSetMetaData and JDOM to produce the XML.

You also have the option of using a Sql2Java framework (I've only used Castor myself) to get the Sql data into JavaBeans.

Well that was just my thoughts, good luck.
 
jay vas
Ranch Hand
Posts: 407
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh - i forgot to mention that the files are enormous binary files ( > 3 megabytes) so encoding them as XML is prohibitive...

any other thoughts ?
 
Paul Clapham
Sheriff
Posts: 21892
36
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best practices don't come out of a vacuum. Your main goal is to produce files that can be used by these legacy tools. So you need to search for commonalities among the file formats you're going to be producing. If there are none then I wouldn't really be concerned about a unified approach, just write separate code for each. If the formats are very similar, then try to leverage the similarities.

Another issue is whether the file formats come in varieties. We have some code that produces a comma-separated flat file format for a certain system that a lot of our customers use. It was pretty straightforward to produce nice clean code that extracted data from the database and wrote it in this format. But then the customers had some requests for the contents of the file. "We need check digits on the UPCs." "Don't send us the credit invoices." "We need our commodity codes in field 17." And so on. Some wanted this, some wanted that. All totally ad hoc requirements. It's almost impossible to make a nice system out of that.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!