Implementing the ClientApp ADO Plan Database

Eric Hartwell - January, 2000

Tables and Records

Since a MyApp Plan is simply a collection of plan elements, it could be stored in a single table where each record defines a single plan element. The table would have a column for each element attribute: Name, Caption, Type, Template, Value. All values for the element would be stored as a single XML string.

At the other extreme, each element could be stored in a separate table, with a separate record for each value.

The actual layout is a tradeoff between the needs of database size and speed of access, against the granularity needed for update, audit, synchronization, and replication.

MyApp loads the entire plan data store into memory for quick access, since a function like cross reference can touch almost all plan elements. However, MyApp itself is usually only concerned with a limited subset of the plan data, and it may be better to limit the amount of memory used by only loading those plan elements that are actually needed.

Amazing but true - the MyApp plan database can be built, saved, and used without using a database.

No, it's not just smoke and mirrors. ADO lets you create a recordset independently, without any database connection required. You can start with a totally blank recordset, define the fields, and load data into them. You can work with this recordset (queries, updates, etc.), still without any database connection. Finally, you can save the recordset to disk as a single binary or XML file. The next time you need the data, you can load your recordset from the file - all without a database connection!

Of course, when we want to use a database (for read or write), all we have to do is specify the connection and the table name instead of the file name - the code doesn't know or care about the difference. ADO even has built-in support for multi-user conflict detection and resolution. This is not implemented in phase 1, mostly because we need to decide if or how to audit plan changes first.

For the sake of simplicity, the initial implementation will use:
1. A single table with a single record per element.
2. No database. Plan data will be stored as a persisted ADO recordset in a binary file with a .MyApp extension. 

Data Source

MyApp has many ways to get the plan data. The IMyAppPlan::Load(BSTR Connect) method parses the connection string to determine what kind of data store is being used, and where to get it from. If the connection came from the MyApp password database, it contains database information as well as plan information. In this case, the PPATH substring specifies the path or connect string for the plan files. The information is packed into a comma-delimited string which looks like this:

CALCN=TEST_CALC,CONN="srvr=2:orcl;uid=po7pwd=po7",LOG=,
MAST=TEST_MEMBERSHIP,PLID=5500000TCP,PNAME="MyApp Sample",
PDIR=D:\PLANS\,PPATH=D:\PLANS\TEST,
ICONN=,IMAST=,AUDIT=B_TEST_AUD,HIST=,USER=TEST,
REST=TEST_REST,SPECF=,SPECV=,ALLOW=,DENY=,ACC=21ffffe,DACC=5

First we look for a PPATH variable, and extract it if there. Then we look for plan data sources in the following order:

1. ADO binary file persisted recordset: No "=" character in the input string, file extension is ".MyApp"
2. ADO XML file persisted recordset: No "=" character in the input string, file extension is ".xml"
3. Legacy format plan files: No "=" character in the input string,  assume string is path to 'f' file
4. ADO provider: Assume string is an ADO connection string

Note: We might also use a more generic XML format, which would be simple and more explicit.

Table Specification

The plan table has a separate column for each of the basic Plan Element attributes.

Other attributes, such as Count and Changed, are maintained by the plan component rather than stored in the database.

Note that the element's data is typically stored in XML format. Since MyApp uses the same XML DTD as ADO does for its persisted recordsets, these values could be physically stored with hierarchical recordsets instead, providing more granularity with no effect on the plan interface.

Load

MyApp uses an ADO disconnected recordset which contains one record for each plan element.

Load from Database

Opening a disconnected recordset is much the same as opening a standard recordset, with a few extra parameters specified (see Resynchronising Disconnected ADO Recordsets).

The Visual Basic equivalent of the C++ code would look something like this:

Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
cn.ConnectionString = DBConnect
cn.Open
Set rs.ActiveConnection = cn

rs.CursorLocation = adUseClient

rs.LockType = adLockBatchOptimistic
rs.Supports   adUpdateBatch

rs.CursorType = adOpenStatic

Dim strSQL as String
strSQL = "Select * From PlanStore"
rs.open strSQL,,,, adCmdText
Set rs.ActiveConnection = Nothing

Load from Persisted Recordset

A persisted recordset is stored as a single  disk file that can be modified off-line, or even sent to another user via floppy disk or email. You open it using the recordset's Open method with adCmdFile for the Options argument:

rs.Open Source:="D:\Path\PlanStore.MyApp", Options:=adCmdFile
rs.Open Source:="D:\Path\PlanStore.xml",     Options:=adCmdFile

This binary persisted recordset format is the new standalone MyApp plan file format.

Getting Plan Elements from the Recordset

Individual plan elements may be retrieved from the recordset all at once, or one-at-a-time as required. 

• For the all-at-once approach, the simplest and most efficient method is to use the GetRows method to load all the data into a variant array. This has the advantage of preloading the data into BSTRs, which are used internally in MyAppPlan. In addition, the array can be scanned with a simple index loop.
   
• For the one-at-a-time approach, use the Find method to locate the desired element (a query on a query result!), then GetRows to load the matching row into a variant array.

The resulting data is exactly what the Legacy library conversion routines produce.

Save

MyApp uses an ADO disconnected recordset which contains one record for each plan element.

Note that an ADO recordset can be created and manipulated independently of any database.

Save to Persisted Recordset

A persisted recordset is stored as a single  disk file that can be modified off-line, or even sent to another user via floppy disk or email. You save the recordset to disk using the recordset's Save method. You can save as a binary file with any extension (smaller, faster), or as a pure XML file.

The Visual Basic equivalent of the C++ code would look something like this:

rs.Save "D:\Path\PlanStore.MyApp", adPersistADTG
rs.Save "D:\Path\PlanStore.xml",   adPersistXML

This binary persisted recordset format is the new standalone MyApp plan file format.

Save to Database

Marshalling

When updating recordsets from the client to the original database, it is important to consider the Marshal options property of the recordset object. Setting the MarshalOptions property can improve performance when modified remote data is marshaled, for updating back to the original database. There are two options that can be set for this property:

• AdMarshalAll - [Default]  - Returns all rows to the server.
• AdMarshalModifiedOnly - Returns only modified rows to the server.

Since MyApp usually changes only a few elements at a time, we will always use AdMarshalModifiedOnly. This will send only as many records as necessary to the database (see ADO Disconnected Recordsets, Microsoft Transaction Server and Collision).

rs.MarshalOptions = adMarshalModifiedOnly      ' Update only modified rows

Conflict Detection

Plan data is rarely saved to the plan database, usually only by the analyst implementing the plan. However, there may be cases where more than one analyst is working on the plan, or the client has been given the capability to modify some of the plan parameters. 

With ADO, it is possible to deal with the problems of collision. ADO offers us three kinds of values that can be used to solve collisions. These values are properties of the recordset object:

• Value - Indicates the value assigned to a field, parameter, or property object.
• OriginalValue - Indicates the value of a field that existed in the record before any changes were made.
• UnderlyingValue - Indicates a field's current value in the database.

When a disconnected recordset is reconnected with the original database, the OriginalValue and the UnderlyingValue are compared. If a difference exists, an error will be raised. One way to prevent this would be to manually compare the OriginalValue and UnderlyingValue for each field in the recordset to see where any conflicts exist before attempting the actual update (see Resynchronising Disconnected ADO Recordsets). This means that the client must reload the current values for the entire recordset first. The obvious disadvantage to this approach is that the download and comparison can be quite time-consuming, especially for a case that rarely happens.

A better solution is to use ADO's built-in support for conflict resolution. There are two ways to do this:

• Error trapping in UpdateBatch with adFilterConflictingRecords to see which records cause a conflict 
• Using code in the RecordChangeComplete event to capture error conditions.

For coding in C++, the error trapping approach is probably the simplest. A Visual Basic version of the code would look like this:

Dim cn As New ADODB.Connection
cn.ConnectionString = DBConnect
cn.Open
Set rs.ActiveConnection = cn

rs.MarshalOptions = adMarshalModifiedOnly

rs.Filter = adFilterPendingRecords  
If Not rs.EOF Then
    On Error GoTo SaveRecordSet_Error
    rs.UpdateBatch adAffectGroup 
End If

SaveRecordSet_Error:

If Err.Number = ERR_UPDATE_COLLISION Then
    rs.Filter = adFilterConflictingRecords
    '...
    'Here's where we look at each conflict
    '...
Else 'Oops - another error
    Err.Raise Err.Number, Err.Source, _
     Err.Description, Err.HelpFile, Err.HelpContext
End If

rs.ActiveConnection = Nothing
Set ResyncRecordset = rs

Conflict Resolution

If a conflict is detected during batch update, it means that another user has modified the same plan element. We could handle this in several ways:

• First-in: Keep changes made by the first user to save them, and ignore changes from the current user.
• Last-in: Changes made by the current user overwrite the first one's.
• Ask the user: Display a list of elements that were changed in both places, and ask what to do about each one (or the whole lot).

In any case, conflict resolution is much more reasonable if we can provide audit information about who made the changes and why the made the changes. ADO already provides us with field-level information about the value in the data store and the value before we changed it, in addition to the new value. This information is best presented in a list with checkboxes so the user can save or cancel each change independently (along with buttons to Save All or Cancel All, or course).

Once all conflicts are resolved, we can rerun the batch update.

Create Recordset

A special case is encountered when converting a Legacy plan to the ADO data format; there is no existing recordset. In this case, we can create and populate a recordset without even using a database.

Dim objRS As ADODB.Recordset                 ' dim a variable of the type ADODB.Recordset
Set objRS = CreateObject("ADODB.Recordset")  ' create an instance of the ADODB.Recordset class
objRS.CursorLocation = adUseClient           ' bring the entire resultset to the client

' add fields to the recordset
objRS.Fields.Append "Name",     adVarChar,   20, adFldKeyColumn
objRS.Fields.Append "Type",     adVarChar,   20, adFldKeyColumn
objRS.Fields.Append "Caption",  adVarChar,     , adFldIsNullable
objRS.Fields.Append "Template", adLongVarChar, , adFldIsNullable
objRS.Fields.Append "Value",    adLongVarChar, , adFldIsNullable
. . .

objRS.Open                                   ' open the recordset so we can add data to it
objRS.AddNew                                 ' add data to the recordset
objRS.Fields("Name") = "VIEW"
objRS.Fields("Type") = "screen"
objRS.Update

The recordset may now be persisted as a binary file, even with no database specified.

References:

• ADO without a data store
• ADO 2.5 Embraces the Web
• ADO Data Shaping
• ADO Disconnected Recordsets
• ADO Disconnected Recordsets, Microsoft Transaction Server and Collision
• ADO VC Tutorial in MSDN Has Compile/Run-Time Errors (Q231351)
• CreateRecordset Function in VC++ (Q190473)
• Data Shaping 101
• Disconnected Recordsets
• Disconnected recordsets, conflict resolution - Professional Active Server Pages 3.0, Wrox Press, ch.10, p.414-416
• How Visual C++ Users Should Read the ADO Documentation
• MarshalOptions Property Example (VC++)
• OriginalValue and UnderlyingValue Properties Example (VC++)
• Open and Close Methods Example (VC++)
• Resolving Conflicts in Disconnected Recordsets
• Resynchronising Disconnected ADO Recordsets
• SQL OLE DB: BLOBs and OLE Objects
• SQL OLE DB: Managing text and image Columns
• UpdateBatch and CancelBatch Methods Example (VC++)
• Using ADO with Microsoft Visual C++
• Using the ADO GetRows Method for Faster Data Retrieval (MSDN, now offline)
• Using Disconnected Hierarchical Recordsets (Q213856)
• Using GetChunk and AppendChunk in Visual C++ (Q189415)
• Using Long Data Types

Revisions:

1. January, 2000 - Initial version