× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



This must be the trigger program --- been a while since I've done C --
usually do RPG.

Some other program does an insert into the file, and that file is under
commitment control ( is that true? or is it just your autonumber file? )
the case file issues the trigger call after the insert.  the trigger then
tries to get the new record which is not technically in the file until you
issue the commit for the case file group.  You should not be able to get
that record until commit for case is done.

Here is what I would do instead -

        The call to the trigger program passes in record information.
        In RPG:
C     *ENTRY        PLIST

C                   PARM                    @TRGBUFFER

C                   PARM                    @TRGLEN


D@TRGBUFFER       DS         32000

D @TRGFILE                1     10

D @TRGFLIB               11     20

D @TRGFMBR               21     30

D @TRGEVENT              31     31

D @TRGOLDOFS             49     52B 0                 BEforE OffsEt
D @TRGOLDLNG             53     56B 0                 BEforE LEngth
D @TRGCHGOFS             65     68B 0                 AftEr OffsEt
D @TRGCHGLNG             69     72B 0                 AftEr LEngth


The if @trgevent is '1' insert than you want to take the data starting the
data structure from @trgchgofs for the length of @trgchglng and put it into
a strucure that looks like your case file or overlay it starting at the chg
offset.  do your SQL to generate the new case number. Then you can change
the case number before the record even gets inserted to the file - of course
you would change the trigger to *before *insert.

If you have any questions or would like to see RPG source that does this I
would be glad to help.

Joe Reinhardt
Lance, Inc.
(704) 557-8673
-----Original Message-----
From: tomh@simas.com [mailto:tomh@simas.com]
Sent: Wednesday, March 13, 2002 2:50 PM
To: midrange-l@midrange.com
Subject: RE: SQLCODE -907


This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
You tell me - here's the program (I didn't write it, just helping one of
our programmers get thru it):

#include <stdio.h>
#include <stdlib.h>
#include <string.h>


int main(int argc, char* argv[])
{
    EXEC SQL INCLUDE SQLCA;

    long nOldValue = 0;
    long nNewValue = 0;

    printf("Start\n");
    EXEC SQL SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    printf("After SET TRANSACTION SQLCODE=%d\n", SQLCODE);
    EXEC SQL DECLARE C1 CURSOR FOR
         SELECT last_auto_number
         FROM cms/AUTONUMBER
         WHERE table_name = 'CASE'
         FOR UPDATE;
    printf("After SELECT SQLCODE=%d\n", SQLCODE);
    EXEC SQL OPEN C1;

    printf("After OPEN CURSOR SQLCODE=%d\n", SQLCODE);

    EXEC SQL FETCH C1 INTO :nOldValue;
    printf("After FETCH SQLCODE=%d\n", SQLCODE);
    printf("Fatched %d\n", nOldValue);
    nNewValue = nOldValue + 1;

    EXEC SQL UPDATE cms/AUTONUMBER
             SET last_auto_number = :nNewValue
             WHERE CURRENT OF C1;
    printf("After UPDATE AUTONUMBER SQLCODE=%d\n", SQLCODE);
    EXEC SQL CLOSE C1;
    printf("After cursor CLOSE SQLCODE=%d\n", SQLCODE);
    EXEC SQL COMMIT;
    printf("After COMMIT SQLCODE=%d\n", SQLCODE);

// Here's where the SQLCODE -907 happens:
    EXEC SQL UPDATE cms/"CASE"
             SET case_id = :nNewValue
             WHERE case_id = 0;
    printf("After UPDATE CASE SQLCODE=%d\n", SQLCODE);
    return 0;
}


Tom Hightower
Solutions, Inc
http://www.simas.com





Joe Reinhardt <JReinhardt@Lance.com>
Sent by: midrange-l-admin@midrange.com
03/13/02 11:04 AM
Please respond to midrange-l


        To:     "'midrange-l@midrange.com'" <midrange-l@midrange.com>
        cc:
        Subject:        RE: SQLCODE -907


Since you are doing *after *insert does your trigger program then read in
the record to do the change?

Joe Reinhardt

-----Original Message-----
From: tomh@simas.com [mailto:tomh@simas.com]
Sent: Wednesday, March 13, 2002 11:58 AM
To: midrange-l@midrange.com
Subject: RE: SQLCODE -907


This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Not a red herring, but doesn't really help either.  The trigger time is
*AFTER, trigger event is *INSERT.  We use SQL to 'INSERT INTO' the file,
which causes the trigger to run, which in turn runs the program, which
updates a field in the row we're inserting.

On the SQL 'INSERT INTO' - if we use 'WITH CS' (isolation level: row is
locked until end of current transaction) at the end of it, we get the
SQLCODE -907.  If we leave the 'WITH CS' off, we get no error and the
files are updated like we want but a ROLLBACK does nothing.

What we want is to set an isolation level and not get the -907, and be
able to do a ROLLBACK.  How do we accomplish that?  Do I need to post the
C program to show how the files are being updated?

Tom Hightower
Solutions, Inc
http://www.simas.com





Buck Calabro <Buck.Calabro@commsoft.net>
Sent by: midrange-l-admin@midrange.com
03/13/02 10:27 AM
Please respond to midrange-l


        To:     midrange-l@midrange.com
        cc:
        Subject:        RE: SQLCODE -907


Tom wrote:

>When we looked up -907, we found that it said
>something like "attempt to update same row twice".

DSPMSGD SQL0907 says that a trigger fired which tried updating the same
row.
Does that help, or is it a red herring?
  --buck
_______________________________________________


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.