I have a file trigger when a record is updated it writes information to another file to update UPS tracking in an order file. If I use a SQL statement in Universe to insert the record I get a permissions error (Program "UPSTRACK.TRIGGER": Line 81, FATAL:The locks necessary for database operations at the current isolation
level (2) are not held by this process.) If I add the item through ED it works fine updates all the files and adds the new record. Are there different levels of permission when execute SQL from TCL vs. ED?
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Page 1 / 1
I have a file trigger when a record is updated it writes information to another file to update UPS tracking in an order file. If I use a SQL statement in Universe to insert the record I get a permissions error (Program "UPSTRACK.TRIGGER": Line 81, FATAL:The locks necessary for database operations at the current isolation
level (2) are not held by this process.) If I add the item through ED it works fine updates all the files and adds the new record. Are there different levels of permission when execute SQL from TCL vs. ED?
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
level (2) are not held by this process.) If I add the item through ED it works fine updates all the files and adds the new record. Are there different levels of permission when execute SQL from TCL vs. ED?
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Does the trigger subroutine place a record lock on the record being written? In UniVerse, SQL update statements are executed in a transaction so the rules are different when just updating with the ED command. The error message seems to indicate that the appropriate locks are not set. In terms of the TRIGGER, is this a SQL Trigger generated via a CREATE TRIGGER command? Or a trigger created via an indexed based subroutine? And one additional question is what is the setting of ISOMODE in the uvconfig file?
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
Hi JR,
Does the trigger subroutine place a record lock on the record being written? In UniVerse, SQL update statements are executed in a transaction so the rules are different when just updating with the ED command. The error message seems to indicate that the appropriate locks are not set. In terms of the TRIGGER, is this a SQL Trigger generated via a CREATE TRIGGER command? Or a trigger created via an indexed based subroutine? And one additional question is what is the setting of ISOMODE in the uvconfig file?
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
Does the trigger subroutine place a record lock on the record being written? In UniVerse, SQL update statements are executed in a transaction so the rules are different when just updating with the ED command. The error message seems to indicate that the appropriate locks are not set. In terms of the TRIGGER, is this a SQL Trigger generated via a CREATE TRIGGER command? Or a trigger created via an indexed based subroutine? And one additional question is what is the setting of ISOMODE in the uvconfig file?
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
Thanks for the response. The trigger is a index based trigger calling a subr.
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Neil:
Thanks for the response. The trigger is a index based trigger calling a subr.
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Thanks for the response. The trigger is a index based trigger calling a subr.
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
Thanks JR. Then I'm "guessing" the index based subroutine is not setting a READU lock on the record being written? If so, using ED would not have an issue as the update would not be within a transaction. But doing a SQL update would be transaction based. Would this be the case? If not, can you supply the index based trigger subroutine code?
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
Here is my SUBR code and ISOMODE = 1
SUBROUTINE UPSTRACK.TRIGGER(C.DATA,DATA1)
*
* TRIGGER CALLED TO UPDATE FILE SHIPMENT_INFO FOR
* SHIPPERS TO READ TO CREATE SHIPPING
* LABEL
* JR MOORE 02/17/22
*
* DECLARE VARIABLES
NEWREC = "" ; INVNO = "" ; NEWREC = "" ; OREC = "" ; CSZREC = "" ; VM = CHAR(253)
TYPE = "" ; TEST.ID = ""
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
DOCID = @RECORD<1>
TRACKNUM = @ID
DEBUG
* ESTABLISH DOC ID EITHER INV# OR TRANSFER #
* IF ORDER INSERT TRACKING IN ORDER RECORD
IF INDEX(DOCID,"-",1) > 0 THEN
* IF NO ORDERS WITH INV# FOUND TRY INVOICE FILE
BSCAN IDX.REC,RECX.VAR FROM F.ORD,DOCID USING "INV#" ELSE RECX.VAR = ''
ORDID = ''
IF RECX.VAR # '' THEN
TEST.ID = RECX.VAR<1>
READU OREC FROM F.ORD, TEST.ID THEN
IF OREC<63> = DOCID THEN
TYPE = "ORD"
ORDID = TEST.ID
END
END
END
IF TEST.ID = '' THEN
* REPEAT
* END ELSE
READU OREC FROM F.OLDINV, DOCID THEN
TYPE = "INV"
END
END
IF OREC # "" THEN
*DETERMINE HOW MANY PARTS ON ORDER
NUMPARTS = DCOUNT(OREC<27>,@VM)
* INSERT "M" AT 27 AND COMMENT AND TRACK NUM AT COUNT OF PARTS +1
OREC<27,NUMPARTS+1> = "M"
OREC<28,NUMPARTS+1> = "TRACKING"
OREC<173,NUMPARTS+1> = TRACKNUM
* INSERT COMMENT AT PARTS +2
OREC<27,NUMPARTS+2> = "C"
OREC<28,NUMPARTS+2> = "TRACKING"
END
END ELSE
* IF NOT FOUND IN ORDER FILE TRY TRANSFER
READU OREC FROM F.TRANSFER, DOCID THEN
TYPE = "TR"
OREC<102,-1> = TRACKNUM
END
END
* write out the tracking data based on the document type
BEGIN CASE
CASE TYPE = "ORD"
WRITE OREC ON F.ORD, TEST.ID
CASE TYPE = "INV"
WRITE OREC ON F.OLDINV, DOCID
CASE TYPE = "TR"
WRITE OREC ON F.TRANSFER, DOCID
CASE 1
RETURN
END CASE
RETURN
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Neil:
Here is my SUBR code and ISOMODE = 1
SUBROUTINE UPSTRACK.TRIGGER(C.DATA,DATA1)
*
* TRIGGER CALLED TO UPDATE FILE SHIPMENT_INFO FOR
* SHIPPERS TO READ TO CREATE SHIPPING
* LABEL
* JR MOORE 02/17/22
*
* DECLARE VARIABLES
NEWREC = "" ; INVNO = "" ; NEWREC = "" ; OREC = "" ; CSZREC = "" ; VM = CHAR(253)
TYPE = "" ; TEST.ID = ""
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
DOCID = @RECORD<1>
TRACKNUM = @ID
DEBUG
* ESTABLISH DOC ID EITHER INV# OR TRANSFER #
* IF ORDER INSERT TRACKING IN ORDER RECORD
IF INDEX(DOCID,"-",1) > 0 THEN
* IF NO ORDERS WITH INV# FOUND TRY INVOICE FILE
BSCAN IDX.REC,RECX.VAR FROM F.ORD,DOCID USING "INV#" ELSE RECX.VAR = ''
ORDID = ''
IF RECX.VAR # '' THEN
TEST.ID = RECX.VAR<1>
READU OREC FROM F.ORD, TEST.ID THEN
IF OREC<63> = DOCID THEN
TYPE = "ORD"
ORDID = TEST.ID
END
END
END
IF TEST.ID = '' THEN
* REPEAT
* END ELSE
READU OREC FROM F.OLDINV, DOCID THEN
TYPE = "INV"
END
END
IF OREC # "" THEN
*DETERMINE HOW MANY PARTS ON ORDER
NUMPARTS = DCOUNT(OREC<27>,@VM)
* INSERT "M" AT 27 AND COMMENT AND TRACK NUM AT COUNT OF PARTS +1
OREC<27,NUMPARTS+1> = "M"
OREC<28,NUMPARTS+1> = "TRACKING"
OREC<173,NUMPARTS+1> = TRACKNUM
* INSERT COMMENT AT PARTS +2
OREC<27,NUMPARTS+2> = "C"
OREC<28,NUMPARTS+2> = "TRACKING"
END
END ELSE
* IF NOT FOUND IN ORDER FILE TRY TRANSFER
READU OREC FROM F.TRANSFER, DOCID THEN
TYPE = "TR"
OREC<102,-1> = TRACKNUM
END
END
* write out the tracking data based on the document type
BEGIN CASE
CASE TYPE = "ORD"
WRITE OREC ON F.ORD, TEST.ID
CASE TYPE = "INV"
WRITE OREC ON F.OLDINV, DOCID
CASE TYPE = "TR"
WRITE OREC ON F.TRANSFER, DOCID
CASE 1
RETURN
END CASE
RETURN
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Here is my SUBR code and ISOMODE = 1
SUBROUTINE UPSTRACK.TRIGGER(C.DATA,DATA1)
*
* TRIGGER CALLED TO UPDATE FILE SHIPMENT_INFO FOR
* SHIPPERS TO READ TO CREATE SHIPPING
* LABEL
* JR MOORE 02/17/22
*
* DECLARE VARIABLES
NEWREC = "" ; INVNO = "" ; NEWREC = "" ; OREC = "" ; CSZREC = "" ; VM = CHAR(253)
TYPE = "" ; TEST.ID = ""
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
DOCID = @RECORD<1>
TRACKNUM = @ID
DEBUG
* ESTABLISH DOC ID EITHER INV# OR TRANSFER #
* IF ORDER INSERT TRACKING IN ORDER RECORD
IF INDEX(DOCID,"-",1) > 0 THEN
* IF NO ORDERS WITH INV# FOUND TRY INVOICE FILE
BSCAN IDX.REC,RECX.VAR FROM F.ORD,DOCID USING "INV#" ELSE RECX.VAR = ''
ORDID = ''
IF RECX.VAR # '' THEN
TEST.ID = RECX.VAR<1>
READU OREC FROM F.ORD, TEST.ID THEN
IF OREC<63> = DOCID THEN
TYPE = "ORD"
ORDID = TEST.ID
END
END
END
IF TEST.ID = '' THEN
* REPEAT
* END ELSE
READU OREC FROM F.OLDINV, DOCID THEN
TYPE = "INV"
END
END
IF OREC # "" THEN
*DETERMINE HOW MANY PARTS ON ORDER
NUMPARTS = DCOUNT(OREC<27>,@VM)
* INSERT "M" AT 27 AND COMMENT AND TRACK NUM AT COUNT OF PARTS +1
OREC<27,NUMPARTS+1> = "M"
OREC<28,NUMPARTS+1> = "TRACKING"
OREC<173,NUMPARTS+1> = TRACKNUM
* INSERT COMMENT AT PARTS +2
OREC<27,NUMPARTS+2> = "C"
OREC<28,NUMPARTS+2> = "TRACKING"
END
END ELSE
* IF NOT FOUND IN ORDER FILE TRY TRANSFER
READU OREC FROM F.TRANSFER, DOCID THEN
TYPE = "TR"
OREC<102,-1> = TRACKNUM
END
END
* write out the tracking data based on the document type
BEGIN CASE
CASE TYPE = "ORD"
WRITE OREC ON F.ORD, TEST.ID
CASE TYPE = "INV"
WRITE OREC ON F.OLDINV, DOCID
CASE TYPE = "TR"
WRITE OREC ON F.TRANSFER, DOCID
CASE 1
RETURN
END CASE
RETURN
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
For example, if a READUed record doesn't exist, or if ORCE<63> does not equal DOCID.
Maybe that's just theoretical & never happens in practice.
But the 4 OPENs are local to this subroutine, so I think the locks are released on the implicit file close when the subroutine returns. Maybe that is not true under transactions?
2. Also, no READU LOCKED clauses mean that if some other process holds the lock, the current update going to wait on that other process. That will hold up the complete transaction, namely the write of the primary record.
3. I think the entire update of the primary record is abandoned if an OPEN...ELSE ABORT ever occurs.
4. Neil,
I did not understand the distinction you made between SQL vs. ED. Even in ED, the update of the primary record & all its indices are under an umbrella transaction. So this subroutine would be, too, no?
More questions than answers. Sorry.
------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
US
------------------------------
1. It strikes me that there are potential paths through the routine that can set readu locks that don't explicitly get released.
For example, if a READUed record doesn't exist, or if ORCE<63> does not equal DOCID.
Maybe that's just theoretical & never happens in practice.
But the 4 OPENs are local to this subroutine, so I think the locks are released on the implicit file close when the subroutine returns. Maybe that is not true under transactions?
2. Also, no READU LOCKED clauses mean that if some other process holds the lock, the current update going to wait on that other process. That will hold up the complete transaction, namely the write of the primary record.
3. I think the entire update of the primary record is abandoned if an OPEN...ELSE ABORT ever occurs.
4. Neil,
I did not understand the distinction you made between SQL vs. ED. Even in ED, the update of the primary record & all its indices are under an umbrella transaction. So this subroutine would be, too, no?
More questions than answers. Sorry.
------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
US
------------------------------
For example, if a READUed record doesn't exist, or if ORCE<63> does not equal DOCID.
Maybe that's just theoretical & never happens in practice.
But the 4 OPENs are local to this subroutine, so I think the locks are released on the implicit file close when the subroutine returns. Maybe that is not true under transactions?
2. Also, no READU LOCKED clauses mean that if some other process holds the lock, the current update going to wait on that other process. That will hold up the complete transaction, namely the write of the primary record.
3. I think the entire update of the primary record is abandoned if an OPEN...ELSE ABORT ever occurs.
4. Neil,
I did not understand the distinction you made between SQL vs. ED. Even in ED, the update of the primary record & all its indices are under an umbrella transaction. So this subroutine would be, too, no?
More questions than answers. Sorry.
------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
US
------------------------------
The distinction I was making was in terms of running as a "transaction" which involves a BEGIN TRANSACTION, COMMIT or ABORT, and an END TRANSACTION. Using ED on a record would compare to doing a READ and WRITE in a BASIC program. A SQL update is transaction based so it would compare doing READ's and WRITE's in a BASIC program wrapped within a BEGIN TRANSACTION, COMMIT, and END TRANSACTION. When updates are done within a transaction, there are rules involving the locks which need to be acquired prior to performing a WRITE operation. My suspicion was that the indexed based subroutine was doing a WRITE operation on a record that was not holding a READU lock. This would not be a problem when using ED. However, when the update was being done via a SQL statement it would violate the rule that a READU lock must be set before doing a WRITE.
Does that make sense?
I haven't been able to review the subroutine code yet in detail to determine if there is a case where a WRITE is being done without the record being locked. The error message does hint that this is what is causing the failure.
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
I have a file trigger when a record is updated it writes information to another file to update UPS tracking in an order file. If I use a SQL statement in Universe to insert the record I get a permissions error (Program "UPSTRACK.TRIGGER": Line 81, FATAL:The locks necessary for database operations at the current isolation
level (2) are not held by this process.) If I add the item through ED it works fine updates all the files and adds the new record. Are there different levels of permission when execute SQL from TCL vs. ED?
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
level (2) are not held by this process.) If I add the item through ED it works fine updates all the files and adds the new record. Are there different levels of permission when execute SQL from TCL vs. ED?
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Just an incidental - I recommend adding a named COMMON block to hold the open file handles with a flag you can test so that the files only get opened on the first invocation. This avoids the need to re-open on subsequent invocations which is a relatively expensive operation saved.
Regards
JJ
------------------------------
John Jenkins
Principal Technical Support Engineer
Rocket Software Limited
U.K.
------------------------------
Neil:
Here is my SUBR code and ISOMODE = 1
SUBROUTINE UPSTRACK.TRIGGER(C.DATA,DATA1)
*
* TRIGGER CALLED TO UPDATE FILE SHIPMENT_INFO FOR
* SHIPPERS TO READ TO CREATE SHIPPING
* LABEL
* JR MOORE 02/17/22
*
* DECLARE VARIABLES
NEWREC = "" ; INVNO = "" ; NEWREC = "" ; OREC = "" ; CSZREC = "" ; VM = CHAR(253)
TYPE = "" ; TEST.ID = ""
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
DOCID = @RECORD<1>
TRACKNUM = @ID
DEBUG
* ESTABLISH DOC ID EITHER INV# OR TRANSFER #
* IF ORDER INSERT TRACKING IN ORDER RECORD
IF INDEX(DOCID,"-",1) > 0 THEN
* IF NO ORDERS WITH INV# FOUND TRY INVOICE FILE
BSCAN IDX.REC,RECX.VAR FROM F.ORD,DOCID USING "INV#" ELSE RECX.VAR = ''
ORDID = ''
IF RECX.VAR # '' THEN
TEST.ID = RECX.VAR<1>
READU OREC FROM F.ORD, TEST.ID THEN
IF OREC<63> = DOCID THEN
TYPE = "ORD"
ORDID = TEST.ID
END
END
END
IF TEST.ID = '' THEN
* REPEAT
* END ELSE
READU OREC FROM F.OLDINV, DOCID THEN
TYPE = "INV"
END
END
IF OREC # "" THEN
*DETERMINE HOW MANY PARTS ON ORDER
NUMPARTS = DCOUNT(OREC<27>,@VM)
* INSERT "M" AT 27 AND COMMENT AND TRACK NUM AT COUNT OF PARTS +1
OREC<27,NUMPARTS+1> = "M"
OREC<28,NUMPARTS+1> = "TRACKING"
OREC<173,NUMPARTS+1> = TRACKNUM
* INSERT COMMENT AT PARTS +2
OREC<27,NUMPARTS+2> = "C"
OREC<28,NUMPARTS+2> = "TRACKING"
END
END ELSE
* IF NOT FOUND IN ORDER FILE TRY TRANSFER
READU OREC FROM F.TRANSFER, DOCID THEN
TYPE = "TR"
OREC<102,-1> = TRACKNUM
END
END
* write out the tracking data based on the document type
BEGIN CASE
CASE TYPE = "ORD"
WRITE OREC ON F.ORD, TEST.ID
CASE TYPE = "INV"
WRITE OREC ON F.OLDINV, DOCID
CASE TYPE = "TR"
WRITE OREC ON F.TRANSFER, DOCID
CASE 1
RETURN
END CASE
RETURN
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Here is my SUBR code and ISOMODE = 1
SUBROUTINE UPSTRACK.TRIGGER(C.DATA,DATA1)
*
* TRIGGER CALLED TO UPDATE FILE SHIPMENT_INFO FOR
* SHIPPERS TO READ TO CREATE SHIPPING
* LABEL
* JR MOORE 02/17/22
*
* DECLARE VARIABLES
NEWREC = "" ; INVNO = "" ; NEWREC = "" ; OREC = "" ; CSZREC = "" ; VM = CHAR(253)
TYPE = "" ; TEST.ID = ""
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
DOCID = @RECORD<1>
TRACKNUM = @ID
DEBUG
* ESTABLISH DOC ID EITHER INV# OR TRANSFER #
* IF ORDER INSERT TRACKING IN ORDER RECORD
IF INDEX(DOCID,"-",1) > 0 THEN
* IF NO ORDERS WITH INV# FOUND TRY INVOICE FILE
BSCAN IDX.REC,RECX.VAR FROM F.ORD,DOCID USING "INV#" ELSE RECX.VAR = ''
ORDID = ''
IF RECX.VAR # '' THEN
TEST.ID = RECX.VAR<1>
READU OREC FROM F.ORD, TEST.ID THEN
IF OREC<63> = DOCID THEN
TYPE = "ORD"
ORDID = TEST.ID
END
END
END
IF TEST.ID = '' THEN
* REPEAT
* END ELSE
READU OREC FROM F.OLDINV, DOCID THEN
TYPE = "INV"
END
END
IF OREC # "" THEN
*DETERMINE HOW MANY PARTS ON ORDER
NUMPARTS = DCOUNT(OREC<27>,@VM)
* INSERT "M" AT 27 AND COMMENT AND TRACK NUM AT COUNT OF PARTS +1
OREC<27,NUMPARTS+1> = "M"
OREC<28,NUMPARTS+1> = "TRACKING"
OREC<173,NUMPARTS+1> = TRACKNUM
* INSERT COMMENT AT PARTS +2
OREC<27,NUMPARTS+2> = "C"
OREC<28,NUMPARTS+2> = "TRACKING"
END
END ELSE
* IF NOT FOUND IN ORDER FILE TRY TRANSFER
READU OREC FROM F.TRANSFER, DOCID THEN
TYPE = "TR"
OREC<102,-1> = TRACKNUM
END
END
* write out the tracking data based on the document type
BEGIN CASE
CASE TYPE = "ORD"
WRITE OREC ON F.ORD, TEST.ID
CASE TYPE = "INV"
WRITE OREC ON F.OLDINV, DOCID
CASE TYPE = "TR"
WRITE OREC ON F.TRANSFER, DOCID
CASE 1
RETURN
END CASE
RETURN
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
My experience is only with SQL file triggers and not the index based subroutines.
I believe the recordlocku statement is required to create/update a record in a different file. We use an internal sub to obtain the lock e.g.
.
.
.
for SubCnt = 1 to 4 until bError
on SubCnt gosub checkFiles,readValues,lockFileRecord,updateFiles
next SubCnt
return
.
.
.
lockFileRecord:
tryCount = 0
bRecordLocked = @false
loop until tryCount > 9 do
recordlocku fileVar,recordKey
on error
errors = '** fileName key ':recordKey:' could not be '
errors := 'accessed. pid = ':PID:', status = ':status()
bError = @true
return
end
locked
nap 250
tryCount += 1
continue
end
exit
repeat
if tryCount > 9 then
errors = '** fileName record ':recordKey:' could not be locked. '
errors := 'pid = ':PID:', status = ':STATUS()
bError = @true
return
end
bRecordLocked = @true
returnWhile I detest using 'exit' and 'continue' commands, the above works well enough and has done so here for many years.
The PID variable is used in all our trigger code and is assigned in common.
DECLARE GCI getpid
PID = getpid()Cheers,
Peter
------------------------------
Peter Cheney
Developer and Systems Superstar
Firstmac
Brisbane Qld Australia
------------------------------
Neil:
Here is my SUBR code and ISOMODE = 1
SUBROUTINE UPSTRACK.TRIGGER(C.DATA,DATA1)
*
* TRIGGER CALLED TO UPDATE FILE SHIPMENT_INFO FOR
* SHIPPERS TO READ TO CREATE SHIPPING
* LABEL
* JR MOORE 02/17/22
*
* DECLARE VARIABLES
NEWREC = "" ; INVNO = "" ; NEWREC = "" ; OREC = "" ; CSZREC = "" ; VM = CHAR(253)
TYPE = "" ; TEST.ID = ""
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
DOCID = @RECORD<1>
TRACKNUM = @ID
DEBUG
* ESTABLISH DOC ID EITHER INV# OR TRANSFER #
* IF ORDER INSERT TRACKING IN ORDER RECORD
IF INDEX(DOCID,"-",1) > 0 THEN
* IF NO ORDERS WITH INV# FOUND TRY INVOICE FILE
BSCAN IDX.REC,RECX.VAR FROM F.ORD,DOCID USING "INV#" ELSE RECX.VAR = ''
ORDID = ''
IF RECX.VAR # '' THEN
TEST.ID = RECX.VAR<1>
READU OREC FROM F.ORD, TEST.ID THEN
IF OREC<63> = DOCID THEN
TYPE = "ORD"
ORDID = TEST.ID
END
END
END
IF TEST.ID = '' THEN
* REPEAT
* END ELSE
READU OREC FROM F.OLDINV, DOCID THEN
TYPE = "INV"
END
END
IF OREC # "" THEN
*DETERMINE HOW MANY PARTS ON ORDER
NUMPARTS = DCOUNT(OREC<27>,@VM)
* INSERT "M" AT 27 AND COMMENT AND TRACK NUM AT COUNT OF PARTS +1
OREC<27,NUMPARTS+1> = "M"
OREC<28,NUMPARTS+1> = "TRACKING"
OREC<173,NUMPARTS+1> = TRACKNUM
* INSERT COMMENT AT PARTS +2
OREC<27,NUMPARTS+2> = "C"
OREC<28,NUMPARTS+2> = "TRACKING"
END
END ELSE
* IF NOT FOUND IN ORDER FILE TRY TRANSFER
READU OREC FROM F.TRANSFER, DOCID THEN
TYPE = "TR"
OREC<102,-1> = TRACKNUM
END
END
* write out the tracking data based on the document type
BEGIN CASE
CASE TYPE = "ORD"
WRITE OREC ON F.ORD, TEST.ID
CASE TYPE = "INV"
WRITE OREC ON F.OLDINV, DOCID
CASE TYPE = "TR"
WRITE OREC ON F.TRANSFER, DOCID
CASE 1
RETURN
END CASE
RETURN
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
Here is my SUBR code and ISOMODE = 1
SUBROUTINE UPSTRACK.TRIGGER(C.DATA,DATA1)
*
* TRIGGER CALLED TO UPDATE FILE SHIPMENT_INFO FOR
* SHIPPERS TO READ TO CREATE SHIPPING
* LABEL
* JR MOORE 02/17/22
*
* DECLARE VARIABLES
NEWREC = "" ; INVNO = "" ; NEWREC = "" ; OREC = "" ; CSZREC = "" ; VM = CHAR(253)
TYPE = "" ; TEST.ID = ""
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
DOCID = @RECORD<1>
TRACKNUM = @ID
DEBUG
* ESTABLISH DOC ID EITHER INV# OR TRANSFER #
* IF ORDER INSERT TRACKING IN ORDER RECORD
IF INDEX(DOCID,"-",1) > 0 THEN
* IF NO ORDERS WITH INV# FOUND TRY INVOICE FILE
BSCAN IDX.REC,RECX.VAR FROM F.ORD,DOCID USING "INV#" ELSE RECX.VAR = ''
ORDID = ''
IF RECX.VAR # '' THEN
TEST.ID = RECX.VAR<1>
READU OREC FROM F.ORD, TEST.ID THEN
IF OREC<63> = DOCID THEN
TYPE = "ORD"
ORDID = TEST.ID
END
END
END
IF TEST.ID = '' THEN
* REPEAT
* END ELSE
READU OREC FROM F.OLDINV, DOCID THEN
TYPE = "INV"
END
END
IF OREC # "" THEN
*DETERMINE HOW MANY PARTS ON ORDER
NUMPARTS = DCOUNT(OREC<27>,@VM)
* INSERT "M" AT 27 AND COMMENT AND TRACK NUM AT COUNT OF PARTS +1
OREC<27,NUMPARTS+1> = "M"
OREC<28,NUMPARTS+1> = "TRACKING"
OREC<173,NUMPARTS+1> = TRACKNUM
* INSERT COMMENT AT PARTS +2
OREC<27,NUMPARTS+2> = "C"
OREC<28,NUMPARTS+2> = "TRACKING"
END
END ELSE
* IF NOT FOUND IN ORDER FILE TRY TRANSFER
READU OREC FROM F.TRANSFER, DOCID THEN
TYPE = "TR"
OREC<102,-1> = TRACKNUM
END
END
* write out the tracking data based on the document type
BEGIN CASE
CASE TYPE = "ORD"
WRITE OREC ON F.ORD, TEST.ID
CASE TYPE = "INV"
WRITE OREC ON F.OLDINV, DOCID
CASE TYPE = "TR"
WRITE OREC ON F.TRANSFER, DOCID
CASE 1
RETURN
END CASE
RETURN
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
I have reviewed the trigger subroutine and it would appear that a record lock would have been set in order for any of the CASE statements to be executed. One thing I did notice is that the error provided originally indicates line 81. I did a cut/paste of the program onto my system in order to review and I'm showing line 81 as a blank line after the CASE statements.
0071: BEGIN CASE
0072: CASE TYPE = "ORD"
0073: WRITE OREC ON F.ORD, TEST.ID
0074: CASE TYPE = "INV"
0075: WRITE OREC ON F.OLDINV, DOCID
0076: CASE TYPE = "TR"
0077: WRITE OREC ON F.TRANSFER, DOCID
0078: CASE 1
0079: RETURN
0080: END CASE
0081:
0082: RETURN
I'm not sure if something got lost due to the cut and paste. On your system, is line 81 one of the WRITE statements in the CASE block?
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
JR,
Just an incidental - I recommend adding a named COMMON block to hold the open file handles with a flag you can test so that the files only get opened on the first invocation. This avoids the need to re-open on subsequent invocations which is a relatively expensive operation saved.
Regards
JJ
------------------------------
John Jenkins
Principal Technical Support Engineer
Rocket Software Limited
U.K.
------------------------------
Just an incidental - I recommend adding a named COMMON block to hold the open file handles with a flag you can test so that the files only get opened on the first invocation. This avoids the need to re-open on subsequent invocations which is a relatively expensive operation saved.
Regards
JJ
------------------------------
John Jenkins
Principal Technical Support Engineer
Rocket Software Limited
U.K.
------------------------------
If he adds the common:
COMMON /UPSTRACK.TRIGGER/ ATPATH, F.INFO, F.ORD, F.TRANSFER, F.OLDINV
IF ATPATH # @PATH THEN
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
ATPATH = @PATH
END
If he adds the labelled common, then later, if & when the subroutine ever returns without having written the record that it locked, I believe the lock is retained, rather than released when the file is implicitly closed when the previously local file variable is abandoned upon RETURN.
I have found that a problem when retrofitting opens to common in existing subroutines, where releases may be sloppy.
I don't recall ever testing that behaviour under a trigger index like this one, tho.
------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
US
------------------------------
JJ,
If he adds the common:
COMMON /UPSTRACK.TRIGGER/ ATPATH, F.INFO, F.ORD, F.TRANSFER, F.OLDINV
IF ATPATH # @PATH THEN
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
ATPATH = @PATH
END
If he adds the labelled common, then later, if & when the subroutine ever returns without having written the record that it locked, I believe the lock is retained, rather than released when the file is implicitly closed when the previously local file variable is abandoned upon RETURN.
I have found that a problem when retrofitting opens to common in existing subroutines, where releases may be sloppy.
I don't recall ever testing that behaviour under a trigger index like this one, tho.
------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
US
------------------------------
If he adds the common:
COMMON /UPSTRACK.TRIGGER/ ATPATH, F.INFO, F.ORD, F.TRANSFER, F.OLDINV
IF ATPATH # @PATH THEN
* OPEN FILES
OPEN 'UPS_ORDER_TRACKING' TO F.INFO ELSE ABORT 201,'SHIPMENT_INFO'
OPEN 'ORDER' TO F.ORD ELSE ABORT 201,'ORDER'
OPEN 'TRANSFER' TO F.TRANSFER ELSE ABORT 201,'TRANSFER'
OPEN 'OLD-INVOICES' TO F.OLDINV ELSE ABORT 201,'OLD-INVOICES'
ATPATH = @PATH
END
If he adds the labelled common, then later, if & when the subroutine ever returns without having written the record that it locked, I believe the lock is retained, rather than released when the file is implicitly closed when the previously local file variable is abandoned upon RETURN.
I have found that a problem when retrofitting opens to common in existing subroutines, where releases may be sloppy.
I don't recall ever testing that behaviour under a trigger index like this one, tho.
------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
US
------------------------------
I believe so - I always aim to explicitly release any file or record locks set and never rely upon the default behaviour myself.
Regards
JJ
------------------------------
John Jenkins
Principal Technical Support Engineer
Rocket Software Limited
U.K.
------------------------------
I have a file trigger when a record is updated it writes information to another file to update UPS tracking in an order file. If I use a SQL statement in Universe to insert the record I get a permissions error (Program "UPSTRACK.TRIGGER": Line 81, FATAL:The locks necessary for database operations at the current isolation
level (2) are not held by this process.) If I add the item through ED it works fine updates all the files and adds the new record. Are there different levels of permission when execute SQL from TCL vs. ED?
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
level (2) are not held by this process.) If I add the item through ED it works fine updates all the files and adds the new record. Are there different levels of permission when execute SQL from TCL vs. ED?
Thanks JR Moore
------------------------------
Jr Moore
IT Special Projects
Auto Parts Warehouse
Hillsboro OR US
------------------------------
I have a similar situation. The trigger subroutine that writes to the log file fails at the WRITE line if it is triggered by SQL but works with ED.
I added LOCKFILE and UNLOCKFILE after reading post 21 on this page.
Presumably I would have a lock on the log file but that still throws the same error.
Surprisingly it did work just once from SQL after I added the lock management lines, though not immediately. It worked the first time I ran it this morning, then it went back to the errors, which was all I saw yesterday.
The subroutine code I'm using is here.
https://docs.rocketsoftware.com/bundle/universesystemdescription_rg_1141/page/lws1722514424487.html
These are where I added the lock management lines. (I am a novice at BASIC).
0006: OPEN "AUDIT.FILE" TO F.AUDIT ELSE STOP "CANNOT OPEN AUDIT.FILE"
----:
0007: FILELOCK F.AUDIT LOCKED STOP "AUDIT FILE ALREADY LOCKED"
0028: WRITE AUDIT.REC ON F.AUDIT,RECID
----:
0029: FILEUNLOCK F.AUDIT
It isn't the end of the world because the third party application runs through BASIC subroutines but I also do some work with SQL and it would be nice if it worked with SQL too.
------------------------------
Greg Clitheroe
Rocket Forum Shared Account
------------------------------
I have a similar situation. The trigger subroutine that writes to the log file fails at the WRITE line if it is triggered by SQL but works with ED.
I added LOCKFILE and UNLOCKFILE after reading post 21 on this page.
Presumably I would have a lock on the log file but that still throws the same error.
Surprisingly it did work just once from SQL after I added the lock management lines, though not immediately. It worked the first time I ran it this morning, then it went back to the errors, which was all I saw yesterday.
The subroutine code I'm using is here.
https://docs.rocketsoftware.com/bundle/universesystemdescription_rg_1141/page/lws1722514424487.html
These are where I added the lock management lines. (I am a novice at BASIC).
0006: OPEN "AUDIT.FILE" TO F.AUDIT ELSE STOP "CANNOT OPEN AUDIT.FILE"
----:
0007: FILELOCK F.AUDIT LOCKED STOP "AUDIT FILE ALREADY LOCKED"
0028: WRITE AUDIT.REC ON F.AUDIT,RECID
----:
0029: FILEUNLOCK F.AUDIT
It isn't the end of the world because the third party application runs through BASIC subroutines but I also do some work with SQL and it would be nice if it worked with SQL too.
------------------------------
Greg Clitheroe
Rocket Forum Shared Account
------------------------------
After reading the BASIC documentation about Isolation levels and transactions, I was able to make it work logging changes made by UniVerse SQL by locking, writing and unlocking AUDIT.FILE inside a transaction.
I would really like it if someone who actually knows BASIC could comment on this structure.
0027 BEGIN TRANSACTION
0028 FILELOCK F.AUDIT LOCKED STOP "AUDIT FILE ALREADY LOCKED"
0029 WRITE AUDIT.REC ON F.AUDIT,RECID
0030 COMMIT
0031 FILEUNLOCK F.AUDIT
0032 END TRANSACTION
------------------------------
Greg Clitheroe
Rocket Forum Shared Account
------------------------------
After reading the BASIC documentation about Isolation levels and transactions, I was able to make it work logging changes made by UniVerse SQL by locking, writing and unlocking AUDIT.FILE inside a transaction.
I would really like it if someone who actually knows BASIC could comment on this structure.
0027 BEGIN TRANSACTION
0028 FILELOCK F.AUDIT LOCKED STOP "AUDIT FILE ALREADY LOCKED"
0029 WRITE AUDIT.REC ON F.AUDIT,RECID
0030 COMMIT
0031 FILEUNLOCK F.AUDIT
0032 END TRANSACTION
------------------------------
Greg Clitheroe
Rocket Forum Shared Account
------------------------------
Hi Greg,
The requirement is that you have a record lock (READU) lock on record you are writing within the transaction boundaries. The FILELOCK statement essentially puts a record lock on every record in the file. This will work but could be problematic if you have multiple users attempting to update different records in the AUDIT file as the FILELOCK will block all records. You could change the FILELOCK to a READU instead and you wouldn't need the FILEUNLOCK statement as a WRITE statement automatically releases an active READU lock.
Thanks,
Neil
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------
After reading the BASIC documentation about Isolation levels and transactions, I was able to make it work logging changes made by UniVerse SQL by locking, writing and unlocking AUDIT.FILE inside a transaction.
I would really like it if someone who actually knows BASIC could comment on this structure.
0027 BEGIN TRANSACTION
0028 FILELOCK F.AUDIT LOCKED STOP "AUDIT FILE ALREADY LOCKED"
0029 WRITE AUDIT.REC ON F.AUDIT,RECID
0030 COMMIT
0031 FILEUNLOCK F.AUDIT
0032 END TRANSACTION
------------------------------
Greg Clitheroe
Rocket Forum Shared Account
------------------------------
Hi Greg,
We've used triggers for quite some time now, the trick is to lock the record id prior to writing but sometimes need to check for existing records to avoid overwriting them. In all our code we just gosub the "get lock" subroutine and get the record lock, then perform the update:
getRecordLock:
tryCount = 0
loop until tryCount > 9 do
recordlocku fileHandle, recordId on error
errors = 'fileName ':recordId:' could not '
errors := 'be accessed. PID = ':PID:', ERR = ':status()
bError = @true
return
end locked
nap 100
tryCount += 1
continue
end
exit
repeat
if tryCount > 9 then
errors = 'fileName record ':recordId:' could not be '
errors := 'locked. PID = ':PID:', ERR = ':status()
bError = @true
gosub logError
return
end
bSuccess = @true
return
Also be aware that recordlocku may not always be suitable because it doesn't test for records existence, just sets the lock. In this case it is also acceptable to use a readu:
getRecordLock:
recorId = oconv(date(),'D4YMD["",2"",2""]')
recordId := '*':oconv(int(time()),'MTS':@vm:'MCN'):'*':recordType
recordId := '*':time();* Adding extra time to make unique per millisec
tryCount = 0
loop until tryCount > 9 do
readu existingRecord from fileHandle, recordId locked
nap 250
tryCount += 1
end then
nap 100 ;* Get next 10th second in key
recordId = oconv(LOGIN.DATE,'D4YMD["",2"",2""]')
recordId := '*':oconv(int(time()),'MTS':@vm:'MCN'):'*':REC.TYPE
recordId := '*':time();* Adding ext time to make unique per millisec
end else
exit ;* The result we want
end
repeat
if tryCount > 9 then
errors = 'fileName record ':recordId:' could not be '
errors := 'locked. PID = ':PID:', ERR = ':status()
bError = @true
gosub logError
return
end
bSuccess = @true
return
------------------------------
Peter Cheney
Brisbane Qld Australia
------------------------------
Hi Greg,
We've used triggers for quite some time now, the trick is to lock the record id prior to writing but sometimes need to check for existing records to avoid overwriting them. In all our code we just gosub the "get lock" subroutine and get the record lock, then perform the update:
getRecordLock:
tryCount = 0
loop until tryCount > 9 do
recordlocku fileHandle, recordId on error
errors = 'fileName ':recordId:' could not '
errors := 'be accessed. PID = ':PID:', ERR = ':status()
bError = @true
return
end locked
nap 100
tryCount += 1
continue
end
exit
repeat
if tryCount > 9 then
errors = 'fileName record ':recordId:' could not be '
errors := 'locked. PID = ':PID:', ERR = ':status()
bError = @true
gosub logError
return
end
bSuccess = @true
return
Also be aware that recordlocku may not always be suitable because it doesn't test for records existence, just sets the lock. In this case it is also acceptable to use a readu:
getRecordLock:
recorId = oconv(date(),'D4YMD["",2"",2""]')
recordId := '*':oconv(int(time()),'MTS':@vm:'MCN'):'*':recordType
recordId := '*':time();* Adding extra time to make unique per millisec
tryCount = 0
loop until tryCount > 9 do
readu existingRecord from fileHandle, recordId locked
nap 250
tryCount += 1
end then
nap 100 ;* Get next 10th second in key
recordId = oconv(LOGIN.DATE,'D4YMD["",2"",2""]')
recordId := '*':oconv(int(time()),'MTS':@vm:'MCN'):'*':REC.TYPE
recordId := '*':time();* Adding ext time to make unique per millisec
end else
exit ;* The result we want
end
repeat
if tryCount > 9 then
errors = 'fileName record ':recordId:' could not be '
errors := 'locked. PID = ':PID:', ERR = ':status()
bError = @true
gosub logError
return
end
bSuccess = @true
return
------------------------------
Peter Cheney
Brisbane Qld Australia
------------------------------
Finally getting back to this.
If I understand correctly, recordlocku will lock a record id that doesn't yet exist in the file, allowing a new record with that id to be written?
------------------------------
Greg Clitheroe
Rocket Forum Shared Account
------------------------------
Finally getting back to this.
If I understand correctly, recordlocku will lock a record id that doesn't yet exist in the file, allowing a new record with that id to be written?
------------------------------
Greg Clitheroe
Rocket Forum Shared Account
------------------------------
Greg,
READU and RECORDLOCKU are applied to a record key whether it exists or not. And if you are writing a new record within a SQL transaction it must have a lock on the key, otherwise some other process could in theory write the same key at the same time breaking the ACID rules. Outside a SQL transaction anything goes.
------------------------------
Brian Leach
Director
Brian Leach Consulting
Chipping Norton GB
------------------------------
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
