Application Techniques

Developing a DSO for Use by Multiple Clients

How to develop a SilverStream data source object that can be used by multiple pages. This technique describes the data source object used with the DataXpress sample application.

About this technique



Data Access Techniques> Data source objects


You'll learn about:

You can run this technique code from:

  • Database: DataXpress3 (choose Import, Publish, or Export from the application home page)

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

Related reading

The data source object (dsoEditTableData)   Top of page

The object described in this technique (dsoEditTableData) is an updatable data source object designed to service different types of database requests from multiple clients. For each request from the caller (which can be a SilverStream page or a business object), the data source object instantiates a new AgiDataSource object. The AgiDataSource is responsible for communications with the actual data source.

The calling object communicates with the data source object through a SilverStream data cache object (AgData). It passes only the database and table values to the data source object. Then the caller uses AgiRowCursor methods to call the data navigation and data manipulation methods to which the AgiDataSource responds.

This updatable data source object configures itself at runtime for the appropriate database and table. The process of getting the metadata from the database involves a certain amount of overhead. (This is in contrast to a static updatable data source object, where the table columns are predefined in the class.)

Objects used in this technique   Top of page

This technique uses the following objects:


What it is

What it does


SilverStream data source object

Instantiates a utility object called JDBCDataSource and sets the data source


Utility class that implements the com.sssw.rt.util.AgiDataSource interface

Obtains the data from the external data source, formats it in the appropriate row and column format and populates the server-side data cache object (AgaData) for download to the client


Standard Java utility class that returns the next available row

Used by the JDBCDataSource.getNextRows() method, one of the methods that is part of the AgiDataSource implementation


Utility class that implements the com.sssw.rt.util.AgiBandDescriptor interface

Provides important column information


Utility class that extends the com.sssw.rt.util.AgoDataUpdateRequest object

Gathers the updated records from the AgcData on the client and contains methods that define how the updates should be committed to the external data source; defines methods that provide the mechanics of an update


Utility class that extends the com.sssw.rt.util.AgoDataUpdateRow object

Is instantiated by JDBCDataUpdateRequest


Utility class that implements the com.sssw.rt.util.AgiTransactionHandle interface

Connects and disconnects from the database

Calling the data source object   Top of page

The data source object in the DataXpress application services three pages that handle three database functions: import data, export data, and publish data. Each page gets the data selected by the user and passes it in a hashtable to an invoked business object. In each case the Object Designer was used to create a business object containing a data cache object (agaData) that is bound to the data source object. The invoked business object instantiates the data source object by calling invokeQuery() on its AgaData, passing the database and table values.

For example, the invoked object for the publish function calls the data source object twice--once on the AgaData for the source database and once on the AgaData for the destination database:

  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); 

Data flow   Top of page

  1. The data source object dsoEditTableData gets the database and table parameters from the caller and does the following:

  2. JDBCDataSource instantiates other objects as part of the AgiDataSource implementation.

  3. JDBCDataSource responds to the AgiRowCursor methods from the caller:

Coding the data source object    Top of page

When dsoEditTableData object is instantiated by the caller, it first gets the database (as an AgiDataBase object) from the server and gets a database connection from the SilverStream connection pool. Then it creates a ResultSetMetaData variable to hold information about the structure of the table.

The code then uses a series of vectors and arrays plus a hashtable to get and store information about the table metadata. First it gets the number of columns from the ResultSetMetaData object, then it loops through the object, storing other metadata in vectors. Then the code loops through the vectors and copies the data to a corresponding set of arrays. Next the arrays are passed to a hashtable.

Finally, the code instantiates a new JDBCDataSource, passing the hashtable as a parameter, and sets this object as the data source for this invocation. Here is the code for the invokeQuery event:

  public void invokeQuery(AgoDataSourceEvent evt) throws Exception 
         Hashtable htParms = (Hashtable) evt.getParameter(); 
        // Get the parameters from the caller 
        String dbName = (String) htParms.get("DBNAME"); 
        String tableName = (String) htParms.get("TABLENAME"); 
        // Pull the database 
        AgiDatabase adb = evt.getServer().getDatabase(dbName); 
          if (adb==null) throw new Exception  
            ("DATABASE: " + dbName + " was not available!"); 
        // Open a connection to the db 
        java.sql.Connection conn = adb.getConnection (true); 
        java.sql.Statement stmt = conn.createStatement(); 
        // Get the table metadata 
        java.sql.ResultSet rs = stmt.executeQuery  
           ("select * from " + tableName + " WHERE (1=2)"); 
        java.sql.ResultSetMetaData rsm = rs.getMetaData(); 
        int colCount = rsm.getColumnCount(); 
        Vector vNames = new Vector(); 
        Vector vTypes = new Vector(); 
        Vector vAuto = new Vector(); 
        // Loop through rsm and store the results in the vectors 
        for (int x=0; x<colCount; x++) 
          // Call local method to get data types 
          char dType = getDataTypeCode (rsm.getColumnType(x+1)); 
            if ( dType != DatatypeCodes.TYPE_BYTEARRAY) 
              vNames.addElement (rsm.getColumnName(x+1)); 
              vTypes.addElement (new Character (dType)); 
              vAuto.addElement (new Boolean (rsm.isAutoIncrement 
        // Clean up the db connections and set things to null  
        rsm = null;   
        rs = null; 
        stmt = null; 
        adb.releaseConnection (conn); 
        // Now start building the arrays for JDBCDSO objects 
        int size = vNames.size(); 
        String colNames[]  = new String[size]; 
        String colUpdates[]  = new String[size]; 
        boolean canQuery[]  = new boolean [size]; 
        boolean autoIncrement[] = new boolean [size]; 
        char colTypes[]   = new char[size]; 
        for (int x=0; x<size; x++) 
        // Put the vector values in the arrays 
           colNames[x] = (String) vNames.elementAt (x); 
           colUpdates[x] = (String) vNames.elementAt (x); 
           colTypes[x] = ((Character) vTypes.elementAt 
           autoIncrement[x]= ((Boolean) 
           canQuery[x] = true; 
        // Set up the parameters for this data source object 
        Hashtable hshParms = new Hashtable(); 
        hshParms.put ("DBName", dbName);       // ODBC DSN 
        hshParms.put ("TableName", tableName); // Table name 
        hshParms.put ("FieldNames", colNames); // Array of the 
                                               // column names 
        hshParms.put ("AgiDatabase", adb);    // Reference to 
                                              // db object 
        // DATATYPES  - an array with the data type for each column 
        hshParms.put ("DataTypes", colTypes); 
        // CANQUERY  - an array of booleans, where true means  
        // the column may be included in a query 
        hshParms.put ("CanQuery", canQuery); 
        // UPDATE COLUMNS 
        hshParms.put ("Updates", colUpdates); 
        hshParms.put ("AutoIncrement", autoIncrement); 
         // Create a new AgiDataSource and set it as  
         // the new data source 
        JDBCDataSource ds = new JDBCDataSource(hshParms); 
        // Pass back the information about metadata and 
        // autoincrement fields (not used in DataXpress app) 
        Vector vReturn = new Vector(); 

Implementing the AgiDataSource    Top of page

This JDBCDataSource object is responsible for accessing and updating the external data source. It gets instantiated by the dsoEditTableData call to the setDataSource() method in the invokeQuery event.

Declared variables

The JDBCDataSource declares the following variables in the General Declarations section:

  // Save the following across calls ... 
  private AgiDatabase gdataSource= null;// Data source 
  private Connection gConnection= null;// Connection 
  private AgiDataRowFactory growFactory= null;// Data row factory 
  private PreparedStatement gprepStatement= null; 
  private ResultSet gresultSet= null;// Result set 
  private Hashtable ghshParms= null;// Hashtable parameters 
  private String gsQueryString= null;// Query String 
  private String gsOrderBy= null;// Order by columns 
  int giColumnCount= 0;// Column count 


The JDBCDataSource object imports these packages in the Imports section:

  import com.sssw.rt.util.*;// For the AgiDataRowFactory 
  import com.sssw.srv.api.*;// For the AgiDatabase 
  import java.sql.*;// For the connection 
  import java.util.*;// For the hashtable 


The constructor for the object is defined in the General section. It has this declaration:

  public   JDBCDataSource(Hashtable phshParms) 

where phshParms is a hashtable that contains the database, table, and column information.

    For more information, see Implementing AgiDataSource in the data source objects chapter of the Programmer's Guide.

JDBCBandDescriptor    Top of page

The JDBCBandDescriptor describes the columns provided by a particular band for an AgiRowCursor object. The AgiDataUpdateRow calls the AgiDataSource getBandDescriptor() method to create the AgiBandDescriptor object.

The JDBCBandDescriptor is instantiated by JDBCDataSource object when it calls the getBandDescriptor() method.


The constructor for the JDBCBandDescriptor object takes a hashtable:

  public JDBCBandDescriptor(Hashtable phshParms) 

where Hashtable is a hashtable containing database, table, and column information.

    For more information, see Implementing AgiBandDescriptor in the data source objects chapter of the Programmer's Guide.

JDBCDataUpdateRequest   Top of page

The JDBCDataUpdateRequest is a utility class that extends AgoDataUpdateRequest. It acts as the basket for gathering the rows that the client has updated, and then does the work necessary to update those rows to the external data source.

JDBCDataUpdateRequest gets instantiated by JDBCDataSource during the prepareUpdateRequest() method. It in turn creates the transaction handle object and the AgoDataUpdateRow. The JDBCDataUpdateRequest object relies on the AgoDataUpdateRow and the AgiTransactionHandle.


This implementation defines the following variables in the General Declarations section. This is done in order to save the hashtable across calls and save the transaction object as well.

  // Save the hashtable parameters across calls 
  private Hashtable ghshParms; 
  // Save the transaction object calls 
  private JDBCTransactionHandle ghTransaction; 


The object defines these imports in the Imports section for the connection, hashtable, and database exceptions.

  import java.sql.*;  // For the connection 
  import java.util.*; // For the hashtable parameters 
  import com.sssw.rt.util.*; // For database exceptions 


As part of the mechanics for updating the external data source, the update request has to create the rows that need to be updated. It uses the createUpdateRow() method to do so. There are two variants of this method.

One variant lets you create rows for insert or delete. The return statement is as follows:

  return new JDBCDataUpdateRow(char0, agiDataSource1, 
  m_rowFactory, agiDataRow2);




The kind of operation (from CommandCodes)


The data source object


The row factory


The data row

The other variant is used for rows that have been modified:

  return new JDBCDataUpdateRow(char0, agiDataSource1, 
   m_rowFactory, agiDataRow2, agiDataRow3);




The kind of operation (from CommandCodes)


The data source object


The row factory


The old data row


The new data row


The data update request is responsible for defining the beginning and end of a transaction and specifying whether the transaction should be committed or aborted. This is how the beginTransaction() method is implemented:

  protected com.sssw.rt.util.AgiTransactionHandle beginTransaction() 
      throws com.sssw.rt.util.AgoApiException 
  // Create our transaction handle object, passing the  
  // hashtable parameters, and return it to the caller 
     ghTransaction = new JDBCTransactionHandle(ghshParms); 
     return m_transaction; 


In this implementation, transactions are committed by calling the commitTransaction() method. This is how it is implemented:

  protected void 
  agiTransactionHandle0) throws com.sssw.rt.util.AgoApiException 
     // Cast the parameter to our transaction handle object, 
     // get our connection, commit it, then release it 
       JDBCTransactionHandle hTransaction =  
       (JDBCTransactionHandle) agiTransactionHandle0; 
       Connection connection = hTransaction.getConnection(); 
     catch (SQLException ex)  
     throw new AgoSystemDatabaseException(ex, GS_THIS + 
           ".commitTransaction():  Exception during commit"); 

JDBCDataUpdateRow   Top of page

The JDBCDataUpdateRow is a utility class that extends the AgoDataRow object. It holds the AgiDataRow objects that represent the rows that the user has added, deleted, or modified. The AgiDataSource object is saved in the constructor so that it can call the getBandDescriptor() method to get the AgiBandDescriptor object to use when building SQL.

The JDBCDataUpdateRow object must implement the checkStatement() and prepareAndExecuteUpdate() methods. These methods do the work required by the row.


The object defines these variables in the General Declarations section:

  public java.util.Hashtable ghshParms; 
  public String GS_UPDATE_NAMES []; 
  public boolean GB_AUTO_INCREMENT[]; 
  // Save the data source and transaction handle across calls 
  AgiDataSource gdataSource; 
  char gcOperation; 
  AgiDataRow growMain; 
  AgiDataRow growUpdate; 
  int m_parmCount = 0; 


This object imports the following:

  import java.sql.*;// For the connection 
  import com.sssw.rt.util.*;// For the prepareAndExecuteUpdate 
  import com.sssw.srv.api.*;// For the AgiDatabase 
  import java.math.*;// For BigDecimal 


This object defines two constructors, one for the insert and delete operations and another for the update operation:

  // Constructor with 4 parameters (for inserts and deletes) 
  public JDBCDataUpdateRow(char kind, AgiDataSource dataSource, 
     AgiDataRowFactory rowFactory, AgiDataRow row) 
        throws AgoApiException  
    super(kind, dataSource, rowFactory, row); 
    gdataSource = dataSource; 
    gcOperation = kind; 
    growMain = row; 
    growUpdate = null; 
    ghshParms = ((JDBCDataSource)gdataSource).getParameters(); 
    GB_AUTO_INCREMENT = (boolean[])ghshParms.get ("AutoIncrement"); 

Here is the constructor for updates:

  // Constructor with 5 parameters (for updates) 
  public JDBCDataUpdateRow(char kind, AgiDataSource dataSource, 
    AgiDataRowFactory rowFactory, AgiDataRow oldRow,  
       AgiDataRow newRow) 
         throws AgoApiException  
    super(kind, dataSource, rowFactory, oldRow, newRow); 
    gdataSource = dataSource; 
    gcOperation = kind; 
    growMain = oldRow; 
    growUpdate = newRow; 
    // Updatable columns 
    ghshParms = ((JDBCDataSource)gdataSource).getParameters(); 
    GS_UPDATE_NAMES = (String[])ghshParms.get("Updates"); 
    if (GS_UPDATE_NAMES == null) 
      System.out.println("NAMES ARE NULL"); 
      System.out.println ("NAMES ARE NOT NULL"); 

prepareAndExecuteUpdate() method

This method does most of the work. It uses several methods defined in this class to build, compile, and bind the appropriate SQL statement that deletes, inserts, or updates the row. (This is based on the requirements of the external data source, which is a relational database in this example.) The CommandCodes passed by the client determine the insert, update, or delete operation.

Here is the code for prepareAndExecuteUPdate():

  public com.sssw.rt.util.AgiDataRow prepareAndExecuteUpdate 
  throws com.sssw.rt.util.AgoApiException  
      // Cast our transaction handle to our transaction handle 
      // object and get the connection 
      JDBCTransactionHandle hTransaction = (JDBCTransactionHandle) 
      Connection connection = hTransaction.getConnection(); 
      // Based on the operation type, build, compile, and  
      // bind the appropriate SQL statement 
      PreparedStatement prepStatement = null; 
        switch (gcOperation) {  
        case CommandCodes.CCINSERT: 
          // Build an insert statement 
          prepStatement = buildInsertStatement (connection); 
        case CommandCodes.CCUPDATE: 
          // Build an update statement 
          prepStatement = buildUpdateStatement (connection);   
        case CommandCodes.CCDELETE: 
          // Build a delete statement 
          prepStatement = buildDeleteStatement (connection); 
          System.out.println(GS_THIS +  
           " prepareAndExecuteUpdate():   
             Unsupported update operation type '" 
             + gcOperation + "' on row " 
             + growMain.getRowKey()); 
            throw new AgoUnrecoverableSystemException(GS_THIS +  
            ".prepareAndExecuteUpdate():  Unsupported  
                update operation type '" + gcOperation  
                + "' on row " + growMain.getRowKey()); 
        } // End of switch block 
       // Execute the PreparedStatement 
          int iResult = prepStatement.executeUpdate(); 
        if (iResult != 1) 
          throw new AgoDataConcurrencyException 
            (GS_THIS + "prepareAndExecuteUpdate(): 
            executeUpdate failed.\r\nRow " + growMain.getRowKey() 
            + " must have been modified"); 
      }  // End of try block  
      catch (SQLException ex)  
        System.out.println(GS_THIS + ".prepareAndExecuteUpdate() 
           SQLException:  " + growMain.getRowKey()  
           + ": " + ex.getMessage()); 
        throw new AgoSystemDatabaseException(ex, GS_THIS +  
            SQLException occurred during update of row "  
          + growMain.getRowKey() + "\r\nDescription: "  
          + ex.toString() + "\r\nDetail:  " + ex.getMessage()); 
      } // End of catch block 
       } // Method 
      return null;   // No server-modified rows here 

JDBCTransactionHandle   Top of page

The JDBCTransactionHandle is a utility class that implements the AgiTransactionHandle interface.

A transaction handle represents an in-progress database transaction--whatever that might mean for the underlying external data source. You can use it to store information that you need to pass to each updatable row during the update operation. In this example, the transaction handle gets created by the AgoDataUpdateRequest.beginTransaction() method.

This implementation includes a constructor, a getConnection() method, and a releaseConnection() method.


The JDBCTransactionHandle defines two variables used by the class Constructor:

  // Save the connection across calls 
  private Connection gConnection; 
  // True if we were able to connect using the connection pool 
  private AgiDatabase gAgiDatabase; 


The object imports these packages:

  import java.sql.*;// For the connection 
  import java.util.*;// For the hashtable parameters 
  import com.sssw.srv.api.*;// For the AgiDatabase and AgiServer 


The constructor uses the AgiDataSource object from the hashtable to save the connection across calls to the database:

  public JDBCTransactionHandle(Hashtable phshParms) 
          ghshParms = phshParms; 
          gAgiDatabase = (AgiDatabase) ghshParms.get("AgiDatabase"); 
     catch (Exception excpError) 
          System.out.println("====> Constructor failed!"); 


This method gets the database connection and saves it to a member variable:

  public Connection getConnection() 
      if (gbTrace) 
         System.out.println(GS_THIS + ".getConnection()"); 
        gConnection = gAgiDatabase.getConnection(true); 
        return gConnection; 


This method closes the connection, returning the connection to the connection pool:

  public void releaseConnection() 
      if (gbTrace) 
        System.out.println(GS_THIS + ".releaseConnection()"); 
      if (gConnection != null)  
       gConnection = null; 
    catch (Exception excpError) 
  System.out.println("====> releaseConnection 

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