Using the Transact subroutine

vince

After a year and a half with OpenInsight, I feel I ought to be able to work out how to use the Transact subroutine, but so far no success. Perhaps I missed something in basic training. I start with the programmer's reference:

Transact subroutine
Description: Controls transaction processing.
Note: The TRANSACT volume (in the RevSoft\OI32bit folder) must be attached for transaction control to be enabled.

I gather from this note that I should be able to see the TRANSACT volume in the Database Manager. In this discussion I can see a screenshot of the DB Manager with a TRANSACT volume in the left pane, but in my DB Manager it does not exist.
I found a way to attach such a volume: Create new volume and new table in it; without a table in it, the new TRANSACT volume does not appear in the DB Manager. Or should it appear automatically when the temporary tables are created, provided that I have a Transact folder in my OpenInsight working directory?

These are my concurrency environment settings:


My code starts with:
Control_On(dTbl :@FM: sTbl, 0) Transact(2, trnState) I then try to roll back with:
Transact(0, trnState) Control_Off(dTbl :@FM: sTbl, 0) But whether or not the Database Manager shows a TRANSACT volume, my rollback code fails to prevent writing to my tables (source table name in sTbl variable, destination table name in dTbl). trnState always is zero. When I pause my program between the two Transact statements, the Database Manager still shows no change (no temporary tables in a TRANSACT volume).

I have no idea if the Control_On/Control_Off syntax is correct; nothing in the prog. ref. about how to string together multiple table-names. (I tried with just one table, but always the real table was written despite the rollback statement.) Nor have I any idea, really, how to interpret the TRANSACT note in the reference. I can find no RevSoft\OI32bit folder, for instance. Unfortunately no one in this company has used OpenInsight's transaction processing. Any help will be much appreciated.

DonBakke

@vince - This wasn't covered in the training class.

The transaction volume is not normally visible in the Database Manager. That is, OI does not add it to your Database for you since this is meant to be a "behind the scenes" feature. Not too dissimilar to the way OI creates temp files for sorting.

Below is a simple routine I wrote to test if transaction processing will work. Hopefully it will fill in some blanks in your understanding:

Function Test_Transact(Void) $insert LOGICAL $insert SQLSTATES_STAT Equ TransactionRollback$ to 0 Equ TransactionCommit$ to 1 Equ TransactionInitialize$ to 2 Declare subroutine Control_On, Control_Off, Transact Table = 'CUSTOMERS' KeyIDStart = 1 KeyIDEnd = 10 Result = '' Error = '' // Add transaction control (i.e., PROTECT.MFS) to this table. Set the TempFlag to True$. If this table is to be // permanently under transaction control, then set transaction control separately and save the database. Control_On(Table, True$) // SQLState will be populated with a 0 if successful, or an error code if not successful (see SQLSTATES_STAT insert). SQLState = '' Transact(TransactionInitialize$, SQLState) If (SQLState EQ 0) OR (SQLState EQ SQL_TRANS_ALREADY_ON_STAT$) then For KeyID = KeyIDStart to KeyIDEnd Open Table to hTable then Lock hTable, KeyID then Read Record from hTable, KeyID then Record<2> = 'BBB' Write Record to hTable, KeyID else Error = 'Error writing ' : KeyID end end else Error = 'Error reading ' : KeyID end Unlock hTable, KeyID end else Error = 'Error locking ' : KeyID end end Until Error NE '' Next KeyID SQLState = '' If Error EQ '' then // No errors were encountered. Commit the transactions. Transact(TransactionCommit$, SQLState) If SQLState NE 0 then Result = 'Unable to commit the transaction. Error: ' : SQLState end end else // Errors were encountered. Rollback the transactions. Transact(TransactionRollback$, SQLState) If SQLState NE 0 then Result = 'Unable to rollback the transaction. Error: ' : SQLState end end end else Result = 'Unable to initialize the transaction. Error: ' : SQLState end Return Result

vince

I do appreciate the example transaction function you provided, @Don. On first read I could see nothing different between your code and mine apart from better error-handling in yours (specifically, SQLState: I will definitely get my code up to the standard) and using Control_On with the tempflag set to true.

And yet your code rolled back the transaction when I expected it to, but mine did not.

Eventually I noticed that your Control_On and Transact calls were outside all the table manipulations, but in mine I initiated transaction processing immediately before the first Write and tried to force rollback of the transaction immediately after the last Write. My logic was that if I cannot open the tables, lock the records and read the fields, there is no point in attempting a transaction. The mistake I made was to include Control_On and Control_off at the same places. They have to be called before and after doing anything with the tables.

So I left the Transact calls where they were but moved Control_On to a point before opening the tables. At last I got the transaction rollback for which I was testing. And of course, now that I look closer at the transaction-processing example provided by Revelation in the programmer's reference, I see finally that it, too, calls Control_On before opening the table!

DonBakke

@vince - I'm glad that helped. It's vindication of my practice to have sample code that demonstrates concepts that might not be well known or commonly used.

vince

Sorry for the delayed response, Don (thanks to getting pushed on to other tasks when four fifths finished and having to complete in my spare time).

Yes, clarity is key, and your example has that. And without your coding example I was unaware of SQL_TRANS_ALREADY_ON_STAT$. Speaking of examples, I think this is not the first time I have asked a question without posting even a stripped-down version of my code, believing the error to be somewhere else. Next coding question I will post an example of the problem.

I do have one more question, about calling Control_On. The prog. ref. mentions that with tempflag true, "control features are added only temporarily, until the table is next accessed." Since accessing the table the next time can remove the control features, does this mean that others on the network who access the table can turn off control before I complete the transaction?

DonBakke

@vince - It might be easier for me to just explain what Control_On / TempFlag does at a low level and then you should be able to answer your own questions.

Transaction processing depends upon a special MFS being installed to your table(s). This is called PROTECT.MFS.

PROTECT.MFS by itself does nothing. It is used by the TRANSACT routine to help facilitate the transaction processing itself. Thus, this is why you can have multiple transactions from different tables occurring in your process but only those that are controlled (i.e., have PROTECT.MFS installed) will have their transactions queued up.

The TempFlag determines whether PROTECT.MFS is permanently installed (i.e., the media map is updated) or if it is just for your current session. Your use cases will determine whether a permanent installation is warranted, but in general you would make it permanent if a given table will always be under transaction processing management.

To be honest, I am not sure when PROTECT.MFS is removed if installed temporarily. I agree with you that the documentation suggests this happens when the current transaction process has completed, so perhaps this is done when the Commit/Rollback is executed, but I also would not be surprised if it remains until you call Control_Off or until OI is reset.