In AREV we had TRANSACTION Rollback, On, and Commit. I know there's a Control On/Off in OI but I see no corresponding statement to the Rollback and Commit.

Am I missing something?

Dave,

I know there's a Control On/Off in OI but I see no corresponding statement to the Rollback and Commit.

Transaction control is possible in OpenInsight.

There are three functions:

<code>
Control_On(Table_List, Ctl_Act)  - table_list is @fm-delim'd, ctl_act should be ON or TEMP
Control_Off(Table_List, Ctl_Act) - table_list is @fm-delim'd, ctl_act should be OFF or TEMP_OFF
Transact(Action, SQLState)

</code>

There are two inserts: TRANSACT_CONTANTS and CONTROL_CONSTANTS.

Here is the TRANSACT_CONTANTS insert:

<code>
*
* CONSTANTS FILE FOR THE SQL TRANSACTION CONTROL PROCEDURES
*

* TRANSACT function - miscellaneous equates
*
EQU OFF$   TO 0
EQU ON$    TO 1

* Names of the SQL transaction files
*
EQU COMMIT_FILE_NAME$ TO "REVCOMMITLOG"  ;* Prefix for the Commit Log  (CL)
EQU TRANS_FILE_NAME$  TO "REVTF_"      ;* Prefix of the transaction files  (TF)
EQU TRANS_LOG_NAME$   TO "REVTL_"      ;* Prefix of transaction log  (TL)

* Smart filing system equates for transactions
*
EQU SMART_FS_INDEX$   TO 0        ;* Smart filing system no trans file needed
*
* Record prefix in the commit log
*
EQU MACHINE_NAME_ENTRY$ TO "%ID%" ;* Prefix of record name for machine entry in commit log

* Transaction File Codes - 1st character of the record
*
EQU DELETE_CODE$     TO 'D'  ;* Signifies DELETE in transaction file
EQU CLEAR_CODE$      TO 'C'  ;*     ''    CLEARFILE    ''        ''
EQU WRITE_CODE$      TO 'W'  ;* This code is either an UPDATE or INSERT
EQU DELETE_RECORD$   TO '%%DELETEMASK%%'  ;* This record contains values
                                           * which are used in SQL_DISTINCT.

* Field numbers for the TRANS.INFO@ variable and DEADLOCK log record
*
EQU TRANS.INFO.TIME$  TO 1   ;* Start time of transaction
EQU TRANS.INFO.DATE$  TO 2   ;* Start date of transaction.
EQU TRANS.INFO.ACTIVITY$ TO 3  ;* Read/Write activity field.
EQU TRANS.INFO.MASK$  TO 4   ;* Contains the file name whose delete mask is in use
EQU STATION.NAMES$    TO 4   ;* Stations which are in contention
EQU READ.VALUE$       TO 1   ;* Activity value of Read Statement
EQU WRITE.VALUE$      TO 3   ;* Activity value of Write/Delete Statement
EQU CLEAR.VALUE$      TO 5   ;* Activity value of Clearfile Statement

* Constants which pertain to locking and deadlock detection
*
EQU SHARED_LOCK_MARK$ TO 2   ;* This mark indicates a shared lock record in the lock file
EQU EXCL_LOCK_MARK$   TO 1     ;* This mark indicates an exclusive lock record in the lock file
EQU FILE_LOCK_MARK$   TO 1     ;* This mark indicates a file lock
EQU REC_LOCK_MARK$    TO 2      ;* This mark indicates a record lock
EQU TRANS_LOCK_NAME$  TO "SQL_LF."  ;* Prefix for a lock file (LF)
EQU DEADLOCK_FILE_NAME$ TO "SQL_DF"  ;* Name of centralized deadlock file (DL)
EQU VICTIM_YOUNGEST$  TO 0    ;* Selects the youngest as a victim
EQU VICTIM_ACTIVITY$  TO 1    ;* Selects the transaction with lowest activity
EQU UPDATE_RECORD$    TO "%%SQL_UPGRADE_LOCK%%"  ;* Record name when upgrading
                                                  * shared lock to table lock.
EQU SEMA_LOCK_NAME$   TO "SEMAPHORE"  ;* Pseudo-file handle for a semaphore lock


* TRANSACT function - parameter values for the ACTION parameter
*
EQU ROLLBACK$          TO 0
EQU COMMIT$            TO 1
EQU BEGIN_TRANSACTION$ TO 2
EQU RESTART$           TO 3
EQU RESTART_SUPPRESS$  TO 4   ;* This will suppress all STATUP on-screen messages
EQU ROLLBACK_SUPPRESS$ TO 5   ;* This will suppress all STATUP on-screen messages
EQU COMMIT_SUPPRESS$   TO 6   ;*  ''   ''     ''      ''
EQU SET_TRANS$         TO 7   ;* Parameters passed in SQLSTATE

* Locking functions - parameter values for the OPERATION parameter
*
EQU OP_MODIFY$   TO 0        ;* This statement is changing data
EQU OP_SELECT$   TO 1        ;* This statement is only querying data

* Equates for the different consistency levels
*
EQU CL_4$ TO 4     ;* 4 defined under the SQL2 standards
EQU CL_3$ TO 3     ;* 3 releases shared locks early (at post-query/op time)
EQU CL_2$ TO 2     ;* 2 defined under the SQL2 standards
EQU CL_1$ TO 1     ;* 1 like 0, but locking for update is performed (the
                    * lock is released at post-query/op time).
EQU CL_0$ TO 0     ;* 0 defined under SQL2 standards except ROLLBACK and
                    * COMMIT operations are not functional and NO locking is
                    * performed.

* FILE.NAMES@ variable
EQU FILE_NAME$       TO 1  ;* List of filenames is first field
EQU CLEAR_FILE$      TO 2  ;* List of associated clear file marks is the second
EQU DELETE_FLAG$     TO 3  ;* A flag which marks a file has had deletions.
EQU VOLUME_NAME$     TO 4  ;* Volume where the file exists - used for Restart
EQU SMART_VOLUMES$   TO 5  ;* Ordered list of smart AFS's which contain their
                            * own transaction control. Ordering based on
                            * intelligence - Most intelligent are first.

* LOCK_INFO@ variable
*
EQU TOTAL$           TO 1  ;* Total lock counts for file/record & impl/expl locks
EQU FILE_NAMES$      TO 2  ;* Lock file names
EQU FILE_LOCKS$      TO 3  ;* List of codes for shared or file locks
EQU FILE_LOCK_TYPES$ TO 4  ;* Mode of the lock
EQU REC_KEYS$        TO 5  ;* List of keys for record locks
EQU REC_LOCKS$       TO 6  ;* Type of record lock used
EQU REC_LOCK_TYPES$  TO 7  ;* Mode of this record lock

* LOCK_INFO@ variable - lock types for FILE_LOCK_TYPES$ and REC_LOCK_TYPES$
*
EQU LOCK_TYPE_EXPL$ TO 0 ;* Explicit lock type
EQU LOCK_TYPE_BUMP$ TO 1 ;* Explicit lock type which has been bumped - the
                          * unlock will be delayed until commit time.
EQU LOCK_TYPE_IMPL$ TO 2 ;* Implicit lock type

* Possible values for the TRANS.CONTROL@ variable
*
EQU TRANS_OFF$       TO 0  ;* Transaction is turned off
EQU TRANS_ON$        TO 1  ;* Transaction is turned on
EQU TRANS_ACTIVE$    TO 2  ;* Transaction is active

* Equates for the INSERT_LIST_DATA@ variable
*
EQU SELECT_RN_DATA$  TO 1  ;* This is the first field of this variable
EQU KEY_LIST_DATA$   TO 2  ;* This is the second field of this variable

* Subfields of the SELECT_RN_DATA$ field of the INSERT_LIST_DATA@ var
*
EQU TOTAL_LISTS$     TO 1  ;* Total number of lists (on disk & in memory)
EQU LISTS_PROCESSED$ TO 2  ;* Total number of lists processed by READNEXT

EQU MAX_INSERT_LIST_LEN$  TO 55000 ;* Maximum size of total list
EQU MAX_FILE_LIST_LEN$ TO 6     ;* Max size of a list per file
* Source Date: 16:42:32  12 JAN 1993  Build ID: OI*1.0.10  Level: 2.0

</code>

Here is the CONTROL_CONSTANTS insert:

<code>
EQUATE CTL_ACT_DEFAULT$     TO 0
EQUATE CTL_ACT_ON$          TO 1
EQUATE CTL_ACT_OFF$         TO 2
EQUATE CTL_ACT_TEMP$        TO 3       ;* TEMPORARY PROTECT
EQUATE CTL_ACT_TEMP_OFF$    TO 4       ;* Temporary protect off
*
EQUATE CTL_POPUP_ON$        TO 5       ;* COMMUTER POPUP FOR WINDOW
EQUATE CTL_POPUP_OFF$       TO 6       ;* COMMUTER POPUP FOR WINDOW
EQUATE CTL_PRESAVE$         TO 7       ;* PRESAVE FOR WINDOW

EQUATE CTL_OK$              TO 0
EQUATE CTL_NOT_ERR$         TO 1       ;* ATTEMPT TO REMOVE FROM UNCONTROLLED FILE
EQUATE CTL_ALREADY_ERR$     TO 2       ;* ATTEMPT TO ADD TO ALREADY CONTROLLED FILE
EQUATE CTL_INVALID_VOLUME$  TO 3       ;* VOLUME DOES NOT EXIST
EQUATE CTL_INVALID_FILE$    TO 4       ;* NULL OR INVALID (! OR DICT) FILE NAME
EQUATE CTL_UNATTACHED_FILE$ TO 5       ;* FILE NOT ATTACHED WHEN REQUIRED.
EQUATE CTL_FILE_DNE$        TO 6       ;* SPECIFIED FILE/VOLUME DOES NOT EXIST
EQUATE CTL_MEDIA_WRITE_ERR$ TO 7       ;* ERROR WRITING TO MEDIA MAP
EQUATE CTL_FILE_VOL_MISMATCH$  TO 8    ;* (TEMP ONLY) - FILE ATTACHED IS NOT IN SPECIFIED VOLUME
EQUATE CTL_FILES_WRITE_ERR$ TO 9       ;* ERROR WRITING TO FILES FILE
* Source Date: 17:34:38  31 JUL 1989  Build ID: AREV*1.13.74  Level: 2.0

</code>

Cameron Purdy

info@revelation.com

Sorry for the mis-post. I pressed Enter by mistake.

Here' s my test code:

COMPILE SUBROUTINE TEMP(VOID)

declare function transact

call control_on("SOD" , 1) /* turn control on temporarily for the table */

SQLState='

result=transact( 2 , SQLState) /* Line 6 -]Begin transaction */

result=transact( 1 , SQLState) /* Commit */

call control_off("SOD" , 1) /* Control is turned off */

This is the error message I get :

ENG0010: TEMP, line 6. Variable has not been assigned a value.

I cannot figure out from the inserts what SQLState shouuld be when passed in. Is that the problem?

Looks like your function should be a subroutine.

Example from David Goddard follows:

COMPILE SUBROUTINE TESTORYX(BRANCH,FAIL)

DECLARE SUBROUTINE TRANSACT, Control_On, Control_Off

* Run from the EXEC line.

* Write Records with no fail - RUN TESTORTX "WRITE",0 - this should create 10 new records

* Write records with fail - RUN TESTORYX "WRITE",1 - This should fail and rollback

* Delete records no fail - RUN TESTORYX "DEL",0 - Should delete 10 records

* Delete records with fail - RUN TESTORYX "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) /* Line 6 -]Begin transaction */

IF FAIL=1 THEN

	LOCK DATAFILE,96 THEN

	   * COOL, we are holding a lock to make the bach 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) /* Commit */	

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

View this thread on the forum...