This sample extends the framework code by making use of the Recordset, one of the core ADO/WFC classes. Recordsets can be opened much like a Connection, and can be used to execute data operations (such as add, remove, update) and to marshal tabular data to and from business objects.
// Establish a global variable to hold the open Recordset
// used in run(), createRecordset(), and useRecordset().
private Recordset rs;
public boolean run()
{
boolean fSuccess = true;
fSuccess = createRecordset();
useRecordset ();
rs.close();
return fSuccess;
}
boolean createRecordset ()
{
Connection c = new Connection();
c.open ("server=aDSN; ", "aName", "aPWD");
rs = new Recordset();
rs.setActiveConnection ("dsn=aDSN;database=pubs");
rs.setSource ("select * from authors");
rs.setActiveConnection (c);
rs.setCursorType (AdoEnums.adOpenStatic);
rs.setCursorLocation (AdoEnums.adUseClientBatch);
rs.setLockType (AdoEnums.adLockBatchOptimistic);
rs.setCacheSize (10);
rs.setMaxRecords (1000);
rs.open();
return true;
}
boolean useRecordset()
{
// Extract all of the Recordset properties and
// print out the <attribute, value>-tuples.
AdoProperties props = rs.getProperties();
int cProps = props.getCount();
for( int iProp = 0; iProp < cProps; iProp++ )
{
AdoProperty p = props.getItem( iProp );
System.out.println("[" + p.getName() + " = " + p.getString() + "]");
}
// Perform simple navigation and boundary checking
// (BOF= beginning of file, EOF= end of file).
rs.moveFirst();
rs.movePrevious();
if (! rs.getBOF())
return false;
rs.moveLast();
rs.moveNext();
if (! rs.getEOF())
return false;
int recordCount = rs.getRecordCount();
System.out.println ("record count = " + recordCount);
rs.addNew( new Object[] {"899-46-2036", "O'Maley", "Zoltar",
"67 Seventh Av.", "Salt Lake City", new Boolean (true),
"801 826-0752", "UT", "84152","409-56-7098"});
rs.update();
rs.delete(1);
rs.addNew();
rs.cancelUpdate();
rs.moveFirst();
rs.delete(1);
rs.cancelBatch();
rs.setMarshalOptions (AdoEnums.adMarshalModifiedOnly);
rs.setPersistFormat (AdoEnums.adPersistADTG);
if (rs.getMarshalOptions() != AdoEnums.adMarshalModifiedOnly)
return false;
if (rs.getPersistFormat() != AdoEnums.adPersistADTG)
return false;
rs.moveFirst();
Object bmk = rs.getBookmark();
rs.moveLast();
rs.setBookmark(bmk);
rs.movePrevious();
if (! rs.getBOF())
return false;
rs.addNew();
if (rs.getEditMode() != AdoEnums.adEditAdd)
return false;
rs.cancelUpdate();
// Requery the result, overriding any local changes
// and completely replacing the result.
rs.requery();
return true;
}
The createRecordset method creates a Recordset by opening a connection and executing a query against the data source referenced by the System DSN:
rs = new Recordset();
rs.setActiveConnection ("dsn=aDSN;database=pubs");
rs.setSource ("select * from authors");
The ActiveConnection property is used in much the same way that the ConnectionString property is utilized on the Connection object — to specify a data source to connect to. The Source property is an overloaded property that can either reference a SQL DDL/DML string or a Command object. In this case, a string is used to initialize the Recordset. Note that no data has been fetched yet, since the Recordset has not been opened. It is also possible to set the ActiveConnection property of a Recordset to refer to a Connection object instance, as in rs.setActiveConnection(c).
The remaining properties are used to specify aspects of the type of cursor required by the application:
rs.setCursorType (AdoEnums.adOpenStatic);
rs.setCursorLocation (AdoEnums.adUseClientBatch);
rs.setLockType (AdoEnums.adLockBatchOptimistic);
rs.setMaxRecords (1000);
rs.open();
Note that in the absence of explicit property settings, any Recordset created by an open method invocation will result in a static, forward-only, server cursor — the equivalent of a generic ANSI cursor. However, you may need a cursor that supports scrolling and batch behavior; you may need a “disconnected” cursor where you can apply changes locally before deciding whether to propagate those changes back to the data source. Applications can be written to capitalize on this local caching of data, which provides a better solution for Internet applications that cannot afford to keep server resources tied up. The particular property settings chosen above request a static, local cursor that does not keep any locks on server resources. Instead, it uses optimistic concurrency control when it tries to update the data source. The MaxRecords property specifies an upper bound on the amount of rows that should be returned. Finally, the Recordset is opened through the open method. If the connection information has been specified properly, the Recordset is populated with up to 1000 rows of data.
The useRecordset method begins with an iteration over the Recordset properties that were set in the createRecordset method. The properties are returned in a collection, AdoProperties, which is then traversed. Each attribute name of the Recordset is printed, followed by the value of the specific property. This type of information may be considered “component metadata” — information about the state of the component, rather than on what it contains.
AdoProperties props = rs.getProperties();
int cProps = props.getCount();
for( int iProp = 0; iProp < cProps; iProp++ )
{
AdoProperty p = props.getItem( iProp );
System.out.println("[" + p.getName() + " = " + p.getString() + "]");
}
The next block of code consists of simple navigation commands. If the Recordset has a scrollable cursor, it supports the following navigational methods: moveFirst, moveNext, movePrevious, and moveLast. Recordsets also provide a bookmark object so that a position may be bookmarked and navigated back to quickly and efficiently. This requires you to use code similar to the following:
rs.moveFirst();
Object bmk = rs.getBookmark();
rs.moveLast();
rs.setBookmark(bmk);
rs.movePrevious();
A bookmark has an “opaque” structure, but two bookmarks can be compared to determine if they reference the same location. This is useful to writers of visual components such as data grids and other complex data-bound components. In the code above, the bookmark is set for the current row pointed to by the Recordset. The current position is then changed to the final row in the set. Then, the bookmark is used to set the position back to where the Recordset was pointing before the position was moved to the final row. Finally, the Recordset is moved back from the new position, placing it before the first row in the set. Recordsets have two special “positions” or states: just before the first row and just after the last row. These states are called BOF and EOF, respectively, and are often used in iteration constructions to terminate navigation. Testing for the values of these boolean properties is quite common in ADO/WFC programming and familiar to programmers from DAO and RDO.
The next cluster of operations performed on the open, populated Recordset revolve around data manipulation, such as adding rows, deleting rows, accepting local updates, and canceling these local changes. The advantage of the local, disconnected cursor can be seen in such circumstances. The programmer has wider discretion to modify local, non-persistent data, rather than alter production data in the data source. This allows for a more efficient partitioning of application logic between the client, the application server (if utilized), and the data source.
rs.addNew( new Object[] {"899-46-2036", "MindFlayer", "Zortan",
"67 Seventh Ave.","Salt Lake City", new Boolean (true),
"801 826-0752", "UT", "84152", "409-56-7098"});
rs.update();
rs.delete(1);
rs.addNew();
rs.cancelUpdate();
New rows are added to the Recordset in the form of arrays of Object values. The “shape” of the array should match that of the Recordset. In this case, it is for an author row. Data type checking is performed on the values, and if a type coercion is not supported for a particular column, then an exception (AdoException) will be thrown by the addNew method. Applications may choose to catch this run-time exception, or allow for the core exception handler to catch the condition. This usually means aborting the current process, so you are encouraged to use proper try/catch blocks around such operations. The delete call above marks the first row of the Recordset as deleted. The row is not actually purged from the Recordset until commit is called. (For more information about commit, see the Transaction sample.) Rows that are marked as deleted enter a “row deleted” state and are not visible to the user unless the cancelUpdate method is invoked. New rows may be appended to the Recordset using the addNew method, which creates a row with null values for all fields. The update method is used to actually “push” the local changes to the data source.
Next, two Recordset properties are set to specify how data is marshaled. Data marshaling refers to the packaging, transfer, and unpackaging of data across process and/or machine boundaries. ADO/WFC supports distributed object invocation/operation and data marshaling over protocols such as HTTP 1.0/1.1 and DCOM. The following relevant properties are set for the Recordset:
rs.setMarshalOptions(AdoEnums.adMarshalModifiedOnly);
rs.setPersistFormat(AdoEnums.adPersistADTG);
The MarshalOptions property specifies that when it is time to marshal the Recordset back to a business object or to the data source, only the modified and new data will be sent. If you have a large result and only end up modifying a small minority of rows, this makes perfect sense, assuming that your business rules (objects) do not require the full result set for processing. The PersistFormat property tells the ADO run time to package the rows of the Recordset in a specific, optimized format for transfer — the ADTG tablegram streaming format defined by Microsoft. These properties are activated only when the Recordset is “pushed” from the client to the middle tier or data source.
Finally, the requery method is introduced in this sample. This method has the effect of purging the local Recordset cache and replacing it with the most recent results of the query or SQL DML used to create the Recordset. This is useful if information has become stale on the client. A simple scenario is to refresh a monitor display that lists the current prices of traded securities. ADO/WFC makes it easy to “restart with latest data.” Unlike other data access models, getting the latest data is as straightforward as requerying your Recordset.
The last step in this sample, after returning from the useRecordset method, is to close and release the resources of the Recordset. This is an important habit to get into, especially in your Java business objects when you use ADO/WFC for data access programming.