I recently had the opportunity to rewrite one of our major data entry systems. This system processes payments on a monthly basis, either individually or submitted as a group under an administration company - rather like companies that have a third party handle their payroll.

The Problem
In some cases these group submissions run into thousands of individual payments that can take even the best data entry person a week or better to enter. In the past the problem was addressed either by leaving the workstation running with the payment in flight or saving an incomplete and unbalanced set of payments for later edits and corrections.

Neither of these solutions really solved the problem and on more than one occasion several days of work were lost due to a system outage or power failure. In addition, I wanted to create a system that wouldn't allow an out-of-balance payment to be saved to the database as these were particularly hard to ferret out under the old system. That, of course, eliminated the option of storing an incomplete payment entry for later.

What I needed was a way to take a "SnapShot" of the payment entry window that could be used at a later time to restore the window and allow an in-flight payment entry to be resumed.

The Solution
Fortunately, PowerBuilder's DataWindow provided the ideal tool to solve this problem. The GetFullState() DataWindow function is usually used in distributed processing. According to the PowerBuilder Help description, GetFullState() "retrieves the complete state of a DataWindow or DataStore as a BLOB". A BLOB (Binary Large Object) is a data type that can hold an unbounded amount of raw binary data. The counterpart to GetFullState() is the SetFullState() function that's used to restore a DataWindow (or DataStore) to the state that existed by applying the information stored in a BLOB created by GetFullState().

GetFullState() creates the BLOB with the DataWindow object specification, the contents of the four data buffers (Original, Primary, Filter, and Delete), and the status flags used by PowerBuilder to control the operation of the DataWindow. Since most of the data that I would need to restore is contained in either a DataWindow or a DataStore, these two functions make it easy to re-create those controls. The only remaining values that I would need to restore are any instance variables used in the window. These will be stored in the same location as the GetFullState() BLOBs.

Storage
The next piece of the puzzle is where to store this information so it can be used to reconstruct the DataWindow or DataStore sometime in the future. My first use of the BLOB data type was in a different application that actually stored the contents of the PBD files for the app on the database. These BLOBs were then downloaded to the system workstation whenever a new version was detected. Not much different from what I wanted to do here. For those who are interested, I described that system in a previous article, "Automating Your Software Installation and Updates" (PBDJ, Vol. 4, issue 12).

For Adaptive Server Enterprise the Image data type can be used to store BLOB information up to a maximum of 2,147,483,647 bytes of information.

Oracle has the Long Raw data type and Adaptive Server Anywhere uses Long Binary. One major difference between Sybase and Oracle is that Oracle limits the number of Long Raw data type columns to one per table. Sybase allows multiple columns to use the Image data type. All things considered, the Sybase capability makes it much easier to associate multiple BLOBs that will result from a SnapShot.

It's important to note that the columns that are defined to hold BLOB data cannot be accessed using normal SQL commands. The UPDATEBLOB and SELECTBLOB SQL statements take care of all data access. There's no INSERTBLOB statement. A SQL INSERT has to be used to create the row with the non-BLOB columns first, followed by the UPDATEBLOB.

The SnapShot Table
I first created a table with the columns in place that I would need to identify the ownership of a particular SnapShot. This will vary from application to application - however my system required the items shown in Table 1.

I then created columns to hold each of the instance variables in the Payment Entry Window. As this was a very complex system, I won't repeat every variable here - just a few:

Federal_id varchar(9) null
Payment_date smalldatetime null

I chose to allow null values here so that a SnapShot could be taken at any stage of the payment-entry process.

I finished up by defining image columns for each of the DataWindow/DataStores that I needed to reconstruct during the SnapShot Restore process. These I identified by the control name used in the window.

Dw_payor_information image null
Dw_payment_header image null
Dw_payment_detail image null

The Window Open Event
So far so good. Now I'll add the mechanism to the window that will allow me to take the SnapShot. Because I want the database activity for the SnapShots to be independent from the rest of the window activity, I need to define a transaction object strictly for SnapShot activity.

First the instance variable for the transaction object:

Transaction    itr_snapshot

in the open event for the window:

Itr_snapshot = CREATE transaction

itr_snapshot.DBMS = "SYC Sybase
System 10/11"
itr_snapshot.Database =
SQLCA.Database
itr_snapshot.LogPass = SQLCA.LogPass
itr_snapshot.ServerName =
SQLCA.ServerName
itr_snapshot.LogId = SQLCA.LogId
itr_snapshot.DBParm = "Release='11'"

and in the close event:

IF IsValid(itr_snapshot) THEN
DESTROY itr_snapshot
END IF I set up the payor_payment_snapshot table on the same database that the application uses to store payment information. Under different circumstances the independent snapshot transaction object would allow me to use a different database if that was desirable.

Accessing the SnapShot Table
To update the non-BLOB columns of the table I need to create a DataStore capable of updating the fields in question. In my application this is ids_snapshot using dataobject "d_"windowname"_snapshot.

I use a window-defined function to actually "take" the SnapShot. Taking the SnapShot will be very specific to the contents of that window and therefore well suited to being written into the window itself instead of a global function or NVO (see Listing 1).

I use a similar window-defined function to restore the window from a SnapShot (see Listing 2). Basically we reverse the process - retrieving the SnapShot information and applying it to the window before the user sees it. The only problem here is if one of the DataWindows has changed (say a new column) since the SnapShot was taken. There are two ways to deal with this, which I'll cover later.

Putting the Pieces in Place
Implementation of the SnapShot functionality was fairly straightforward. A command button was placed on the window; Cb_snapshot executed the wf_snapshot() function, which checks to see if this window has a SnapShot ID already. The presence of an ID would mean that we want to update a SnapShot that already exists on the database. If the ID is null, the wf_snapshot() creates a new snapshot row on the database and records the ID as an instance variable.

In the open event of the window that contains SnapShot functions, a check is made to see if the user has any SnapShots currently on the database. If any are found, a response window is opened to allow the user to either select the SnapShot to be restored or cancel SnapShot selection, resulting in a new payment entry being started.

The response window also allows a user to delete any SnapShot entries that are no longer needed. This doesn't happen very often as the window automatically deletes a SnapShot once the payment entry is successfully saved.

Pitfalls
I mentioned earlier that if the DataWindow object has been changed between the taking of the SnapShot and the restoration, this will cause a problem. Specifically the SetFullState() function will return a 2, indicating that the DataWindow object being restored is different than the DataWindow object that is currently assigned to the DataWindow or DataStore. This usually means that a column has been added or removed from the DataWindow object. This will probably cause problems with the application that's trying to continue processing.

I've dealt with this situation two different ways:
1.   I use a version indicator on the SnapShot to tell the application which DataWindow object to assign using simple IF - THEN - ELSE logic.
2.   I keep a copy of the old executable around for use with the old SnapShots. This takes some coordination with the users but it's easier to do if there are a large number of DataWindow changes to deal with.

Summary
With SnapShot we provided our data entry personnel with the ability to record the current state of any payment entry that they're working on. I tell people the same thing I tell users about backups: "How much data do you want to rekey in the event of a system failure?" Nice thing about SnapShot is that it is fire-and-forget. Fire alarm? Hit SnapShot and exit the building. Coffee break? SnapShot will complete while you're stirring in the sugar.

I also have people do a SnapShot immediately before doing a save. If there's a problem with the save or a programming error, I have a fighting chance of saving the work if I have that SnapShot. This has happened on a number of occasions - usually with the largest payments (isn't that always the way it seems to go?).

One last benefit relates strictly to the developer. How many times have we had to do significant work setting up a situation to test either a new feature or a program fix? With SnapShot I can get the program set up once - do the SnapShot on the test system and re-create the test scenario as many times as I need - simply by choosing to restore the SnapShot.