For the most part I understand the principles of both COM+ and ADO transactions. What I require is to execute a series of stored procedures that may be rolled back at any time (ORACLE and SQL Server 2K). My investigations on COM+ tell me not to run transactions longer than a minute but in this case I require transactions to be running for extended periods, as long as a day or perhaps even longer.
The task requires me to further develop a recent WIP solution written in Visual Basic that synchronizes data in GIS system to non-GIS, business tables within an extended transaction. Currently the existing software solution is designed to extend a third party, spatial GIS (Geographic Information System) object framework. This GIS software allows the user with an editing environment so as to edit polygons among other spatial object types. The GIS object model framework exports a series of interfaces that expose on_create, on_change, on_delete events for each class of each spatial object and further exports edit session interfaces providing the state of the users edit session once again through events.
The spatial object data is persisted in ORACLE or SQL server data tables in a proprietary format such that it is recommended by the GIS vendor not to modify data table structure, as the GIS system must independently manage this on its own and may change table names and structure frequently. This limitation thus will not allow one to apply user-based triggers directly on object tables themselves. The WIP application currently simulates triggers as described below.
To facilitate the event mapping of spatial object data to non-GIS business tables a second client application provides a mechanism to map the attribute field value from the spatial object's data table to the input and/or output parameters of a selected stored procedure within the external non-GIS business database table(s). The client tool allows many spatial to stored procedure mappings to be edited against each object class, and persists the mapping details into the GIS object class structure as XML.
When a GIS editor begins to edit an object class type, the XML mapping details are acquired so that as the object fires through their respective class creation/change/delete events the SP mapping details are which provides all the necessary details on how to execute the appropriate SP for that class and event. A simple ADO exercise at this point in the event procedure executes the appropriate stored procedure, passing the values from the fields/attributes of the polygon in to the appropriate parameters of the chosen stored procedure.
This all works, however not within transactions.
By now you may see where this is going. If the user decides not to save the changes, I would like to rollback all of the changes made within the previously executed stored procedures. I know when a user begins and exits the edit session as well if they drop their edit changes.
My initial thoughts were to create a COM+ server that was launched at the start of their edit session, and was act as the portal for all stored procedure calls. This server would support transactions thus in the event the user decides not to save their edit session changes, the com server would be signaled to rollback all data transactions that occurred through it, or all edit session related transactions. As I initially pointed out, it would appear COM+ is perhaps not a likely candidate to provide such long transactional control, this perhaps is my question.
I am looking for a nice solution to this problem.
