�eems this is the same as the code in the store with the same issues. on 5/22/02 9:51 AM, Troy Sosamon at [EMAIL PROTECTED] wrote:
> Transaction locking and table locking produce a lot of unnecessary overhead > for your database. Something this simple should not need to put that mutch > overhead on the system. Bill Downall a few years ago came up with the most > creative, simpelest, least overhead multiuser numbering methods I have ever > seen. No locks required. Here it is. You need a control table w/ 1 col > and 1 row in it to hold your counter. Lets call the table "control" and our > last number used "counter". > > LABEL TRY_AGAIN > Select counter into mycountervar from control -- get the last # used > set var myNEWcounter = (.mycountervar + 1) > > update control set counter = myNEWcountervar where counter = .mycountervar > IF sqlcode = 0 THEN > -- SUCCESS, YOU ARE DONE AND USE THE VALUE myNEWcountervar > ELSE > -- YOU DID NOT GET YOUR UPDATE, TRY AGAIN > GOTO TRY_AGAIN > ENDIF > > This always works, and has very little overhead. You should put some code > in here in the event of a system problem so it doesn't get stuck in an > infinite loop. > > Troy Sosamon > > > ===== Original Message from [EMAIL PROTECTED] at 5/22/02 6:42 am >> I would agree it seems to me that using the begin and end transaction will >> lock things till I complete so I safely retrieve the correct number. >> >> Dan >> on 5/22/02 8:31 AM, Stefan Gonick at [EMAIL PROTECTED] wrote: >> >>> I feel compelled to point out a problem with this system. I >>> originally used the counter table functionality for all of my >>> projects but have completely dropped it now. The problem >>> that I had was that occasionally there would be a database >>> glitch that would leave a counter in a locked state. This >>> happened regularly with MS Access (I know), but it would >>> also happen with MS SQL Server, which I thought would be >>> safe. Each time it happened it would mess things up until >>> it was reset. I found that it is much safer to use an autoincrementing >>> identity field and then look up the value, either using transactions >>> or with userreference stored in the table. My 2 cents. >>> >>> Stefan >>> >>> At 07:53 AM 5/22/2002 -0400, you wrote: >>>> One of the beauties about the method that was written for the storefront is >>>> that if a particular table does not have a counter created for it, it will >>>> create one automatically. The counter table has three fields: counter_name >>>> (varchar 35 characters), counter_value (integer), and locked (integer). >>>> >>>> The calling tag is: >>>> >>>> <@ASSIGN local$NewID "<@CALLMETHOD scope$variable 'GetNextID(xxx)'>"> >>>> >>>> where 'scope' and 'variable' are what you've declared your object instance >>>> to be and 'xxx' is the name of the counter you wish to call. I use the >>>> table >>>> name for the name of the counter to keep things easy to remember. To use >>>> the >>>> value brought back by the call to the counter, use <@VAR NewID >>>> SCOPE=Local>. >>>> For your situation you could have this: >>>> >>>> <@ASSIGN local$NewCourseID "<@CALLMETHOD scope$variable >>>> 'GetNextID(course)'>"> >>>> <@ASSIGN local$NewCourse_CrosslinkID "<@CALLMETHOD scope$variable >>>> 'GetNextID(course_crosslink)'>"> >>>> >>>> in a results action immediately before the insert for the course table. You >>>> would then have two variables: >>>> >>>> <@VAR NewCourseID SCOPE=Local> >>>> >>>> would contain the courseID and: >>>> >>>> <@VAR NewCourse_CrosslinkID SCOPE=Local> >>>> >>>> would contain the course_crosslinkID. >>>> >>>> Hope this helps, >>>> >>>> Steve Smith >>>> >>>> Skadt Information Solutions >>>> Office: (519) 624-4388 >>>> GTA: (416) 606-3885 >>>> Fax: (519) 624-3353 >>>> Cell: (416) 606-3885 >>>> Email: [EMAIL PROTECTED] >>>> Web: http://www.skadt.com >>>> >>>> >>>> -----Original Message----- >>>> From: [EMAIL PROTECTED] >>>> [mailto:[EMAIL PROTECTED]]On Behalf Of Dan Stein >>>> Sent: May 21, 2002 2:34 PM >>>> To: Multiple recipients of list witango-talk >>>> Subject: Re: Witango-Talk: Next WiTango_SQL conundrum >>>> >>>> >>>> The first suggestion sounds interesting can you expand? >>>> Is the counter table pre populated how do I do GetNextID method? >>>> I'll try and dig up the demo. >>>> >>>> Dan >>>> >>>> on 5/21/02 1:28 PM, Steve Smith at [EMAIL PROTECTED] wrote: >>>> >>>>> Two suggestions. First, why not go with a counter table and use the >>>>> GetNextID method that was included in the TCF for the storefront demo. It >>>>> retrieves the next id which you can then use in your insert for the course >>>>> table AND for the course_crosslink table. It will guarentee that you have >>>>> the right value in both. >>>>> >>>>> The other suggestion if you are determined to not use a counter table is >>>> to >>>>> use the same values for the insert in your search rather than searching >>>> for >>>>> max course_ID. You run a far less chance of obtaining the wrong course_ID >>>> if >>>>> use the exact same <@ARG xxx> values for the search criteria that were >>>> used >>>>> in the insert action. >>>>> >>>>> Hope this helps, >>>>> >>>>> Steve Smith >>>>> >>>>> Skadt Information Solutions >>>>> Office: (519) 624-4388 >>>>> GTA: (416) 606-3885 >>>>> Fax: (519) 624-3353 >>>>> Cell: (416) 606-3885 >>>>> Email: [EMAIL PROTECTED] >>>>> Web: http://www.skadt.com >>>>> >>>>> >>>>> -----Original Message----- >>>>> From: [EMAIL PROTECTED] >>>>> [mailto:[EMAIL PROTECTED]]On Behalf Of Dan Stein >>>>> Sent: May 21, 2002 11:58 AM >>>>> To: Multiple recipients of list witango-talk >>>>> Subject: Witango-Talk: Next WiTango_SQL conundrum >>>>> >>>>> >>>>> OK I have fixed everything else data is where it needs to be and everyone >>>>> was a big help. >>>>> >>>>> Here is my next issue. >>>>> >>>>> When I create a course with create_course.taf after the insert there is a >>>>> DBMS that gets Max course_ID and set's it to a local variable. >>>>> >>>>> That is because I need to know what the course_ID that was assigned by SQL >>>>> to the inserted course. I then use this to insert into the >>>> course_crosslink >>>>> table the course_ID and some other values. >>>>> >>>>> It looks like sometimes it failed to set the correct course ID I guess >>>> there >>>>> was some fast enough data entry going on from more than one person so the >>>>> Max did not get the right number. At least it is the only thing I can >>>> think >>>>> of that would cause the missing course_ID's in course_crosslink. Since the >>>>> insert action comes right the DBMS I can see how it could fail without an >>>>> error message to the user which I think I would have heard about. >>>>> >>>>> I know there is some way to lock the table or someway to make sure I >>>>> retrieve the correct value but I don't know how to write it in WiTango or >>>> as >>>>> a DBMS. >>>>> >>>>> >>>>> >>>>> -- >>>>> Dan Stein >>>>> Digital Software Solutions >>>>> 799 Evergreen Circle >>>>> Telford PA 18969 >>>>> 215-799-0192 >>>>> 610-256-2843 >>>>> Fax 413-410-9682 >>>>> FMP,Tango, EDI,SQL 7 >>>>> [EMAIL PROTECTED] >>>>> www.dss-db.com >>>>> >>>>> >>>>> ________________________________________________________________________ >>>>> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] >>>>> with unsubscribe witango-talk in the message body >>>>> >>>>> ________________________________________________________________________ >>>>> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] >>>>> with unsubscribe witango-talk in the message body >>>>> >>>> >>>> -- >>>> Dan Stein >>>> Digital Software Solutions >>>> 799 Evergreen Circle >>>> Telford PA 18969 >>>> 215-799-0192 >>>> 610-256-2843 >>>> Fax 413-410-9682 >>>> FMP,Tango, EDI,SQL 7 >>>> [EMAIL PROTECTED] >>>> www.dss-db.com >>>> >>>> >>>> ________________________________________________________________________ >>>> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] >>>> with unsubscribe witango-talk in the message body >>>> >>>> ________________________________________________________________________ >>>> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] >>>> with unsubscribe witango-talk in the message body >>> >>> ======================================================== >>> Database WebWorks: Dynamic web sites through database integration >>> http://www.DatabaseWebWorks.com >>> >>> ________________________________________________________________________ >>> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] >>> with unsubscribe witango-talk in the message body >>> >> >> -- >> Dan Stein >> Digital Software Solutions >> 799 Evergreen Circle >> Telford PA 18969 >> 215-799-0192 >> 610-256-2843 >> Fax 413-410-9682 >> FMP,Tango, EDI,SQL 7 >> [EMAIL PROTECTED] >> www.dss-db.com >> >> >> ________________________________________________________________________ >> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] >> with unsubscribe witango-talk in the message body > > ________________________________________________________________________ > TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > with unsubscribe witango-talk in the message body > -- Dan Stein Digital Software Solutions 799 Evergreen Circle Telford PA 18969 215-799-0192 610-256-2843 Fax 413-410-9682 FMP,Tango, EDI,SQL 7 [EMAIL PROTECTED] www.dss-db.com ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
