I am looking for anyone with any experience with SET CONNECTION
during a transaction-in-progress with open cursors in Allbase.
 
A working program needed to perform (via a subroutine) a new, ancillary
SQL transaction that needs to always commit work against the same DBE
while the main program's P/O transaction is still in progress.
 
To do this, we used two connection names, 'MainTxn' and 'TxnForEDI'
doing 'MainTxn' last so it is the active connection in the main program.
Also at startup SET MULTITRANSACTION ON and SET USER TIMEOUT's are done.
 
The 1st P/O is successfully processed (including a committed 'TxnForEDI')
and SET CONNECTION back to 'MainTxn' (which can have multiple P/O's
in the same logical 'MainTxn').
 
The 2nd P/O is set up, some non-SQL updates done, and then:
 
FETCH NextLineItem, SQLCode:  -2754
Cursor ISSO.F74420(2) is not open.  (DBERR 2754)
 
The related code fragment (yes, it's FORTRAN) is:
 
EXEC SQL DECLARE NextLineItem CURSOR FOR
         SELECT [col. names snipped]
           FROM ISSO.ReqLineItem
          WHERE ReqNumber = :ReqReqNumber
          ORDER BY [3-col. names snipped]
 
... [ CONNECT's and setup for MULTITRANSACTION done ]
 
EXEC SQL OPEN NextLineItem
IF (SQLCode.NE.Ok) ...
 
CALL SetSQLCOM
NextLineItemGo = .TRUE.
DO WHILE (NextLineItemGo)
   EXEC SQL FETCH NextLineItem     <--- This is the cursor in question.
      INTO ...
   IF (SQLCode.NE.Ok) THEN
      IF (SQLCode.EQ.NotFound) THEN
         NextLineItemGo = .FALSE. ! End of lines on this req
         IF (.NOT.FirstSuppItemNo) THEN ! Prev PO...
            CALL SetSQLCOM  !  Set prev po # into RLI items
            DO WHILE (SQLGo)
               EXEC SQL UPDATE ISSO.ReqLineItem
               ...
            END DO
            IF (SQLAbort) GOTO 990
            PRINT *, 'ReqLineItem update with po #',MyPO
 
            EXEC SQL SET CONNECTION 'TxnForEDI' \
            IF (SQLCode.NE.Ok) ...               )  Added to do
            CALL ForEDI(MyPO,LastDUNS,*990)      )  new secondary
            EXEC SQL SET CONNECTION 'MainTxn'   /   SQL transaction.
            IF (SQLCode.NE.Ok) ...            /
 
         END IF ! Prev PO...
      ELSE  ! hard SQL error
         PRINT *, 'FETCH NextLineItem, SQLCode: ',SQLCode
         SQLAbort = SQLCode.NE.DeadLock
         Call SQLStatusCheck
         NextLineItemGo = SQLGo  !  Set by SQLStatusCheck
      END IF ! SQLCode.EQ.NotFound or not.
C
   ELSE  !  Got a line item to place on a PO
      ...
 
It appears that the SET CONNECTION's are losing the open cursor.
 
Short of running a separate program to do the secondary 'TxnForEDI',
any suggestions would be appreciated...
 
------------
Dirk Punzelt     407/768-7138
Rockwell / Avionics - Collins
 
[log in to unmask]