VFP and SQL server 2005. Please HELP me out!!!  
Author Message

PostPosted: Visual FoxPro General, VFP and SQL server 2005. Please HELP me out!!! Top


for the 3 weeks I m on a research nightmare.  I m trying to use SQL Server 2005  and VFP and I don't know why but it is really difficult for me!!! I ve experience on VFP and SQL but very long time ago!

What I m trying to do is a multiuser application were concurrent transactions will occur in a table.

I ve read all about ISOLATION LEVELS etc. as much as my stomach can take about Microsoft SQL Server 2005. But in case of the following scenario I can't figure out what to do:

A.USER A is taking a snapshot of table Customers.

B. USER A, in Session 1 is taking a cursor of Customers tables, bringing it to the forntend (frontend will be VFP 9) and editing Customers record where Customers.id=1001.
Q1.Here I need someone please to explain me how I can place a row lock to this record (where Customers.id=1001). Is this done just by Isolationlevel and if yes, which one should I use
Q2.Does this means that noone else can touch this row (delete, update) from another session

C. USER B, in Session 2 is taking a cursor of Customers tables as well, and bringing it to the forntend (frontend will be VFP 9). 
Q3. Will USER B will be able to take a cursor of customer tables (because of the lock by session 1). Should I use a specific IsolationLevel

Q4. Generally what type of isolationLevel should I use   I suppose that the SERIALIZABLE is the most safe. Could I just use that as default for all statements

Any examples (including isolation etc) in VFP or SQL !! !!!! :).

Thanks for your help!


PS. I could use MySQL 5.0 if someone could so me how instead of Microsoft SQL Server 2005 Express. I ve read all the documentation but most of the VFP commands are SQL server oriented.

Visual FoxPro2  

PostPosted: Visual FoxPro General, VFP and SQL server 2005. Please HELP me out!!! Top


It seems you're thinking in DBF files while you're going to use an SQL server. Forget manually initiated row or table locks. The best is you never hear about lock problems ;) Write your stored procedure to modify or delete a record in SQL server and just execute. If you want to check if a record is modified in the background by someone else use timestamp fields.

Craig Berntson

PostPosted: Visual FoxPro General, VFP and SQL server 2005. Please HELP me out!!! Top

You need to think very differently when working with client server as opposed to Fox tables. You should almost never return the entire customer table to the user. They only work with one record at a time. Give the user a query screen to get only the desired data. For example, they could ask for all customers with the last name of Smith. You return some fields for all Smiths in a grid. The user scrolls through looking for the one they need. When they find it, do another query to get all the needed data for editing. The user edits, then saves the record. At that time you write the record back to the table.

It sounds to me like the issue isn't locking, but application design. Perhaps some additional research into C/S design will help. In addition to the VFP docs, here are some places to get started. Some of these articles are a bit dated and talk about some technologies that are no longer used (RDS for example), but still contain some good information.


There are some sample chapters you can download from http://www.hentzenwerke.com/catalog/csvfp.htm