VFP 3.0 questions - SQL commands, handling Deleted records in multi-user environment 
Author Message
 VFP 3.0 questions - SQL commands, handling Deleted records in multi-user environment

Edward is rumored to have written:

> 1)  Is there a way to trap for errors, especially ones related to
> locking/access, after execution of a SQL Select (other than the
> result table/cursor being empty), SQL Insert, and SQL Update?
> If any errors are encountered in a multi-record SQL Update,
> are some or none of the records updated?

I would use VFP's buffering scheme and views. Then,
do something like (for optimistic row buffering):

IF !=TableUpdate(.T.)
   =MessageBox("Multiple users changing same

If multiple records are being changed, TableUpdate
will write the ones it can and return false if any
of them could not be written, then the TableRevert
will undo those changes to the buffer. Usually, I
work this on a first-come-first-serve basis. I let
the first user who locked the record write the
changes and tell the later users to try it again
(and see the updated data).

> 2)  When a record is deleted, but packing is not an option at the time
> due to another user accessing the table, how would you recommend
> handling "uniqueness of index violated" from someone adding or
> changing a record to the same key as the deleted record?

Again, if you use views, packing is not a problem,
the user isn't really accessing the table(except
for when the view is being created).

I don't know if this will work in your app, but you
could also change the SET DELETED option....

> Similarly when doing a SQL insert with a dummy blank or zero key
> (as the key is not known at the time but the user wants to be sure
> that that insert is placed immediately in the table), and another user
> is doing another insert?     i.e. is there a better way than trying to
> test for, or to re-use, deleted records in these scenarios?

What do you mean 'reuse'? If you use the buffering
(and if not), VFP figures out where to place a new
row, you don't need to worry about it. You are just
writing canges to the buffer and not to the table
until you issue the TableUpdate. In fact, I don't
think it will even check for uniqueness violation
until it writes to the table.

> 3)  How accurate is my assumption that a SQL Select automatically
> opens up the table(s) in a new, unused work-area, locks the whole
> table during execution, and leaves the table open and selected upon
> termination of the SQL command?

I'm not sure about how it does that. I think it
goes to a cursor. If your buffering is set to
optimistic, I wouldn't think it would ever lock the
whole table, but I could be wrong...

> P.S.  And if any of my questions are already explained adequately in
> Help, please excuse my ignorance as you point out this fact to me,
> thanks!

Multiuser issues are not easy to pick up the first
time, I know that from experience <g>. I did find
the docs and the help files to be usefull. I think
there is a large section in the Professional
Features Guide about these things as well as some
White Papers on the MS site.


Software Engineer
Strategic Advantage, Inc.      Fax: (612) 871-1151

http://www.*-*-*.com/ ~vitus/

Sun, 27 Dec 1998 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. VFP 3.0 multi-user setup: how?

2. Multi user in VFP 3.0

3. Question on deleting Foxpro records with ODBC and SQL

4. No records when excluding deleted records in SQL

5. Allowing users to use last record.. VFP 3.0

6. Good way to handle deleted records?

7. SQL Command in VFP 3.0

8. Mult-User/Multi-Record updates (fixing an old program)

9. User cannot delete records (sometimes)

10. How to recycle table records for multi users

11. Doubt in VFP USE command and VFP SQL Select command

12. Record corrupt with replace command in multiuser app.


Powered by phpBB® Forum Software