• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Database View to CSV and XML

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Manuela Grindei
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you store it on the file system? Same idea: xpath/line counting from there.
 
Manuela Grindei
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, I do create XML/CSV files on the disk, but I cannot parse them, because they may have a really big size.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Manuela Grindei
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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!
reply
    Bookmark Topic Watch Topic
  • New Topic