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
