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.
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.