We have a client that has several folks doing simultaneous order entry on our application. He reports a problem I sure some of you folk have encountered. I enclose his email to me asking for your help:

*

Good talking to you yesterday.

This is more an FYI note than an appeal for help, although if you have any ideas I'd like to hear them.

I had mentioned a record-locking issue a couple of weeks ago in an e-mail to you, and here are the details:

If multiple users try to open the same record, OI Server Service catches the access and notifies the users, offering read only views. This applies to any records that have been committed to the database.

HOWEVER……

If one user completes order #123, closes it, and Buyer offers order #124, order #124 does not exist in the Buyer database, so when user #2 opens Buyer and is assigned an order number, it is also #124. I have tried this and it does happen.

User #1 writes his #124 order, unaware that user #2 is working on the same order number.

User #1 finishes first, commits the record #124 to the database.

User #2 finishes their version of order #124 and commits it to the

database.

User #1 has their order #124 overwritten by the last committed transaction and it does not exist.

Buyer is running on the server, so actually only one application is

running. Is there any way a record-number assignment routine could be

developed?

OI's record-locking is performing as designed, this appears to be a

function of the Buyer application.

Could the program limit assignment of order numbers?

Again, another complex networking issue.

Appreciate your "(open) insight" into this problem.

*

Thanks for any ideas!

Blair

Blair,

I distinctly remember that issue has been raised here before… (doing a search) …uh… …by me! (Blush).

- Oystein -

Blair,

As Oystein has mentioned, this is an issue that has been raised before. Some would contend that this is a carry-over from AREV's problems with sequential key counters.

Unfortunately (or fortunately) none of our clients have experienced this issue with OI. Yes, users can get the same default key to appear but only one will get the record lock. I assume you are using the SEQKEY default and not running your own custom method?

If you search enough on this board and the "public" one you should find many posts that describe how using a default of "" instead of the SEQKEY and then programmatically assigning the key is done. People have claimed this avoids the problems with SEQKEY.

dbakke@srpcs.com

SRP Computer Solutions, Inc.

Hi Blair,

  In the fwiw (for what its worth) category, we had this problem and finally resolved it by using our own code to retrieve the next key and update %SK%  at once.  This does cause some 'holes' in the list of keys in the database, as a user may get a key and then not 'save' the new record (close the form without saveing).  But, it avoids the case of 2 users having the same key and the "2nd" user being unable to save is new record without overwriting the first user's record.  Let me know if I can help more.  paxton@arcscustomsoftware.com

[url=http://www.arcscustomsoftware.com/]ARCS, Inc.

Using ] as the default in KeyField

In the write event script do:

call MyProg("WRITE","@SELF")

return 0

Code in MyProg

WinId=@Window ;*]")

	ID=]"

end	

if]" then

	gosub WriteWithNextKey

	if EventError then return

	ID=NewKey

end else

	call set_eventstatus(0)

	forward_event()

	EventError=Get_EventStatus(eventcode)

	if EventError then return

end

• *POST WRITE (Post write code here) return ! WriteWithNextKey: !**

• Determine the key controls

keyCount=count( keyMap@, @FM) + ( keyMap@  NULL$)

keyControls=NULL$

for keyNbr=1 to keyCount

        keyControls := keyMap@ : @RM

next keyNbr

keyControls-1,1=NULL$

oldKey=get_property( keyControls, 'DEFPROP')

if oldKey-1,1=@RM then

        oldKey-1,1=NULL$

end

convert @RM to '*' in oldKey

locate keyMap@ in controlMap@ using @fm setting pos else

        return 0

end

tableName=controlSemantics@ 

open tableName to hTableName else

        return 0

end

      DictKey=%SK%"

NextNo= get_next_sk(TableName)

NewKey=NextNo

rowLocks@=newKey

convert '*' to @FM in newKey

OrigResultRow@=newKey : @FM : ""

convert @FM to @RM in newKey

properties=str( 'DEFPROP' : @RM, keyCount)

properties-1,1='

unUsed=set_property( keyControls, properties, newKey)

call Set_EventStatus(0)

forward_event()

EventError=Get_EventStatus(eventcode)

if EventError then

	open "DICT",TableName to hDict then

		Sk=DictKey

		loop until lock_record(hDict,Sk);repeat

		readv SkKey from hDict,Sk,1 then

			if SkKey=NextNo+1 then

				writev NextNo to hDict,Sk,1 else null

			end

		end

		unlock hDict,Sk else null

	end

	unlock hTableName,NewKey else null

	NewKey=]"

	rowLocks@=Null$

	OrigResultRow@=]" : @FM : ""

	properties=str( 'DEFPROP' : @RM, keyCount)

	properties-1,1='

	unUsed=set_property( keyControls, properties, newKey)

	setproperty('@','ID',]")

end else

	msg(@Window,"Your Order number is ":NewKey)

end

return

Gentlemen,

Thanks for your posts! You have most helpful. Sorry it took so long to get back here and thanks you. Things have been a bit hectic and this fell to the back burner. I'm sure we can get the problem solved from here with your suggestions.

Blair

View this thread on the Works forum...