|
<Charles>
You should be using a UDF to retrieve the "next number"..
Your UDF could ensure that commitment control is use to lock the record.
Honestly, I'd probably go with an external UDF in RPG as the "read
w/lock,
then update" is more straight forward.
If you want to do it in SQL, you'd need to declare a cursor, fetch
from it
and UPDATE WHERE CURRENT OF CURSOR...
Charles
</Charles>
For the SQL way you should start with the update, to avoid elementar
deadlock situations.
An example from my open Source site:
-- erstellen mit:
-- D*B RUNSQLSTM SRCFILE(QSQLSRC) B*D
-- D*B+ SRCMBR(GETKEY) B*D
-- D*B+ NAMING(*SQL) B*D
-- D*B+ DFTRDBCOL(KURSSQL) B*D
-- generieren unique Key
create function GET_KEY
(
I_TABLE VARCHAR(18)
, I_FIELD VARCHAR(18)
)
returns int
language sql
modifies sql data
set option dbgview = *LIST
begin
declare O_OUT int;
declare sqlcode int;
update AUTOINC
set HIGHVAL = HIGHVAL + 1
where TABLENAME = I_TABLE
and FIELD = I_FIELD;
if sqlcode = 100
then set O_OUT = 1;
insert
into AUTOINC
values(
I_TABLE
, I_FIELD
, O_OUT
);
else
select HIGHVAL
into O_OUT
from AUTOINC
where TABLENAME = I_TABLE
and FIELD = I_FIELD;
end if;
return O_OUT;
end
;
commit
;
Dieter
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.
midrange.com
