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.
You can run this technique code from:
NOTE First make sure that database is running on your localhost SilverStream server | |
|
The import data feature of the DataXpress sample applications implements the following objects:
Gets the user-selected data and calls the invoked business object invImport | ||
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) | |
Instantiates JDBCDataSource, which implements the AgiDataSource object for the DataXpress application | ||
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 |
The invPurgeTable opens a direct JDBC connection to the database, then executes a SQL delete statement to purge the table. (This object is not described in this technique.)
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.
Related reading
private void handle_btnSubmit_pageActionPerformed(ActionEvent evt)
throws Exception
{
// Reset the output box
lblResults.setText("");
// 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 =
chDestTable.getItem(chDestTable.getSelectedIndex());
htParms.put("destDBName",chDestDB.getItem
(chDestDB.getSelectedIndex()));
htParms.put("destTableName",sTableName);
htParms.put("destData",m_byteData);
htParms.put("purge",bPurge);
sbResults.append("<p>Table:");
sbResults.append(sTableName);
try
{
// 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());
}
lblResults.setText(sbResults.toString());
}
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
{
try
{
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...
agaDest.updateRows();
sb.append(dCount.toString() + " Rows Imported" );
}
// Display error or set result...
catch (Exception e)
{
System.out.println(e);
sb.append("Error -- " + e.toString());
}
finally
{
// Return the number of rows copied to the client
evt.setResult(sb.toString());
}
return;
}
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;
try
{
// Get the file and set params for a new streamtokenizer
StreamTokenizer st = new StreamTokenizer(new
InputStreamReader(new ByteArrayInputStream(
byteData)));
st.eolIsSignificant(true);
st.ordinaryChars(32,33);
st.ordinaryChars(35,43);
st.ordinaryChars(45,126);
st.wordChars(32,33);
st.wordChars(35,43);
st.wordChars(45,126);
st.nextToken();
// Parse the contents of the tokenizer and store as
// rows in a new vector
Vector row = new Vector();
rc.gotoLast();
while (st.ttype != StreamTokenizer.TT_EOF)
{
switch (st.ttype)
{
case StreamTokenizer.TT_NUMBER:
row.add(new Double(st.nval));
break;
case StreamTokenizer.TT_WORD:
row.add(st.sval);
break;
case StreamTokenizer.TT_EOL:
// Copy each row into the AgiRowCursor passed in
rc.insertAfter();
count ++;
for (int x=0;x<row.size();x++)
rc.setProperty(x,row.elementAt(x));
// Empty the vector
row.removeAllElements();
break;
case 34: // "'s
row.add(st.sval);
break;
default:
}// End of switch block
st.nextToken();
} // 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);
}