Page History
The programming example below (TESTTRANS) demonstrates transaction processing. The The purpose is to write (and/or delete) 10 rows, with record IDs from 90 to 99, in the CUSTOMERS table. (You can use the CUSTOMERS table in the EXAMPLES application, if you wish.) Before Before each row is written, it is locked. Only if each row is successfully locked, written, and unlocked will all the rows be committed to the database. If If the FAIL argument is passed with a value of 1, row 96 is locked before the loop writing the 10 rows, causing the write to row 96 to fail. This This will cause the entire transaction to be rolled back, so that none of the 10 rows is written to the database, thus returning the database to its condition before the transaction started.
...
Create the TESTTRANS stored procedure by pasting the code below. See See the comments for how to run this stored procedure.
Code Block |
---|
compile subroutine testtrans(branch,fail) declare subroutine transact, control_on, control_off * run from the exec line. * write records with no fail - run testtrans "WRITE",0 - this should create 10 new records * write records with fail - run testtrans "WRITE",1 - this should fail and rollback * delete records no fail - run testtrans "DEL",0 - should delete 10 records * delete records with fail - run testtrans "DEL", 1, - shoud fail and rollback all records. control_on("CUSTOMERS" , 0 ) /* turn control on for the table */ error = 0 open "CUSTOMERS" to datafile then sqlstate = '' transact( 2 , sqlstate) /* begin transaction */ if fail = 1 then lock datafile,96 then * cool, we are holding a lock to make the batch of transactions fail. end end for i = 90 to 99 lock datafile,i else error = 1 end while error = 0 if branch = "WRITE" then write "THIS IS A TEST" on datafile,i else error = 1 end end else delete datafile,i else error = 1 end end unlock datafile,i else error = 1 end next i branch = error if error = 0 then * commit transaction transact( 1 , sqlstate) /* commit */ end else * rollback transact( 0 , sqlstate) /* rollback */ end end if fail = 1 then unlock datafile,96 then * cool, unlock as required. end end control_off("CUSTOMERS" , 0) /* control is turned off */ return |