Eric Hartwell's InfoDabble

 
Welcome to Eric Hartwell's InfoDabble
About | Site Map
Home Tech Notes Apollo 17: Blue Marble Apollo 17 Flight Journal   Calendars About me  
 Exoware .....  

Tech Note 

Implementing the MyApp 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.

Column Description Type Size
Name Unique identifier for this Type of element ("VIEW", "CALC" etc.) adChar 20
Type Class of element - used with Name to create a unique identifier for the element. The class determines how the element is interpreted and used ("plan", "screen", "rule", "calc" etc.) adChar 20
Caption Title of the element ("Basic Member Data") adVarChar <1000
Template Information used to interpret or display the element. For a screen this is the HTML template; for a form this is the Word mail-merge document; for a rule it's the XSL template adLongVarChar unlimited
Value Plan-specific information for this element adLongVarChar unlimited
Parameters Plan-specific information for this element (e.g. compiled rule) adLongVarChar unlimited
Audit (?) Audit information: user ID, change summary or details (?) adLongVarChar unlimited

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

Open a client-side recordset in UpdateBatch mode, then disconnect the recordset by setting its ActiveConnection to Nothing. (Note: don't close the connection - when a connection is closed it closes all its associated recordsets.) 

rs.CursorLocation = adUseClient

Setting the CursorLocation property to adUseClient instructs ADO to use a client-side cursor, which marshals the recordset to the client. If the CursorLocation property is set to adUseServer (which is the default setting) then the data would remain on the server and be served to the client as needed. When using adUseServer an open connection to the database is required.

rs.LockType = adLockBatchOptimistic
rs.Supports   adUpdateBatch
The LockType property affects the behavior of the UpdateBatch method. The UpdateBatch method will apply any pending added , updated or deleted records from the recordset to the database. Using a LockType of adLockBatchOptimistic , the UpdateBatch method allows you to commit all the pending changes made in the client-side recordset applying all the changes to the database at one time. In addition to specifying the LockType , we explicitly tell the recordset object we want it to support batch updating (UpdateBatch method) to transmit the changes back to the database as a group. Setting the Supports property to the adUpdateBatch cursor option does this.
rs.CursorType = adOpenStatic

The UpdateBatch method is only valid when the LockType property is set to adLockBatchOptimistic and the CursorType property is set to either a keyset or static cursor. If we use a keyset cursor, it will only work with tables that have a unique index.

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

Now, all that is left to do is open the recordset and disconnect it from the database.

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
The first thing that must always be done with a disconnected recordset is to reconnect it to the database. Therefore, we create and open a connection, then set the recordset's ActiveConnection property to the open connection.
rs.MarshalOptions = adMarshalModifiedOnly
Returns only modified rows to the server.
rs.Filter = adFilterPendingRecords  
If Not rs.EOF Then
    On Error GoTo SaveRecordSet_Error
    rs.UpdateBatch adAffectGroup 
End If
Update the master table with the offline changes. adFilterPendingRecords restricts  the recordset to records that have changed but that have not been sent to the server yet. Set adAffectGroup so the batch only sends the records in the current filter (ie only those that have changed).
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
Error -2147217864 means that conflicts occurred. 

adFilterConflictingRecords restricts the recordset to record(s) whose underlying value no longer matches the original value. These are the conflicting records.

Note that it's possible a plan element was deleted by another user, resulting in a delete collision.

 

rs.ActiveConnection = Nothing
Set ResyncRecordset = rs
We set our ActiveConnection to nothing in order to disconnect the recordset. 
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

Last modified: April 09, 2007

Creative Commons License

Unless otherwise noted, this work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License

 

Site Map | About Me