Rocket U2 | UniVerse & UniData

 View Only
Expand all | Collapse all

FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

  • 1.  FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    Posted 6 days ago
    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
    ------------------------------


  • 2.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    ROCKETEER
    Posted 5 days ago
    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
    ------------------------------



  • 3.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    Posted 5 days ago
    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
    ------------------------------



  • 4.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    ROCKETEER
    Posted 5 days ago
    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
    ------------------------------



  • 5.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    Posted 5 days ago
    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
    ------------------------------



  • 6.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    Posted 5 days ago
    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
    ------------------------------



  • 7.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    ROCKETEER
    Posted 5 days ago
    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
    ------------------------------



  • 8.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    Posted 4 days ago
    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
    ------------------------------



  • 9.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    ROCKETEER
    Posted 4 days ago
    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
    ------------------------------



  • 10.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    ROCKETEER
    Posted 5 days ago
    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.
    ------------------------------



  • 11.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    Posted 4 days ago
    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
    ------------------------------



  • 12.  RE: FILE TRIGGER ERROR WITH SQL INSERT STATEMENT

    ROCKETEER
    Posted yesterday
    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.
    ------------------------------