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.
You can run this technique code from:
NOTE First make sure that database is running on your localhost SilverStream server | |
|
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.)
This technique uses the following objects:
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);
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
(x+1)));
}
}
// Clean up the db connections and set things to null
rsm = null;
rs.close();
rs = null;
stmt.close();
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
(x)).charValue();
autoIncrement[x]= ((Boolean)
vAuto.elementAt(x)).booleanValue();
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);
evt.setDataSource(ds);
// Pass back the information about metadata and
// autoincrement fields (not used in DataXpress app)
Vector vReturn = new Vector();
vReturn.addElement(vTypes);
vReturn.addElement(vAuto);
return;
}
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
Imports
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
Constructor
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.
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.
Constructor
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.
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.
Variables
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;
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 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 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) | |
beginTransaction()
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;
}
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
{
JDBCTransactionHandle hTransaction =
(JDBCTransactionHandle) agiTransactionHandle0;
Connection connection = hTransaction.getConnection();
connection.commit();
hTransaction.releaseConnection();
}
catch (SQLException ex)
{
throw new AgoSystemDatabaseException(ex, GS_THIS +
".commitTransaction(): Exception during commit");
}
}
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.
Variables
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;
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
Constructors
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");
else
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
(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
JDBCTransactionHandle hTransaction = (JDBCTransactionHandle)
agiTransactionHandle0;
Connection connection = hTransaction.getConnection();
// Based on the operation type, build, compile, and
// bind the appropriate SQL statement
PreparedStatement prepStatement = null;
try
{
switch (gcOperation) {
case CommandCodes.CCINSERT:
// Build an insert statement
prepStatement = buildInsertStatement (connection);
break;
case CommandCodes.CCUPDATE:
// Build an update statement
prepStatement = buildUpdateStatement (connection);
break;
case CommandCodes.CCDELETE:
// Build a delete statement
prepStatement = buildDeleteStatement (connection);
break;
default:
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();
prepStatement.close();
if (iResult != 1)
throw new AgoDataConcurrencyException
(GS_THIS + "prepareAndExecuteUpdate():
executeUpdate failed.\r\nRow " + growMain.getRowKey()
+ " must have been modified");
hTransaction.releaseConnection();
} // End of try block
catch (SQLException ex)
{
hTransaction.releaseConnection();
System.out.println(GS_THIS + ".prepareAndExecuteUpdate()
SQLException: " + growMain.getRowKey()
+ ": " + ex.getMessage());
throw new AgoSystemDatabaseException(ex, GS_THIS +
".prepareAndExecuteUpdate():
SQLException occurred during update of row "
+ growMain.getRowKey() + "\r\nDescription: "
+ ex.toString() + "\r\nDetail: " + ex.getMessage());
} // End of catch block
finally
{
} // Method
return null; // No server-modified rows here
}
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.
Variables
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;
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 uses the AgiDataSource object from the hashtable to save the connection across calls to the database:
public JDBCTransactionHandle(Hashtable phshParms)
{
try
{
ghshParms = phshParms;
gAgiDatabase = (AgiDatabase) ghshParms.get("AgiDatabase");
}
catch (Exception excpError)
{
System.out.println("====> Constructor failed!");
}
}
getConnection()
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;
}
releaseConnection()
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)
{
try
{
gAgiDatabase.releaseConnection(gConnection);
gConnection = null;
}
catch (Exception excpError)
{
System.out.println("====> releaseConnection
failed!");
}
}
}