Question on movinge from VFP to MSSQL

[andreateh]

I', plan to move from the VFP database to MSSQL server. But i have few question before i move to MSSQL server.

In VFP i used to write my single file maintainance program in a form with a pageframe with 2 pages. In page 1 contain a grid which is use to display the entire table's record. In here user can scroll the grid to locate certain record and edit it on second page.

Q1. If i move to SQL server. do i need to issuse the SELECT * from tableA to select all the record from the tableA into a cursor and put it in the grid ?

Q2. So far i have learn that VFP only tranfer the Index from server to client. If i select all the record from the SQL server will it transfer entire table's record to the client ? Will it become very slow?

Q3. Do anyone have any comment this single file maintenance program ? Which Page1 : Have a grid contain all the records from the table. Page2 is use to edit the selected record from Page1.

Q4. Where can i get the article or sample on how to use MSDE with VFP ?

 

[MarciaAkins]

Hello Andreateh.

>> Q1. If i move to SQL server. do i need to issuse the SELECT * from tableA to select all the record from the tableA into a cursor and put it in the grid ? <<

This is something that you definitely do not want to be doing. When you move to using a back end server, like SQL server, you need to change your mind set about just letting the user browse all of the record in a table. You need to allow the user to enter some search criteria so that you can pull down a small subset of the data based on this search criteria.




Marcia G. Akins

http://www.TightlineComputers.com

 

[andreateh]

Thanks for advice.

 

[andreateh]

Hi Marcia G. Akins,

One more question on moving from VFP to MSSQl.

Our company currently application is using RLOCK() to Lock / Reserved a record during the user doing the entry part. (Where the user will need to choose few location to store few types of goods. When user select location A, location B and location C to store some goods. These 3 records in the location table will be Rlock and a lock flag will be place on this 3 records.And untill the user press the save button and complete update the records these 3 record will be unlock.)

This design is request by the user. They don't want to make any changes during they press the save button, if discover that the location they choosed is change (which this method recently use by in optimistic locking method)

So if move to mssql is this method still can be use ?

Do i need to connect to the server to update the location table(the lockflag field) everytime user choose the location? ( The user may choose the location and then unchoose the location. In this case do i need to update the location table in MSSQL when user choose it, and update the location table again when user unchoose it ?

 

[MarciaAkins]

Hello Andreateh.

>> This design is request by the user. They don't want to make any changes during they press the save button, if discover that the location they choosed is change (which this method recently use by in optimistic locking method) <<

I know that you don't want to hear this, but you are going to have to change the way that you are doing things and your end users will have to get used to it ;-)

Pessimistic locking strategies really have no place when it comes to working with back end servers.




Marcia G. Akins

http://www.TightlineComputers.com

 

[andreateh]

Hi ,Marcia G. Akins

Thanks for fast reply. Just have one idea from my friend. But i don't know wherethere it is efficient or not, so need advice from expert

Can i make a "soft lock" on the record ?
What i mean "soft lock" is add 2 field in the location


field name type
lockdt datetime
locked logical


So everytime user select a location. these 2 field is update in the server. The entry program will check these to field. if both are not empty it mean these record is reserved by others. If the value in lockdt > maxlocktime. These record will be able to release to others user.


But these will make the table in backend server update very frequent.(everytime user select a location from the entry form, a record in the location table will be update).

Is these method effient to reserved the record ? and will this method decrease the back end server a lot?

 

[craigber]

Not a good idea. If the program should crash, the lock will stay. You should look at some SQL books and web sites and read about locking strategies.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[andreateh]

Yup,If the program crash the lock will stay there.

If the program found that both field is not empty. It will then check the lockdt as below

If the value in lockdt > maxlocktime.

If line above true, mean this record have exceed the maximun reserved time. And then when another user try to reserved this record. It will success.

The algorithm is like below.

IF locked
   IF lockdt > maxlocktime
         return .T.
   else
         return .F.
   endif 
ENDIF
return .T.

If return .T. mean that the record is available for other user. else the record is reserve by others user.

 

[MarciaAkins]

Hi Andreateh.

>> so need advice from expert <<

< lol >!!!! I am by no means a SQL Server expert, but have worked with it enough to know that you do not want to be using a pessimistic locking stretegy.

I truly think that the best thing you can do is to try and manage the expectations of your users.

Maybe you can post this question in the SQL Server forum and get some better advice from a real expert ;-)



Marcia G. Akins

http://www.TightlineComputers.com

 

[andreateh]

Thanks for advice,MarciaAkins.