How to publish a table from one database to another database on the same SilverStream server. 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 publish data feature of the DataXpress sample application implements the following objects:
Gets the user-selected data and calls the invoked business object invPublishTableData | ||
Contains two AgaDatas (AgaSource and AgaDest), each of which is bound to 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 | Uses JDBC calls to get a connection to the database from the SilverStream connection pool, executes the SQL for deleting the data, and then closes the connection (the implementation of this object is not described in this technique) | |
This object instantiates JDBCDataSource, which implements the AgiDataSource object for the DataXpress application | ||
Gets the table from the data source and copies it to the data destination This object instantiates several utility objects in the JDBC package; for more information, see Developing a DSO for use by Multiple Clients |
The source table(s) selected by the user The destination table(s) specified by the user The user's choice to purge or not to purge the destination table |
invPurgeTable gets a connection to the database and executes a SQL delete statement to purge the table.
When the page is submitted, the code uses the AgiRowCursor.copy() method to copy the user data from the data view (stored in member variable m_dataManagerRC) to a new AgiRowCursor. Then it loops through the row cursor, gets the user's selections, and stores the results in a hashtable. Finally, the code calls the invoked object, passing the hashtable as a parameter.
private void handle_btnSubmit_pageActionPerformed(ActionEvent evt) throws Exception { // Reset the output box lblResults.setText(""); StringBuffer sbResults = new StringBuffer(); // Loop over all the items in the list and // copy items selected AgiRowCursor mylRC = m_dataManagerRC.copy(); // Start download mylRC.gotoFirst(); // Do while ... do { Boolean copyTable = (Boolean) mylRC.getProperty("Copy"); if (copyTable.booleanValue()) { Hashtable htParms = new Hashtable(); htParms.put("srcDBName",chSourceDB.getItem(chSourceDB. getSelectedIndex())); htParms.put("srcTableName",mylRC.getProperty ("SourceTable")); htParms.put("destDBName",chDestDB.getItem (chDestDB.getSelectedIndex())); htParms.put("destTableName",mylRC. getProperty("DestTable")); htParms.put("purge",mylRC.getProperty("EmptyTable")); sbResults.append("<p>Table:"); sbResults.append(mylRC.getProperty("SourceTable")); try { // Invoke the business object, passing the hashtable Double d = (Double) invokeBusinessObject ("com.examples.invokedobjects. invPublishTableData",htParms); // Get number of rows copied from invoked object sbResults.append(" " + d.toString() + " Rows Copied"); } catch (Exception e) { sbResults.append(" Error-" + e.toString()); } } // Selected } while (mylRC.gotoNext()); // Display number of rows copied lblResults.setText(sbResults.toString()); } }
The invoked object tests the boolean variable for deleting the target table. If true, the code invokes another business object to delete the data. Next the code invokes the data source object on each of its AgaDatas, passing the database and table selected by the user.
Next the code calls copyDataset(), passing the two AgaDatas as AgiRowCursor objects (see Copying the data to the destination table). After the data is copied, the code calls updateRows(), which triggers the AgiDataSource object (JDBCDataSource) to commit the rows in AgaDest to the actual data source. Finally, the code in the invoked method gets the number of rows copied and returns it to the page.
public void invoked(AgoInvokedEvent evt) throws Exception { Hashtable htParms = new Hashtable(); Hashtable h = (Hashtable) evt.getParameter(); Boolean bPurge = (Boolean) h.get("purge"); if (bPurge.booleanValue()) // If specified by the user, delete the destination table // first { Hashtable htPurge = new Hashtable(); htPurge.put("database",h.get("destDBName")); htPurge.put("table",h.get("destTableName")); // Get an AgiDatabase object AgiDatabase aDatabase = evt.getDatabase(); // Call a business object to delete the data // The invoked object will open a direct JDBC // connection and delete the data aDatabase.invokeBusinessObject( "com.examples.invokedobjects. invPurgeTable",htPurge); } // Get the AgaDatas on this object... AgaData agaSource = evt.getAgaData("agaSource"); if (agaSource==null) System.out.println( "agaSource is null"); AgaData agaDest = evt.getAgaData("agaDest"); if (agaSource==null) System.out.println( "agaSource is null"); // Get the db and table passed in and invoke the dso on // each AgaData... htParms.put ("DBNAME", h.get("srcDBName")); htParms.put ("TABLENAME", h.get("srcTableName")); agaSource.invokeQuery (htParms); htParms.put ("DBNAME", h.get("destDBName")); htParms.put ("TABLENAME", h.get("destTableName")); agaDest.invokeQuery (htParms); // Call local method to manipulate the AgiRowCursors // and get the number of rows copied. long x = copyDataset((AgiRowCursor) agaSource,(AgiRowCursor)agaDest); // Update the destination database. agaDest.updateRows(); // Return rows copied to the caller evt.setResult(new Double(x)); return; }
The copyDataset() is a user-defined method called by the invoked object. It loops through each AgiRowCursor, calling navigation and update methods to copy the data from the AgaSource to AgaDest.
public long copyDataset(AgiRowCursor agiSource, AgiRowCursor agiDest) { long count = 0; try{ System.out.println ("copyp AgiRowCursor"); int colCount = agiSource.getPropertyCount(); boolean rc = agiSource.gotoFirst(); while (rc) { count++; agiDest.gotoLast(); agiDest.insertAfter(); for ( int x = 0; x < colCount; x++) { Object obj = agiSource.getProperty (x); agiDest.setProperty(x,obj); } rc = agiSource.gotoNext(); } } // Try catch (Exception _e){ System.out.println ("==>boCopyTest -- copyDataset " + _e); } return count; }