How to implement the AgiDataSource interface to access and update data from and to any data source.
You can run this technique code from:
NOTE First make sure that database is running on your localhost SilverStream Server | |
See the chapter on using data source business objects in the Programmer's Guide |
This application demonstrates all of the SilverStream objects that you must implement to create an updateable DSO (also called a setResultSet DSO). This technique documents some, but not all, of the methods that you must implement.
For a complete description of all of the methods you need to implement, see the chapter on using data source business objects in the Programmer's Guide
This application implements the following objects:
frmRDBEmployeeDSO
A SilverStream form containing an AgcData control called dataRDBDSO. The dataRDBDSO object is bound to the DSO called DSORDBEmployees. This form invokes DSORDBEmployees.
DSORDBEmployees
A SilverStream data source object. It instantiates a utility object, called objRDBDataSource, that implements the SilverStream AgiDataSource interface, and sets the data source.
A utility class that implements the com.sssw.rt.util.AgiDataSource interface. It obtains the data from the external data source, formats it in the appropriate row and column format and populates the server-side data buffer for download to the client.
objRDBSingleRowEnumerator
A standard Java utility class that returns the next available row. It is used by the objRDBDataSource.getNextRows() method, one of the methods that is part of the AgiDataSource implementation.
A utility class that implements the com.sssw.rt.util.AgiBandDescriptor interface. It provides important column information.
A utility class that extends the com.sssw.rt.util.AgoDataUpdateRequest object. It gathers the updated records from the AgcData on the client and contains methods that define how the updates should be committed to the external datasource. It defines methods that provide the mechanics of an update.
A utility class that extends the com.sssw.rt.util.AgoDataUpdateRow object.
A utility class that implements the com.sssw.rt.util.AgiTransactionHandle interface. It connects and disconnects from the database.
This diagram illustrates the relationship among these objects.
The form RDBEmployeeDSO displays records from the employees table. The user can navigate the data set and can also add, delete, or update the data set. The interface looks like this:
In the FormActivate event, the invokeQuery() method is called passing parameters as an array of objects. The first parameter is a String that represents and Order by clause, the second represents the Query string. The AgcData calls gotoFirst() to download data to the form.
protected void formActivate()
{
try
{
// Define query parameters
String sOrderBy = "employeeid";
String sQuery = "";
Object[] objParms = new Object[2];
objParms[0] = sOrderBy;
objParms[1] = sQuery;
// Call the DSO, passing the query
Object objRDBResults = dataRDBDSO.invokeQuery(objParms);
// Download data from the AgcData object
dataRDBDSO.gotoFirst();
}
catch (Exception e)
{
System.out.println("Exception calling invokeQuery on
dataRDBDSO:" + e);
agDialog.displayError(e);
}
}
The DSORDBEmployees is the object that describes the data set and provides bind information to the form. The form calls the DSO that is bound to the form's AgcData control. The DSO defines these columns:
The columns that are defined by the DSO must also be defined by the AgiDataSource object (in this case the objRDBEmployees object). The column list does not have to include the entire result set returned by the AgiDataSource; however, any column defined in the data source properties is available for design time data binding.
The Updatable check box is also checked which means that this DSO can perform updates, thus it implements the AgiDataUpdateRequest in addition to the AgiDataSourceListener interface.
Coding the invokeQuery event
In the DSO's invokeQuery event, the DSO instantiates objRDBDataSource and sets it as the data source for the AgcData control.
// ... code from the invokeQuery() event...
// Set up the parameters for the this Data Source Object
Hashtable hshParms = new Hashtable();
// the SilverStream Server
hshParms.put("Server", evt.getServer());
// ODBC DataSource Name
hshParms.put ("DBName", ((AgiDatabase)
evt.getDatabase()).getName());
// user id
hshParms.put("UserID", USER_ID);
//user password
hshParms.put("Password", PASSWORD);
// table name
hshParms.put ("TableName", TABLE_NAME);
// array of the column names
hshParms.put ("FieldNames", COLUMN_NAMES);
// array of the column date types
hshParms.put ("DataTypes", COLUMN_DATA_TYPES)
// array of booleans whether column may be included in a query
hshParms.put ("CanQuery", COLUMN_CAN_QUERY);
// name of autoincrement column, if any
hshParms.put("AutoIncrementColumn", AUTO_INCREMENT);
// See if a parameter was passed by the Form or Page.
// No parameter is required, however, if there is a parameter,
// it expects and array of 2 objects, the first being the
// OrderBy string and the second being the Query string.
Object[] objParms = (Object[]) evt.getParameter();
if (objParms != null)
{
if (objParms.length != 2)
{
System.out.println(this + " - " +
ERROR_INVALID_PARAMETER_LIST);
// If a parm is passed, we require 2 objects
throw new Exception(this + " - " +
ERROR_INVALID_PARAMETER_LIST);
}
hshParms.put("OrderBy", objParms[0]);
hshParms.put("QueryString", objParms[1]);
}
// Create the data source object, set the data source
// object in the event, set the result to ok, and return.
AgiDataSource objRDBDataSource = new
ObjRDBDataSource(hshParms);
evt.setDataSource(objRDBDataSource);
evt.setResult("ok");
return;
The objRDBDataSource is the utility class that implements the com.sssw.rt.util.AgiDataSource interface. It is located in the Objects/com/examples/rdbdso directory.
This objRDBDataSource object is responsible for accessing and updating the external data source. It gets instantiated by the DSORDBEmployees call to the setDataSource() method in the DSOs invokeQuery event.
Declared variables
The objRDBDataSource declares the following variables in the General Declarations section to save the objects across calls to other objects.
private Connection m_connection; // Connection private AgiDataRowFactory m_rowFactory;// data row factory protected ObjRDBBandDescriptor m_bandDescriptor; // bandDescriptor private PreparedStatement m_preparedStatement;// PreparedStatement private ResultSet m_resultSet; // result set private Hashtable m_hshParms; // Hashtable parameters private String m_sTableName; // the table name private String[] m_sColumns; // the column list private String m_sQuery; // Query String private String m_sOrderBy; // Order by columns protected String m_autoIncrementColumn; // Autoinc column name int m_iColumnCount; // Column count
Imports
The objRDBDataSource 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
Constructor
The constructor for the object is defined in the General section. It has this declaration.
public objRDBDataSource(Hashtable phshParms)
Where phshParms is a Hashtable that contains the database, table and column information.
Required methods
When implementing the AgiDataSource there are several method implementations that you must include. You can see the code for these methods when you open objRDBDataSource in in the Examples3_Java database.
For information about these methods, see Implementing AgiDataSource in the Data Source Objects chapter of the Programmer's Guide.
The examples that follows shows how to implement the requery() and getNextRows() methods.
Implementing reQuery()
This method re-executes the current query on the data source:
public void reQuery() throws com.sssw.rt.util.AgoApiException
{
String sODBCDsn = "jdbc:sssw:odbc:";
// First, read the Hashtable dbname and connect
// to the database
try
{
// Close any current connections
close();
// Get the parameters from the Hashtable and connect
// to the database
String sDatabaseName = (String) m_hshParms.get ("DBName");
sODBCDsn += sDatabaseName;
String sUserID = (String) m_hshParms.get ("UserID");
String sPassword = (String) m_hshParms.get("Password");
AgiServer srvServer = (AgiServer) m_hshParms.get("Server");
// Initialize our database object
AgiDatabase database = null ;
// Get our database object from the SilverStream Server
try
{
database = srvServer.getDatabase(sDatabaseName);
m_connection = database.getConnection(true);
m_bConnectionPool = true;
}
// Otherwise, we need to connect to it using
// the SQL DriverManager
catch (Exception e)
{
m_connection = DriverManager.getConnection
(sODBCDsn, sUserID, sPassword);
}
}
}
catch (Exception ex) {
if (DEBUG_ON)
System.out.println("----> reQuery.getConnection failed: "
+ ex.toString() );
// Close any open connection
close();
// Throw the Exception back to the caller
throw new AgoSystemDatabaseException(ex.toString());
}
// Now, build the SELECT statement
try
{
if (m_bandDescriptor == null)
getBandDescriptor();
m_iColumnCount = m_bandDescriptor.getColumnCount();
// Construct the Select statement
String sStatement = "SELECT ";
for (int i = 0; i < m_iColumnCount; i++ ) {
if (i > 0)
sStatement += ", ";
sStatement = sStatement + " " + m_sTableName + "." +
m_sColumns[i];
}
sStatement = sStatement + " FROM " + m_sTableName;
// Append where clause as passed in Query statement
if (m_sQuery != null && !m_sQuery.equals(""))
sStatement = sStatement + " WHERE " + m_sQuery;
// Use Order BY as passed in Query statement or use default
if (m_sOrderBy != null && !m_sOrderBy.equals(""))
sStatement = sStatement + " ORDER BY " + m_sOrderBy;
if (DEBUG_ON)
System.out.println(sStatement);
// Compile the Statement and execute it
m_preparedStatement =
m_connection.prepareStatement(sStatement);
m_resultSet = m_preparedStatement.executeQuery();
}
catch (SQLException excpError)
{
// Close any open connection
close();
// Throw the Exception back to the caller
throw new AgoSystemDatabaseException(excpError.toString());
}
}
Implementing getNextRows()
This method gets the next block of rows from the result set of the data source.
public java.util.Enumeration getNextRows(int int0) throws com.sssw.rt.util.AgoEndOfRowsException, com.sssw.rt.util.AgoApiException
{
if (m_resultSet == null)
throw new AgoEndOfRowsException(null);
try
{
if (!m_resultSet.next())
{
close();
return null;
}
// Create a data row
AgiDataRow row = m_rowFactory.createDataRow(null);
// Loop through the column list and set the data row
//column values with the result set data.
for (int i = 0; i < m_iColumnCount; i++)
{
Object objData = m_resultSet.getObject(i+1);
row.setData(i, objData);
}
// get next row from row enumerator utility class
return new ObjRDBSingleRowEnumeration(row);
}
catch (SQLException ex)
{
System.out.println(M_THIS + " - Error getting data.
\r\nDescription: " + ex.toString());
// Close any open connection
close();
throw new AgoSystemDatabaseException(ex.toString());
}
catch (Exception e)
{
System.out.println("Error getting data.
\r\nDescription: " + e.toString());
// Close any open connection
close();
throw new AgoSystemDatabaseException(e.toString());
}
}
The objRDBBandDescriptor 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 objRDBBandDescriptor is instantiated by the AgiDataSource object when it call the getBandDescriptor() method. This object is located in the Objects/com/examples/rdbdso directory.
Constructor
the constructor for the BandDescriptor object takes a hashtable:
public objRDBBandDescriptor(Hashtable phshParms)
Where Hashtable is a Hashtable containing database, table and column information
Required methods
When implementing the AgiBandDescriptor there are several method implementations that must be included. You can see the code for these methods when you open objRDBBandDescripto in the Examples3_Java database.
For information about these methods, see Implementing AgiBandDescriptor in the Data Source Objects chapter of the Programmer's Guide.
getColumnType()
This is the implementation for the getColumnType() method. The not at the beginning lists the valid return types for the method:
/**
* Method:
* getColumnType()
* Description:
* Return the data type (from DatatypeCodes) of the column.
* Parameters:
* int - the column number
* Returns:
* char - the data type
* Valid DatatypeCodes are:
* DatatypeCodes.TYPE_BOOLEAN
* DatatypeCodes.TYPE_CHAR
* DatatypeCodes.TYPE_BYTE
* DatatypeCodes.TYPE_SHORT
* DatatypeCodes.TYPE_INT
* DatatypeCodes.TYPE_LONG
* DatatypeCodes.TYPE_FLOAT
* DatatypeCodes.TYPE_DOUBLE
* DatatypeCodes.TYPE_BYTEARRAY
* DatatypeCodes.TYPE_STRING
* DatatypeCodes.TYPE_NUMERIC
* DatatypeCodes.TYPE_DATE
* DatatypeCodes.TYPE_TIME
* DatatypeCodes.TYPE_TIMESTAMP
*/
public char getColumnType(int int0)
{
// Get the list of column types and the return the
// data type of the requested column.
char dataTypes[] = (char[]) m_hshParms.get("DataTypes");
if (int0 <= dataTypes.length)
return dataTypes [int0];
else
throw new IllegalArgumentException();
}
The objRDBDataUpdateRequest is a utility class that extends the AgoDataUpdateRequest object. It acts as the basket for gathering the rows that the client has updated, and then doing the work necessary to update those rows to the external data source. This object is located in the Objects/com/examples/rdbdso directory.
It gets instantiated by the object that implements AgiDataSource during the prepareUpdateRequest() method. It in turn creates the transaction handle object and the AgoDataUpdateRow. The objRDBDataUpdateRequest object relies on the AgoDataUpdateRow object and the AgiTransactionHandle objects.
Variables
This implementation defines the following variables in the General Declarations section. This is done in oder to save the Hashtable of parameters across calls and to save the transaction object as well.
// save the Hashtable parameters across calls private Hashtable m_hshParms; // save the transaction object calls private ObjRDBTransactionHandle m_transaction;
Imports
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
createUpdateRow()
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 lets you create rows for insert or delete. The return statement is as follows:
return new objRDBDataUpdateRow(char0, agiDataSource1, m_rowFactory, agiDataRow2);
char0 is the kind of operation (from CommandCodes)
AgiDataSource1 is the data source object
m_rowFactory is the row factory
The other variant is used for rows that have been modified.
return new objRDBDataUpdateRow(char0, agiDataSource1, m_rowFactory, agiDataRow2, agiDataRow3);
char0 is the kind of operation (from CommandCodes)
AgiDataSource1 is the data source object
m_rowFactory is the row factory
agiDataRow2 is the old data row
agiDataRow3 is the new data row
beginTransaction()
The data update request is responsible for defining the beginning and ending of transactions and specifying whether a 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
m_transaction = new ObjRDBTransactionHandle(m_hshParms);
return m_transaction;
}
commitTransaction()
In this implementation, transactions are committed by calling the commitTransaction() method. This is how it is implemented.
protected void
commitTransaction(com.sssw.rt.util.AgiTransactionHandle
agiTransactionHandle0) throws com.sssw.rt.util.AgoApiException
{
// Cast the parameter to our transaction handle object,
// get our Connection, commit it, then release it.
try
{
ObjRDBTransactionHandle hTransaction =
(ObjRDBTransactionHandle) agiTransactionHandle0;
Connection connection = hTransaction.getConnection();
connection.commit();
hTransaction.releaseConnection();
}
catch (SQLException ex)
{
throw new AgoSystemDatabaseException(ex, THIS +
".commitTransaction(): Exception during commit");
}
}
The objRDBDataUpdateRow is a utility class that extends the AgoDataRow object. This object is located in the Objects/com/examples/rdbdso directory.
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 objRDBDataUpdateRow object must implement the checkStatment() and the prepareAndExecuteUpdate() methods. These method actually do the work required by the row.
Variables
The object defines these variables in the General Declarations section.
// save the data source and transaction handle across calls ObjRDBDataSource m_dataSource; AgiDataRowFactory m_rowFactory; AgiDataRow m_rowMain; AgiDataRow m_rowUpdate; char m_operation;
Imports
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
prepareAndExecuteUpdate() method
This method does most of the work. It builds the appropriate SQL to delete, insert, or update the row. (This is based on the requirements of the external data source, which is a relational database in this example.) The command codes passed by the client determine the insert, update, or delete operation.
For a list of command codes see the on-line API help page for CommandCodes.
This is these code for prepareAndExecuteUpdate:
public com.sssw.rt.util.AgiDataRow
prepareAndExecuteUpdate(com.sssw.rt.util.AgiTransactionHandle
agiTransactionHandle0) throws com.sssw.rt.util.AgoApiException
{
// Cast our transaction handle to our transaction handle
// object and get the connection
ObjRDBTransactionHandle hTransaction =
(ObjRDBTransactionHandle) agiTransactionHandle0;
Connection connection = hTransaction.getConnection();
// Based on the operation type, build, compile,
// bind and execute the appropriate SQL statement
PreparedStatement prepStatement = null;
AgiDataRow newRow = null;
try
{
switch (m_operation)
{
case CommandCodes.CCINSERT:
// Build an insert statement
newRow = buildAndExecuteInsertStatement (connection);
break;
case CommandCodes.CCUPDATE:
// Build an update statement
buildAndExecuteUpdateStatement (connection);
break;
case CommandCodes.CCDELETE:
// Build a delete statement
buildAndExecuteDeleteStatement (connection);
break;
default:
throw new AgoUnrecoverableSystemException(M_THIS
+ ".prepareAndExecuteUpdate():
Unsupported update operation type '" +
m_operation + "' on row " +
m_rowMain.getRowKey());
}
}
catch (Exception e)
{
System.out.println(M_THIS + ".prepareAndExecuteUpdate()
SQLException: " + m_rowMain.getRowKey() + ": " +
e.getMessage());
throw new AgoSystemDatabaseException(e, M_THIS +
".prepareAndExecuteUpdate(): SQLException occurred
during update of row " +
m_rowMain.getRowKey() + "\r\nDescription: " +
e.toString() + "\r\nDetail: " + e.getMessage());
}
return newRow;
}
The objRDBTransactionHandle is a utility class that implements the AgiTransaction handle interface. This object is located in the Objects/com/examples/rdbdso directory.
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() and a releaseConnection() method. Note that this example is specific to a SQL RDBMS that uses the driver manager to connect to the database. Depending on the external data source that you are using, you might not implement methods like these. For example, if you are trying to access an object database, you will have to use the vendor's proprietary connection mechanism.
Variables
The objRDBTransactionHandle defines two variables used by the class Constructor:
// Save the connection across calls private Connection m_connection; // true if we were able to connect using the connection pool private boolean m_bConnectionPool = false;
Imports
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
Constructor
The constructor connects to the datasource, and uses the member variables to save the connection across calls to the database. If the database is known by the SilverStream Server, it gets a connection from the connection pool. Otherwise it connects to the database using the SQL Driver Manager:
public ObjRDBTransactionHandle(Hashtable phshParms)
{
m_hshParms = phshParms; // save the parameters across calls
try
{
m_bConnectionPool = false;
String sDatabaseName = (String) phshParms.get("DBName");
String sODBCDsn = "jdbc:sssw:odbc:" + sDatabaseName;
String sUserID = (String) phshParms.get ("UserID");
String sPassword = (String) phshParms.get("Password");
AgiServer server = (AgiServer) phshParms.get("Server");
// Initialize our database object
AgiDatabase dbConnect = null ;
// Get our database object from the SilverStream Server
try
{
dbConnect = server.getDatabase(sDatabaseName);
m_connection = dbConnect.getConnection(true);
m_bConnectionPool = true;
}
// Otherwise, we need to connect to it using
// the SQL DriverManager
catch (Exception e)
{
m_connection = DriverManager.getConnection (sODBCDsn,
sUserID, sPassword);
}
}
catch (Exception excpError)
{
System.out.println("----> Constructor failed!");
}
}
getConnection() method
This method simply gets the database connection and saves it to the member variable
public Connection getConnection()
{
return m_connection;
}
releaseConnection() method
This method closes the connection, first determining whether the connection came from the connection pool on the SilverStream Server or from an SQL Driver Manager:
public void releaseConnection()
{
if (m_connection != null)
{
try
{
if (m_bConnectionPool)
{
AgiServer server = (AgiServer)
m_hshParms.get("Server");
String sDatabaseName = (String) m_hshParms.get
("DBName");
AgiDatabase dbObject =
server.getDatabase(sDatabaseName);
server.getDatabase(sDatabaseName).
releaseConnection(m_connection);
}
else
{
//Release an ODBC SQL DriverManager connection
m_connection.close();
}
}
catch (Exception excpError)
{
System.out.println("====> releaseConnection failed!");
}
m_connection = null;
m_bConnectionPool = false;
}
}