This week's book giveaway is in the Agile and other Processes forum. We're giving away four copies of The Mikado Method and have Ola Ellnestam and Daniel Brolund on-line! See this thread for details.
I have a task to create a batch which converts a given (database) view to XML and CSV and sends the resulting file by mail or FTP. Many APIs are available for this conversion on the net, however I am required to find the number of rows contained in the view and write it in a log (it is compulsory). This cannot be done with the tools I have found so far, there is no possibility to get the number of rows, only the number of columns.
Besides, the view might change after the query, so a count may not give a reliable result.
In addition, the view may have a complex SQL query (with several joins, maybe) and the number of records I have to deal with can be quite large(thus the XML conversion shouldn't use DOM).
Do you know of any useful APIs, so that I don't have to reinvent the wheel? Is there already an efficient solution for such situations or do I have to code one myself?
Maybe I'm missing something, but why don't you count the rows as you're writing them to the CSV or XML file? Presumably you are running some kind of SELECT query against the view to fetch the data to put it into CSV format, so this would ensure that your count reflects the number of record processed from your view. Or maybe you could use a LineNumberReader to find the total no of lines in the CSV file and subtract 1 for the header row?
You could also run a SELECT COUNT(*) FROM [your view] query, although this effectively means reading your view twice - once to build the CSV and once to count the rows in the view. This might be a slow, but you can make both queries part of a single transaction to ensure read consistency.
ex-Oracle bloke
Manuela Grindei
Greenhorn
Joined: Aug 23, 2011
Posts: 4
posted
0
chris webster wrote:Maybe I'm missing something, but why don't you count the rows as you're writing them to the CSV or XML file? Presumably you are running some kind of SELECT query against the view to fetch the data to put it into CSV format, so this would ensure that your count reflects the number of record processed from your view. Or maybe you could use a LineNumberReader to find the total no of lines in the CSV file and subtract 1 for the header row?
You could also run a SELECT COUNT(*) FROM [your view] query, although this effectively means reading your view twice - once to build the CSV and once to count the rows in the view. This might be a slow, but you can make both queries part of a single transaction to ensure read consistency.
I actually did count the rows while writing them to the file (I made my own implementation in the meantime). What I wanted to know was if there was already an API adapted to these requirements. The tools I saw on the net did not have any way to find the number of rows.
0
