FUNCTION NewId && Returns next id value for passed key field. LPARAMETER tcIdField && field or key to increment LOCAL lcFieldName, lcFilter, lcResult, lnConnHandle, lnNewId, lnOrigArea, lnRetVal lcFieldName = UPPER(tcIdField) && set view parameter lcFilter = [UPPER(cfieldname)=?lcFieldName] && filter for SELECT and UPDATE lnOrigArea = SELECT(0) && area to return to lnConnHandle = DefaultConnection() && get connection handle IF lnConnHandle<0 RETURN -1 ENDIF DO WHILE .T. && loop until update successful =SQLEXEC(lnConnHandle, ; [SELECT * FROM UniqueId WHERE ]+lcFilter, ; 'UniqueIdV') && (re)select record into view SELECT UniqueIdV lnOrigId = nLastNumber && store original value for update comparison lnNewId = nLastNumber+1 && get next value * Use "key and modified fields" comparison for update. * If record not found, record has changed, program loops to try again. lnResult = ; SQLEXEC(lnConnHandle, ; [UPDATE UniqueId SET nLastNumber=?lnNewId ; WHERE ]+lcFilter+[ AND nLastNumber=?lnOrigId]) IF lnResult>0 EXIT && exit loop if successfully updated ENDIF * If program got this far, try again WAIT WINDOW NOWAIT "Waiting for "+lcFieldName+" record lock" ENDDO WAIT CLEAR =SQLDISCONNECT(lnConnHandle) SELECT (lnOrigArea) && restore original area RETURN lnNewIdThe test program (simultaneously running in 2 separate VFP sessions) is as follows...
LPARAMETER tcTable SET SAFETY OFF && to overwrite existing table CLOSE DATA ALL CREATE TABLE (tcTable) (nId I) LOCAL X, lnNewId FOR X=1 TO 1000 lnNewId = NewId('nTestId') INSERT INTO (tcTable) (nId) VALUE (lnNewId) ENDFOR WAIT WINDOW NOWAIT tcTable+" loaded"