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