Thanks Steven. Ted pointed me at a better solution and I've already implemented it...works like a charm.
The original problem was that my client wants a sequential numbering system used for their sales documents so that they can account for all sales. Originally I had a field with an Integer that would be read, then incremented by the application, but in the last 6 months there were two occurrences of the same # being used for two sequential documents. So I tried the locking of the table containing the integer value, but now I'm randomly getting "tables not locked" after releasing the lock and trying to access other tables in the database. This has happened on average about 3 times a week for the last month. So, no more locking, no more application incrementing...I've set up a separate single field table that auto-increments and using just two lines of code the deed is done. As my thanks to Ted and you have stated, it's good to look at things outside the VFP lens. Mike > On Sat, Jul 30, 2011 at 4:29 PM, Mike Copeland<[email protected]> wrote: > >> Here's a curious problem with VFP9 using MySQL 5.1 as a back end... >> >> >> >> Randomly, I get an error when trying to access tables in a MySQL >> database. The error is >> >> "Table 'X' was not locked with LOCK TABLES" (where X is one of >> several different tables) >> >> >> lock tables systemtable write >> >> select docid from systemtable >> >> update systemtable set docid=docid+1 >> >> unlock tables >> > ------------- > > You are treating mySQL like VFP. Sorry but it doesn't work that way. > > Can you set mySQL tables to generate their own ID and your insert > achieve that "new" id? > > I know that you can write a stored procedure to do this in one > statement instead of multiple VFP messages. > > Can you give us ALL the code to run the above lines? Are you doing a > connection one time and then submitting all of these statements one at > a time? > _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
