GeeCON Prague 2014*
The moose likes JDBC and the fly likes Database View to CSV and XML Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database View to CSV and XML" Watch "Database View to CSV and XML" New topic
Author

Database View to CSV and XML

Manuela Grindei
Greenhorn

Joined: Aug 23, 2011
Posts: 4
Hi everyone,

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?

Thank you,

Manuela
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30580
    
154

Do you have the XML or CSV in memory after using the tool? If so, you could do an XPath expression or line count to see how many rows there are.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Manuela Grindei
Greenhorn

Joined: Aug 23, 2011
Posts: 4
No, I can't have the XML/CSV in memory, because it can reach big dimensions (the number of query results can be huge).
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30580
    
154

Can you store it on the file system? Same idea: xpath/line counting from there.
Manuela Grindei
Greenhorn

Joined: Aug 23, 2011
Posts: 4
Yes, I do create XML/CSV files on the disk, but I cannot parse them, because they may have a really big size.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1726
    
  14

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.


No more Blub for me, thank you, Vicar.
Manuela Grindei
Greenhorn

Joined: Aug 23, 2011
Posts: 4
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.

Thank you all for your support!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Database View to CSV and XML