Funny you should say that because while with EveryWare we had a support tracking system that was developed by Dave Shelley which used an autoincrementing identity field which consistently gave us fits because it would become corrupt and start issuing duplicate values.
I think it shows that there is no system which is 100% foolproof. As developers, we must come up with the best solution and then try to foresee any problems that might occur and create ways to handle them without impact to the end user. 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 Stefan Gonick Sent: May 22, 2002 8:31 AM To: Multiple recipients of list witango-talk Subject: RE: Witango-Talk: Next WiTango_SQL conundrum 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 ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
