Douwload data from database

In my web application I want to give an option to user to download data from a database Oracle to an Excel file( or csv). The amount of data to retrieve from db is big. How's the better way to do that ? I'm working with JSP and Java.
SS
sandralsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimmackCommented:
If you prepare the data as though you are going to display an HTML table, but set the content type of the response to Excel, the client system should open Excel and read the data into it directly:

<%@ page contentType="application/vnd.ms-excel"%>

<TABLE ...>

</TABLE>
0
jarasaCommented:
Hi Sandra.

You want the data displayed on the browser or to be saved as file?

Javier
0
kennethxuCommented:
I prefer to use servlet to generate the csv file. just loop through the resultset and every column to construct the csv file like:

for every row in resultset
  for every column in the row
    out.println( rs.getString(columnNumber)  + "," );

you can find the number of column in rs.getResultSetMetaData()
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

objectsCommented:
Use a servlet and return the data in whatever format you require, jsp is intended for returning textual data.
POI can be used to generate Excel file:
http://jakarta.apache.org/poi/
0
sandralsAuthor Commented:
Thank you, for your help!
I'm using servlet to generate cvs file, but the problem is that I can't fetch all the information retrieved from the resultset. I'm expecting 40.000 rows but the program crash for 10.000 rows.
Do you know what's hapennig.

Sandra
0
jarasaCommented:
Hi Sandra.

Can you post the code where it crash??

How are you sending the result.... etc.

Javier
0
sandralsAuthor Commented:
Hi Jarasa!

This is an code example.
The problem is when the code executes the while(rs.next()){...} statement, it never finish  resultset fetch, when I want to retrieve a big amount of data.
In a certain point it just stop, and I don't know how to get the exception.
Could it be a memory problem?


...
try
{

      PreparedStatement ps = null;
      ResultSet rs= null;
      ArrayList LIST = new ArrayList();

      String SQL = SELECT ......

      ps = conn.prepareStatement(SQL);
      rs = ps.executeQuery();

      while (rs.next())
      {
            BeanX ITEM = new BeanX();
            ITEM.setY( rs.getInt(1) );
            ITEM.setZ (rs.getString(2));
            ...
            LIST.add(ITEM)
      }                                        

      return LIST;
}
catch (SQLException e){
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
      }
            finally {
            try {
                    if (ps != null) ps.close();
                    if (rs != null) rs.close();
                }
                catch (SQLException e){
                    e.printStackTrace();
                    throw new RuntimeException("error.unexpected");
                }
}
0
jimmackCommented:
The exception output should be being recorded in your servlet container log (eg. for Tomcat anything to System.out goes to catalina.log by default).  If you're not using Tomcat, please specify which servlet container you're using and one of the other experts should be able to tell you where the output goes.

You shouldn't need to throw the RuntimeExceptions either.

It could be a memory problem, especially with the amount of data you're talking about, but you need to find the exception output to really determine this.
0
kennethxuCommented:
I believe the error is because you ran out of memory. For such a big volume data that you are processing, it is strongly recommended not to store the data in server memory, you should process each row and send to client directory instead of store them in a list:

---------- avoid doing this ----
     while (rs.next())
     {
          BeanX ITEM = new BeanX(); // this is very expensive
          ITEM.setY( rs.getInt(1) );
          ITEM.setZ (rs.getString(2));
          ...
          LIST.add(ITEM) // this takes a lot of server memory
     }                                        
---------- try something like this ------------

     BeanX ITEM = new BeanX();
     while (rs.next())
     {
          ITEM.reset( ); // to clear data if needed.
          ITEM.setY( rs.getInt(1) );
          ITEM.setZ (rs.getString(2));
          ...
          out.println( ITEM.getY() + "," + ITEM.getZ() .... );
     }                                        
0
sandralsAuthor Commented:
I have tried to do something like that, but It doesn't write nothing at all.
How could I set the content type of the response to Excel?
...
javax.servlet.jsp.JspWriter out;
javax.servlet.jsp.PageContext pageContext;
out = pageContext.getOut();
BeanX ITEM = new BeanX();
         
while (rs.next())
     {
          ITEM.setX( rs.getInt(1) );
          ITEM.setY (rs.getString(2));
          ...
          out.println( ITEM.getX() + "," + ITEM.getY() .... );
     }      



Sandra



0
sandralsAuthor Commented:
I'm working with Tomcat 4.1.
Sandra
0
jimmackCommented:
response.setContentType("application/vnd.ms-excel");

0
jimmackCommented:
If you want to set the content type from within a JSP, see my first comment at the top of this thread.
0
kennethxuCommented:
>>>>
javax.servlet.jsp.JspWriter out;
javax.servlet.jsp.PageContext pageContext;
out = pageContext.getOut();
>>>>
Above doesn't seems to be right. r u doing as servlet or jsp, I don't think you'll be able to get proper csv file using jsp unless you are ok with blank lines in your csv file.

in servlet:
PrintWriter out = new PrintWriter( request.getOutputStream() );

if you want to use jsp, then the out var is alread there and you don't need to do anything.

and jimmack is absolutely right on content type.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sandralsAuthor Commented:
Thanks all of you!
Sandra
0
kennethxuCommented:
pleasure :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.