{{tag>category:"OpenInsight Specific"}}
[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]]
==== Is there a TRANSACTION statement correspondent in OI? (OpenInsight Specific) ====
=== At 01 DEC 1997 08:33:54PM Dave Pociu wrote: ===
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?
----
=== At 02 DEC 1997 02:05PM Cameron Revelation wrote: ===
Dave,
[i]I know there's a Control On/Off in OI but I see no corresponding statement to the Rollback and Commit.[/i]
Transaction control is possible in OpenInsight.
There are three functions:
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)
There are two inserts: TRANSACT_CONTANTS and CONTROL_CONSTANTS.
Here is the TRANSACT_CONTANTS insert:
*
* 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
Here is the CONTROL_CONSTANTS insert:
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
Cameron Purdy
info@revelation.com
----
=== At 02 DEC 1997 02:59PM Dave Pociu wrote: ===
----
=== At 02 DEC 1997 03:02PM Dave Pociu wrote: ===
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?
----
=== At 25 FEB 2003 10:50PM Emmanuel Carydis wrote: ===
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
[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=4CC8CC0E8B8754DE85256561000898DC|View this thread on the forum...]]