Michael,
What does your routine do if no lock can be obtained or if the user presses escape? Seems like it would pass back a blank. Do you check for that?
Do you ever recall records? If so, you could be issuing duplicate numbers. If you're using a routine like this you don't need the FOR NOT DELETED() filter on the index. Filtered indexes are not optimizable.
Finally, is your key numeric or character? How do you reconcile
lcID = ""
with
REPLACE setup.key_value WITH lcID + 1
?
>At this point I have a integer key generation on the backend in the tables 'DefaultValue' snippet which calls a stored procedure. All tables are accessed through remote-views via odbc. There is a primary key index set for the table with a !deleted() for expression. The duplication of keys hardly happens and I think it's happening when a process brings data down then writes it back again through the remote view. It could be possible that a user's remote-view could be corrupted so therefore writes back bad data but I don't see why VFP would even allow the dup key. I do know that our network is having multiple serious collisions and is going to be fixed soon. Thanks for the advice.
>
>The stored procedure looks like this:
>
>
>
FUNCTION NewID(tcAlias)
> LOCAL lcAlias, ;
> lcID, ;
> lcOldReprocess, ;
> lnOldArea
>
> lnOldArea = SELECT()
> IF PARAMETERS() < 1
> lcAlias = UPPER(ALIAS())
> ELSE
> lcAlias = UPPER(tcAlias)
> ENDIF
>
> lcID = ""
> lcOldReprocess = SET('REPROCESS')
>
> *-- Lock until user presses Esc
> SET REPROCESS TO AUTOMATIC
>
> IF !USED("SETUP")
> USE Setup IN 0
> ENDIF
> SELECT setup
>
> IF SEEK(lcAlias, "setup", "key_name")
> IF RLOCK()
> lcID = setup.key_value
> REPLACE setup.key_value WITH lcID + 1
> UNLOCK
> ENDIF
> ENDIF
>
> SELECT (lnOldArea)
> SET REPROCESS TO lcOldReprocess
>
> RETURN lcID
>ENDFUNC