Application Techniques

Importing a Comma-Delimited File into a Database Table

How to import a comma-delimited file into a database table using an invoked business object and a data source object. This technique uses an invoked 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 Import 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 import data feature of the DataXpress sample applications implements the following objects:


What it is

What it does


Client page for the import file option

Gets the user-selected data and calls the invoked business object invImport


SilverStream-invoked business object

Contains an AgaData called AgaDest, which is bound to a data source object called dsoEditTableData; the invoked object populates AgDest with the rows to be added to the database and calls dsoEditTableData to update the data source


SilverStream-invoked business object that deletes data from a selected table

Opens a direct JDBC connection to the database and executes the SQL for deleting the data, then closes the connection (the implementation of this object is not described in this technique)


SilverStream data source object

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


AgiDataSource object

Accesses the data source and imports the rows to the destination table

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, the page constructs a hashtable (htparams) that includes the database and table selected by the user--plus the selected file, which is stored as a byte array.

  2. The page calls invImport using the invokeBusinessObject() method, passing htparams as a parameter.

  3. The invoked business object tests the value of bpurge. If true, it:

  4. After testing bpurge, the invoked object calls invokeQuery() on AgDest, passing the database and table values to the data source object.

  5. The data source object instantiates JDBCDataSource, the object that implements AgiDataSource. This object is responsible for copying the rows from the AgiRowCursor to the destination database.

  6. The invoked object calls local method importFile() passing two parameters: AgDest as an AgiRowCursor and the uploaded file as a byte stream.

  7. The importFile() method populates the row cursor with the rows to be added to the database.

  8. The invoked object calls updateRows() on AgDest, which triggers the data source object to prepare and commit the rows in the AgiRowCursor to the destination database.

Invoking the business object   Top of page

When the user selects the Submit button on the page, the page gets the form data selected by the user and stores it in a hashtable. Then it invokes the business object and passes the hashtable as a parameter.

  private void handle_btnSubmit_pageActionPerformed(ActionEvent evt) 
        throws Exception    
     // Reset the output box 
     // Create buffer to hold result of dso 
     StringBuffer sbResults = new StringBuffer(); 
     // Get the boolean value for purgTable 
     Boolean bPurge = new Boolean((String)chEmpty.getValue()); 
    // Get selected db and table and place in a hashtable 
     Hashtable htParms = new Hashtable(); 
     String sTableName = 
        // Invoke the business object, passing the hashtable 
          sbResults.append(" " + (String)  
            invokeBusinessObject "com.examples. 
              invokedobjects.invImportData",htParms) ); 
      // Display error or the results of the import 
      catch (Exception e)  
           sbResults.append(" Error - " + e.toString()); 

Invoking the data source object   Top of page

The invoked object tests the purge table value. If true, it calls invPurgeTable to handle the purging of the table before executing the import (this code is not shown here).

It then calls invokeQuery() on AgDest, passing the database and table in a new hashtable to the data source object. Next it calls a local method importFile(), passing AgDest as an AgiRowCursor and the uploaded file as a byte array.

Finally, the invoked object calls updateRows() on the AgiRowCursor returned by importFile(). This method triggers the data source object to prepare the update request and commit the rows to the database.

   public void invoked(AgoInvokedEvent evt) 
        throws Exception 
        StringBuffer sb = new StringBuffer(); 
        // Create variable to hold the number of rows imported 
        Double dCount; 
        Hashtable h = (Hashtable) evt.getParameter(); 
        // Code to handle purge option goes here...      
        // Here is the code to import... 
      // Get the AgaData 
        AgaData agaDest = evt.getAgaData("agaDest"); 
      // Next store db and table indexes in new hashtable  
        Hashtable htParms = new Hashtable(); 
        htParms.put ("DBNAME", h.get("destDBName")); 
        htParms.put ("TABLENAME", h.get("destTableName")); 
       // Get the comma-delimited file and store in new byte array 
        byte[] byteData = (byte[]) h.get("destData"); 
       // Invoke the dso on the AgaData, passing the hashtable  
       // indexes      
        agaDest.invokeQuery (htParms); 
       // Call local method to populate the AgaData 
        dCount = importFile(agaDest,byteData); 
       // Call updaterows to trigger the dso... 
        sb.append(dCount.toString() + " Rows Imported" ); 
       // Display error or set result... 
        catch (Exception e)  
           sb.append("Error -- " + e.toString());          
         // Return the number of rows copied to the client 

Importing the file   Top of page

The importFile() method populates the AgiRowCursor with the rows to be added to the database. First it uses a Java StreamTokenizer to parse the contents of the file to be imported, then it uses a vector to store the contents as row data. Next it copies the vector data row by row into the AgiRowCursor, until all the rows are copied over. Finally, this method gets and returns the number of rows copied, which is passed back to the page:

  private Double importFile(AgiRowCursor rc, byte[] byteData) 
  // PM_Ag_importFile 
      double count = 0; 
         // Get the file and set params for a new streamtokenizer   
         StreamTokenizer st = new StreamTokenizer(new 
          InputStreamReader(new ByteArrayInputStream( 
         // Parse the contents of the tokenizer and store as  
         // rows in a new vector 
         Vector row = new Vector(); 
         while (st.ttype != StreamTokenizer.TT_EOF)  
            switch (st.ttype)  
             case StreamTokenizer.TT_NUMBER: 
             row.add(new Double(st.nval)); 
             case StreamTokenizer.TT_WORD: 
             case StreamTokenizer.TT_EOL: 
             // Copy each row into the AgiRowCursor passed in 
             count ++; 
             for (int x=0;x<row.size();x++)  
             // Empty the vector   
             case 34: // "'s 
           }// End of switch block       
        } // End of while block 
      } // End of try block 
      catch (Exception e)  
         System.err.println("Exception caught: " + e); 
    // Return row count to the client 
    return new Double(count);   

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