Application Techniques

Exporting a Database Table to a Formatted File

How to export a database table to a specified file format. This technique uses a servlet business object and a data source object.

About this technique



Data Access Techniques> Data source objects


You'll learn about:

You can run this technique code from:

  • Database: DataXpress3 (choose Export from the application home page)

NOTE   First make sure that database is running on your localhost SilverStream server

Related reading

Objects used in this technique   Top of page

The export data feature of the DataXpress sample application implements the following objects:


What it is

What it does


Client page for the export file option

Gets the user-selected data and calls the servlet srvSaveAs


SilverStream servlet object

Contains an AgaData called AgaSource, which is bound to a data source object called dsoEditTableData


SilverStream data source object

Instantiates JDBCDataSource, which implements the AgiDataSource object for the DataXpress application


AgiDataSource object

Accesses the data source and populates the AgaData with the rows to be exported

This object instantiates several utility objects in the JDBC package; for more information, see Developing a DSO for use by Multiple Clients

Data flow   Top of page

  1. When the user chooses the Submit button, pgExport constructs a hashtable (htparams) containing the database and table selected by the user and the user's choice to include or not include the table header row in the exported file.

  2. Using the showPage() method, pgExport invokes the URL for the servlet srvSaveAs, passing htparams as a parameter.

  3. The servlet calls invokeQuery() on its AgaData, passing the database and table values to the data source object.

  4. The data source object instantiates JDBCDataSource, the object that implements AgiDataSource. This object sends rows to the AgiRowCursor in response to the method calls from the servlet (see step 7).

  5. The servlet calls the user-defined method saveAs() passing four parameters: a ServletOutputStream object, a String value for the file type, the boolean value for including the headers, and the AgaData as an AgiRowCursor object.

  6. The saveAs() method reads the file type and calls the appropriate method to generate the file type, passing the boolean value and the AgiRowCursor and getting back a StringBuffer.

  7. The generate method formats the file and initiates the download of rows from the data source object by calling gotoFirst() and gotoNext() on the AgiRowCursor.

  8. The saveAs() method passes the result of the generate method back to the client.

Invoking the servlet   Top of page

When the user chooses the Submit button, the page gets the data the user entered on the form and stores it in a has table. Then it invokes the servlet URL using the showPage() method, passing the hashtable to the servlet:

  private void handle_btnSubmit_pageActionPerformed(ActionEvent evt) 
   throws Exception 
      // Reset the output box 
      StringBuffer sbResults =new StringBuffer(); 
      Hashtable htParms = new Hashtable(); 
      try { 
          // Get the db and table indexes 
          String sTableName = 
          String sDBName = 
          if (sTableName==null)  
            sbResults.append("You must specify a table to  
                export from"); 
          // Add items needed by the servlet to the hashtable 
          // Call the servlet, passing the hashtable 
          catch (Exception e) { 
            sbResults.append(" Error - " + e.toString()); 
             // Display the table name on the page 

Invoking the data source object   Top of page

The servlet gets the hashtable parameters and invokes the data source object dsoEditTableData, passing the database and table values. Then it sets the ServletResponse content type according to the file type selected by the user. Finally, it sets the servlet output by calling the local method saveAs():

  public void service(javax.servlet.ServletRequest req, javax.servlet.ServletResponse res) 
      throws javax.servlet.ServletException, 
      AgoHttpRequestEvent hRequest = (AgoHttpRequestEvent) req; 
      HttpServletResponse hResponse = (HttpServletResponse) res; 
      // Get the values passed in; need to cast them 
      String sdbname = (String) 
      String sTableName = (String) 
      String sType = (String) 
      Boolean bHeaders =  new Boolean((String) 
      // Get the AgaData 
      AgaData agaSource = hRequest.getAgaData("agaSource"); 
      if (agaSource==null) System.out.println("agaSource is null"); 
      Hashtable htParms = new Hashtable();  
      // Put db and table values in new hashtable    
      htParms.put ("DBNAME", sdbname); 
      htParms.put ("TABLENAME", sTableName); 
      // Invoke the dso, passing the hashtable 
      Vector vReturn = (Vector) agaSource.invokeQuery (htParms); 
     // Set the appropriate http content type  
     if (sType.equals("HTML"))  
        hResponse.setContentType("text/html; charset=iso-8859-1"); 
      if (sType.equals("CSV"))  
        hResponse.setHeader("Content-Disposition", "attachment; 
        filename=\""+ sTableName +".csv\"");        
      if (sType.equals("TAB"))  
        hResponse.setHeader("Content-Disposition", "attachment; 
        filename=\""+ sTableName +".tab\"");  
      if (sType.equals("EXCEL"))  
    // Set the servlet response ... 
    ServletOutputStream stream = hResponse.getOutputStream();  
    // Pass parameters to local method to generate the response 
    // Pass AgSource as an AgiRowCursor object 
  catch (Exception e)  
      System.out.println("service -- " + e); 

Returning the generated file   Top of page

The saveAs() method reads the file type parameter and calls the appropriate method to generate the file. Then it stores the results of the generate method in a String variable, writes the String to the ServletOutputStream, and returns it to the caller:

  public int saveAs(ServletOutputStream fos, String  sfileType, boolean headers, AgiRowCursor m_rowcursor) 
        int rc = 0; 
        String fileData = null; 
          // Call one of the generate methods 
          if (sfileType.equals("CSV"))   
             fileData =generateCSV(headers,m_rowcursor);  
          else if (sfileType.equals("TAB"))  
             fileData = generateTAB(headers,m_rowcursor);  
          else if (sfileType.equals("EXCEL"))  
             fileData = generateTAB(headers,m_rowcursor);  
          else if (sfileType.equals("HTML"))  
             fileData = generateHTML(headers,m_rowcursor);  
          if (fileData == null)  
             rc = -1; 
          // Write the results to the output stream 
          fos.write (fileData.getBytes()); 
          return rc; 
     catch (Exception _e) 
          System.out.println ("save as" + _e); 
          return -1; 

Generating the file   Top of page

The saveAs() method in the invoked evt calls the method to generate the file. This section describes the generation method for the HTML option.

First the generateHTML() method generates the format for an HTML table, then it creates a StringBuffer to store the row data.

Then, in the second half of the try block, the code loops through the AgiRowCursor object, filling each row of the html table with the row data. Each row is appended to the StringBuffer as a new HTML table row. After the try block, the StringBuffer with all of the rows is returned to the calling method.

  public String generateHTML(boolean headers, AgiRowCursor m_agiRowCursor) 
  // Set up html formatting ... 
  String hdrTD = "<TD VALIGN=TOP BGCOLOR=\"#2C547C\"><FONT SIZE=3 
    COLOR=\"#FFFFFF\" FACE=\"Helvetica\"><SPAN STYLE=\"font- 
  String hdrTDEnd = "</SPAN></FONT></TD>"; 
  String rowTD = "<TD VALIGN=TOP><FONT SIZE=3 
    FACE=\"Helvetica\"><SPAN STYLE=\"font-size:14;\">"; 
  String rowTDColor = "<TD VALIGN=TOP BGCOLOR=\"#d2d2d2\"> 
    <FONT SIZE=3 FACE=\"Helvetica\"><SPAN STYLE=\"font-size:14;\">"; 
        String rowTDEnd = "</SPAN></FONT></TD>"; 
  // Use to alternate row shading     
  boolean toggle = false;   
  //   Create stringbuffer and generate the file... 
  //   System.out.println ("generating HTML"); 
       StringBuffer sb = new StringBuffer (); 
       sb.append("<BODY BACKGROUND=\"../SilverStream/ 
       int colCount = m_agiRowCursor.getPropertyCount(); 
       sb.append ("<TABLE BORDER=1 BGCOLOR=\"#ffffff\"  
       // Include headers from db table? 
       if (headers)  
           sb.append ("<tr>"); 
           for (int y=0; y<colCount; y++) 
              sb.append (hdrTD + m_agiRowCursor. 
                getPropertyName (y) + hdrTDEnd);   
          sb.append ("</tr>\n"); 
      // System.out.println ("generating HTML2");  
      // Tell the dso to start feeding the AgiRowCursor...   
       boolean rc = m_agiRowCursor.gotoFirst(); 
       int rowcount = 0; 
       while (rc) 
             // Keep count of rows added 
             sb.append ("<tr>"); 
             // Get all of the rows from the table 
             for ( int x = 0; x < colCount; x++)  
                 Object obj = m_agiRowCursor.getProperty (x); 
                 if (obj==null)  
                    obj = (String) "null"; 
                 else if (obj.equals(""))  
                    obj = "empty"; 
                 // Alternate row shading 
                 if (!toggle) 
                   sb.append (rowTD + obj.toString() + rowTDEnd); 
                    sb.append (rowTDColor + obj.toString() 
                      + rowTDEnd);                 
               } // end of for block 
            // Add html end of row marker 
            sb.append ("</tr>\n"); 
            toggle = !toggle; 
            // Get next row from the AgiRowCursor 
            rc = m_agiRowCursor.gotoNext(); 
          } // End of while block 
          // Add html end-of-table marker 
          sb.append ("</table>"); 
          // Add text with row count 
           sb.append("<p>Rows Retrieved: "+ new Integer(rowcount) ); 
          // Add EOF html marker 
          // Return table as string to caller 
          return sb.toString(); 
        } // End of try block   
       catch (Exception _e) 
          System.out.println ("gen html:" + _e); 
          return null; 

Copyright © 2000, SilverStream Software, Inc. All rights reserved.