Locking problems
Is there a way of getting the nextnumber in a series (for example invoice
numbers) out of a database ? I should mention that the number is not a
pure integer, hence using serial types does not work. We could build
a dummy column without the non-numeric prefix, but then this means
changing a lot of code ...
We use the following routine :
----------------------------------------------------------------------
{
Desc : Utility to get the next number from table "numbers"
Returns the number to be used or -1 if error
}
{
# test routine
DATABASE findbs
MAIN
DEFINE i INTEGER
BEGIN WORK
CALL get_next_num (ARG_VAL(1)) RETURNING i
DISPLAY i
COMMIT WORK
END MAIN
}
FUNCTION get_next_num(v_type)
DEFINE v_type char(3),
startnum integer,
nextnum integer,
maxnum integer
#LOCK TABLE numbers IN EXCLUSIVE MODE
# It is either the above or DECLARE cursor FOR UPDATE (as below)
DECLARE c_numbers CURSOR FOR SELECT next_num,max_num,start_num
INTO nextnum,maxnum,startnum FROM numbers WHERE code = v_type
FOR UPDATE
OPEN c_numbers
FETCH c_numbers
IF(sqlca.sqlcode) THEN
RETURN(-1)
END IF
IF(nextnum > 0) then
IF maxnum IS NOT NULL AND nextnum >= maxnum THEN
LET nextnum = startnum
ELSE
LET nextnum = nextnum + 1
END IF
ELSE
LET nextnum = 1
END IF
UPDATE numbers SET next_num = nextnum
WHERE CURRENT OF c_numbers
IF(NOT sqlca.sqlerrd[3]) then
return(-1)
END IF
RETURN(nextnum)
END FUNCTION
----------------------------------------------------------------------
Unfortunately to enable locking, we need to put the above routine
in a transaction, which causes error like
Date: 12/10/1994 Time: 11:16:44
Program error at "nextnum.4gl", line number --.
SQL statement error number -244.
Could not do a physical-order read to fetch next row.
SYSTEM error number -107.
ISAM error: record is locked.
because of longish transactions eg:
-----------------------------------------
DECLARE c_cur CURSOR FOR SELECT blah,blah
BEGIN WORK # this is required for locking to work in a logging DB
FOREACH c_cur INTO x
CALL nextnum('XYZ') RETURNING x
INSERT x, blah, blah INTO blah,blah
END FOREACH
COMMIT WORK
-----------------------------------------
Anybody have better strategies ?
-----------------------------------------
Ti Lian Hwang
email : tilh@sin-co.sin-ro.dhl.com
-----------------------------------------