>I am creating a stored procedure to automatically update the primary key value whenever a record is created in the table named XEPCBOAT. I set the default vaue of xepcboat.boatid to UpdateKey('xepcboat').
>
>This worked great until I put the record locking in. When I try to append a record now I get the error message "Cannot write to the record because it is in use.". If I take out the RLOCK it works great but I need to lock the record until the update to sysvars is complete to keep from creating duplicate primary key values.
>
>The function code is below. Anybody have any ideas or a better way to accomplish what I'm trying to do?
>
>FUNCTION Updatekey
>PARAMETERS lcTable
> LOCAL liKeyValue
> USE SYSVARS IN 0
> DO CASE
> CASE UPPER(lcTable) = 'XEPCBOAT'
> RLOCK('sysvars',1)
> REPLACE sysvars.boatid WITH sysvars.boatid + 1 in sysvars
> liKeyValue = sysvars.boarit
> UNLOCK('sysvars')
> ENDCASE
> USE IN SYSVARS
>RETURN liKeyValue
Ken,
Set exclusive off
Set multilocks on
And instead of rlock() use :
do while !rlock() ...unlock...enddo.
With set reprocess to 1, and some delay in "do ... enddo" routine this works.
An easier way is to make PK value char and let it have default value of sys(2015). For multiuser you could stuff its first character with station Id.
Cetin