v8 auto-incrementing

[shaun]

Hi everyone,

Will v8 still require an adaptation of the special coding in the newsletter archive ( http://newsletter.alphasoftware.com/0030305.htm ) to prevent accidental duplicate auto-incrementing numbers on a network?

If so can anyone post a very basic but actual working example in the code archive?

The help file for v8 says:

The reason that the value that displays when the user starts entering a new record is called the "likely" value is that it is not really known until the record is saved what the value will be. For example, say a table has 4 records and two users begin entering a new record. User 1 will see the value of "5" in his new record. User 1 has not yet saved his record. User 2 now starts a new record. he also sees a value of "5". If user 2 saves before user 1 then his record will get the value of "5". If user 1 then saves, the "5" in the record will change to "6".

Does this mean that special coding is no longer required?


Thanks,
Shaun

[Peter.Greulich]

Re: v8 auto-incrementing

As far as I know, it's not an issue in v7. But someone can correct me if I am mistaken.

[shaun]

Re: v8 auto-incrementing

bttt

Could any of the other "heavyweights" of the Alpha world comment on this? Is auto-incrementing now rock solid in A5 v7 and 8?


Thanks

[jkletrovets]

Re: v8 auto-incrementing

I am not one of the "Heavyweights" around here but I have made what I think is a rock solid auto-incrmenting function. It is a modified verision of Cal Locklin's free auto increment function available on his site.

http://www.aimsdc.net/Programs.htm

I am unable at the moment to post my actual code but would be happy to do so if you would like. Basically, I have one table that stores all of my auto-increment numbers for the whole app. Whenever a new record is requested I get an exclusive lock on the auto increment table - get the number and increment it and release the table. The process occurs in fractions of a second so even if 2 users are doing the same exact process the chances are VERY slim there would be a conflict. I have put error checking in to pause the function until an exclusive lock can be made so there should be never be a problem no matter what. I am fairly new to Alpha but I have used a similar function in a networked Foxpro application for over 10 years and never had a problem.

Regards,

Jeff

[oldtony]

Re: v8 auto-incrementing

when you have a change could you please share your code
thanks
old tony

[martinwcole]

Re: v8 auto-incrementing

The fear originally goes back to version 4, when autoincrements were a definite potential problem.

I haven't heard any complaints in V7, but because I had it occur in 4.5 once, and it took me a good while to unravel the records, I still have the fear and still use a manual autoincrement table.

I use Jeff's approach, managing the autoincrement fields for 70 tables, on a 12 user network, with a single table.

[Tim Kiebert]

Re: v8 auto-incrementing

The fear originally goes back to version 4, when autoincrements were a definite potential problem.

I haven't heard any complaints in V7, but because I had it occur in 4.5 once, and it took me a good while to unravel the records, I still have the fear and still use a manual autoincrement table.

I use Jeff's approach, managing the autoincrement fields for 70 tables, on a 12 user network, with a single table.

Martin or/and Jeff,

I have not needed such an aproach as yet but in case I ever do: Is the structure of your single table such that each table you are keeping track of has its own field?

Tim

[martinwcole]

Re: v8 auto-incrementing

say you have a table you name counters
say you have a field named client,n,5
(I am using a numeric counter, and padl() - you could also use a character field here instead)

and say you have a table named clients, with a field named client_id

in clients, in field rules, in events, in record events, at the CanSaveRecord event

put

if a_deleting_record 'don't fire when deleting a record
 end
end if 
t=table.get("clients")
if t.mode_get()=2 'enter
 dim trys as n
 trys=0
 on error goto retry
 t=table.open("counters",FILE_RW_EXCLUSIVE)
 on error goto errors
 t.change_begin()
  t.client=t.client + 1
 t.change_end(.t.)
 n=t.client
 t.close()
 t=table.get("clients")
 t.client_id=padl(alltrim(str(n)),5,"0")
end if 
end
retry:
if trys > 10
 cancel() 'cancel the save
 ui_msg_box("Error","The system is currently busy - try again in a minute!",16)
 sys_send_keys("{esc}") 'the form gets this
 end
end if 
trys=trys + 1
sleep(1/3)
resume 0
end
errors:
err_msg = error_text_get(error_code_get())
line = error_line_number_get()
script = error_script_get()
ui_msg_box("Error", err_msg+" Error occurred at line "+alltrim(str(line,4,0))+ " in script: "+script)
end

[Tim Kiebert]

Re: v8 auto-incrementing

Thanks Martin,

That bit of code is going straight into my 'utilities' folder.

[shaun]

Re: v8 auto-incrementing

So was a major change made in v7? Sorry, I haven't looked at Alpha 5 for a few years now but am getting interested again.

It sounds as though the underlying structure of the new auto-increment is doing exactly what the special coding was previously doing. Is this right? I wonder if any of the Alpha team can comment.

Would it be safe then, on a network, to simply save a new record as soon as it was created without any fear that the auto-incrementing number will duplicate itself?

Perhaps this would be the substance of an updated article for the newsletter?

Thanks,
Shaun

[jkletrovets]

Re: v8 auto-incrementing

Oldtony/Tim

Below is the code from my function. It uses a table called incr_nums to store all the incremented numbers in the app. This includes the unique identifier number for every record of every table as well as numbers like PO Numbers, work order numbers, etc.

Please forgive the verbose comments but since this is my first Alpha app I am making lots of notes to myself.

I only use modal forms with discrete buttons for "Saving" , "Cancelling", etc. - so I put this code on the Save button's On Push event.

An example would be:

tbl.fieldtoincrement=alltrim(auto_incr_c("incr_nums","fieldtoincrement"))

Regards,

Jeff

******** Function is below ********

'Date Created: 24-Aug-2006 05:54:45 PM
'Last Updated: 30-Aug-2006 01:31:39 PM
'Created By : Jeff
'Updated By : Jeff
FUNCTION auto_incr_c as C (incr_table as C,Fldname as C)
'Modification of Cal Locklin's auto_incr function to allow for passing
'field name in the increment table since I am going to use one table
'with multiple fields. Each field will be the increment holder for
'the data tables in the application.
'Date: 08/24/06

'Open the increment table with exclusive access, then get the appropriate number, then close increment table quickly
'to avoid confilct with another user
DIM tries as N
tries = 0
'---------------
Restart_here:
'---------------

ON ERROR goto Not_exclusive
tp = table.open(incr_table,file_rw_exclusive)
'This insures we are on the first (and only record) in the table.
tp.fetch_goto(1)
fld = tp.field_get(Fldname) 'Get a pointer to the appropriate increment field in the record.
curval = alltrim(fld.value_get())
newval = str(val(curval)+1)
'Store the next value in the increment table and quickly close the table to avoid conflicts with other users.
tp.change_begin()
fld.value_put(newval)
tp.change_end()
tp.close()

' This passes the return value. Must be same name as the function
auto_incr_c = newval
'From Dr. Wayne Xbasic for Everyone page 77. We must now clear the error trap and allow the function
'to exit. Prevents the possibility of an endless loop.
ON ERROR goto 0
EXIT FUNCTION

'---------------
Not_exclusive:
'---------------
' Create a loop if table was locked to allow time for whoever has it locked to finish and release the table
WHILE tries < 4
tries = tries+1

' Now pause the script for 2 seconds to allow the table to be closed and goto restart to try again
sleep(2)
GOTO restart_here
END WHILE
' Function just left the loop because it has tried 3 times and could not get exclusive access
' Now show message to user. This should NEVER come up.
ui_msg_box("Increment Failure","Error! - An exclusive lock could not be achieved on the increment table. Contact Jeff!",UI_STOP_SYMBOL)
ON ERROR goto 0
EXIT FUNCTION


END FUNCTION

[Tim Kiebert]

Re: v8 auto-incrementing

Thankyou! Jeff.