Created On:  2010-03-08

Problem:

In the most common environments, Vision files are accessed and modified by Runtime and AcuXDBC at the same time.  This can cause access conflicts, especially when Transactions are involved.

Resolution:

Acucobol-GT documentation reports that “A transaction is a group of related file operations that are treated as an indivisible unit. The purpose of defining such transactions is to ensure that related files can be restored to a consistent state when errors occur.”

Here it is how transactions can be turned on in an AcuXDBC environment and how they work.
In the acuxdbc.cfg file, these are the variables that turn on transactions:
transactions            on
transaction_processing  on
To enable transaction logging and encryption, turn on these variables:
# logging           off
# log_encrypt       off
# log_device        off
# log_file          C:\temp\transactions.log
# temp_dir          C:\temp
# log_buffer_size   512
When transactions are enabled, AcuXDBC will manage them directly according with Vision libraries.

This is a sample of what happens during a transaction.

To connect to data using a Data Source Name, use this command line:
C:\>odbcsql -d myDSN -u system –p manager
Where odbcsql is a .bat (or .sh) file located in AcuGT/bin directory, “myDSN” is the name of the Data Source, system is the user and –p will provide the security password.
From odbcsql prompt, type /? to show the available commands and to identify the ones necessary to start and commit or rollback transactions.

SQL (/? for help) ==> /?

Commands  Parameters      Description
--------  --------------  -----------------------------------------------
/x        c|r[ u ]        Commit or Rollback transaction and, if 'u'
                          specified, start a read/write transaction.

To turn on transaction type this command:
SQL (/? for help) ==> /x cu

Now it is possible to access and modify data within a transaction:
SQL (/? for help) ==> select * from pets;
SQL (/? for help) ==> delete from pets where patient_id = 1;

If another session of odbcsql is run, it is easy to test that this record does not appear in the query as if it was deleted.

From the first odbcsql session, it is possible to COMMIT or ROLLBACK the DELETE statement:

To commit, type
SQL (/? for help) ==> /x c

To rollback, type
SQL (/? for help) ==> /x r
 
When a record is modified during a transaction, it will appear LOCKED until a commit or rollback will be run.
SQL (/? for help) ==> update pets set animal_type = 'Dog' where patient_id = 2;

A query on the same record will show this message:
SQL (/? for help) ==> select * from pets where patient_id = 2;

  PATIENT_ID PATIENT_NAME         ANIMAL_TYPE          BREED                          TREATMENT    OWNER_ID
 ----------- -------------------- -------------------- ------------------------------ --------- -----------
select * from pets where patient_id = 2
***** ERROR: [TOD][ODBC][GENESIS]VISION: Fetch error,Record locked

This record appears locked for runtime, too.
An attempt to modify this record will show an error “93 - File locked by another user.”

If the same table is linked inside Ms Access, it will not be possible to modify this record.
MS Access will show an error message box that says:
“ODBC—update on a linked table ‘PETS’ failed.
[TOD][ODBC][GENESIS]VISION: Fetch error,Record locked (#-5)

Commit or Rollback operation will release this record for future accesses.


 

Incident #2434576