Rocket U2 | UniVerse & UniData

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
------------------------------

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
------------------------------

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

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
------------------------------

Original Message:
Sent: 06-22-2022 17:13
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

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
------------------------------

Original Message:
Sent: 06-22-2022 17:17
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:13
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

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
------------------------------

Original Message:
Sent: 06-22-2022 17:34
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:17
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:13
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

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
------------------------------

Original Message:
Sent: 06-22-2022 18:10
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:34
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:17
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:13
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

Hi Chuck,
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
------------------------------

Original Message:
Sent: 06-23-2022 11:57
From: Chuck Stevenson
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 18:10
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:34
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:17
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:13
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

Hi JR,

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
return​

While 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
------------------------------

Original Message:
Sent: 06-22-2022 18:10
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:34
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:17
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:13
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

Hi JR,
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
------------------------------

Original Message:
Sent: 06-22-2022 18:10
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:34
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:17
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 17:13
From: Neil Morris
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

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.
------------------------------

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

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
------------------------------

Original Message:
Sent: 06-23-2022 15:41
From: John Jenkins
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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.

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------

Chuck,

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.
------------------------------

Original Message:
Sent: 06-24-2022 10:16
From: Chuck Stevenson
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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

Original Message:
Sent: 06-23-2022 15:41
From: John Jenkins
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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.

Original Message:
Sent: 06-22-2022 12:50
From: Jr Moore
Subject: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

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
------------------------------