RE: SQLCODE -907

[tomh@xxxxxxxxx]

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.