Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Douwload data from database

Posted on 2003-11-27
16
Medium Priority
?
340 Views
Last Modified: 2010-04-01
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
0
Comment
Question by:sandrals
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +2
16 Comments
 
LVL 15

Expert Comment

by:jimmack
ID: 9830869
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
 
LVL 6

Expert Comment

by:jarasa
ID: 9830888
Hi Sandra.

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

Javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9831322
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 92

Expert Comment

by:objects
ID: 9833787
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
 

Author Comment

by:sandrals
ID: 9873600
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
 
LVL 6

Expert Comment

by:jarasa
ID: 9873868
Hi Sandra.

Can you post the code where it crash??

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

Javier
0
 

Author Comment

by:sandrals
ID: 9874691
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
 
LVL 15

Expert Comment

by:jimmack
ID: 9874760
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 9874844
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
 

Author Comment

by:sandrals
ID: 9904340
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
 

Author Comment

by:sandrals
ID: 9904354
I'm working with Tomcat 4.1.
Sandra
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9904428
response.setContentType("application/vnd.ms-excel");

0
 
LVL 15

Expert Comment

by:jimmack
ID: 9904441
If you want to set the content type from within a JSP, see my first comment at the top of this thread.
0
 
LVL 14

Accepted Solution

by:
kennethxu earned 1000 total points
ID: 9904520
>>>>
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
 

Author Comment

by:sandrals
ID: 9926731
Thanks all of you!
Sandra
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9930936
pleasure :-)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question