Guidance Required for VFP - MSSQL upgradation...  
Author Message
Markish





PostPosted: Visual FoxPro General, Guidance Required for VFP - MSSQL upgradation... Top

Hi all,

        I've developed a VFP application in vers. 6.0. Now that the client wants the same with MS SQL / Oracle. Please someone tell me the list of atricles and other notes i've to go through..... I'm ok with SQL querries....

Please suggest me regarding the -Remote views, SPT's......

The application as a whole has about 2 GB of data in VFP.....

It's a multi-user application....running on VFP 6.0 ./ WIn2000 Platform.

 Thanks in advance...

-Markish




Visual FoxPro2  
 
 
CetinBasoz





PostPosted: Visual FoxPro General, Guidance Required for VFP - MSSQL upgradation... Top

If it's possible upgrade to VFP9. VFP9 has cursoradapter class which makes it much easier to deal with multiple data sources, use stored procedures as sources etc.

In VFP6 and later you can use Remote View and SPT. Remote Views are easier for the developer because VFP handles most the tasks for you, however you have less control upon it. SPT's are a little harder but lets you take the control and said to be faster.

From VFP side it's not complex. However you need to be familiar with SQL server and Oracle IMHO. VFP's own F1 help give a lot of information about it. Here are quick starters:

-Connect with a connection string

lnHandle = SQLStringConnect( "Driver=SQL server;Server=(local);Trusted_connection=yes;Database=pubs")

-Use parametric queires rather than hardcoding

* Do not prefer this

lcLastName = 'Karson'
SQLExec( m.lnHandle, "select * from authors where LName = '"+m.lcLastName+"'", "myCursor")

* But this

lcLastName = 'Karson'
SQLExec( m.lnHandle, "select * from authors where LName = m.lcLastName", "myCursor")

Parametric approach especially solves problems with passing correct parameters with other types such as date/datetimes.

-Try to use [ ] around your table and field names (designer doesn't do this for you unfortunately). It makes backend understand that you're not referring to a keyword but a field/table when you used a table/fieldname that's same as a keyword. ie:

select [desc] from [values]

However generally it's a bad idea to use keywords as field/tablenames and thus you might not need this at all (check a recent post related to this).

I think following code shows how to use SQL server via SPT in a nutshell for a starter:

Clear All
csql = "SELECT EmployeeID, LastName, FirstName," + ;
"Title, TitleOfCourtesy, BirthDate," + ;
"Hiredate, Address, City, Region," + ;
"PostalCode, Country, HomePhone," + ;
"Extension, Photo, Notes," + ;
"ReportsTo, PhotoPath FROM dbo.Employees"

lnHandle=Sqlstringconnect('DRIVER=SQL Server;'+;
'SERVER=(local);DATABASE=Northwind;Trusted_Connection=Yes')

SQLExec(lnHandle,csql,'v_emp') && v_emp has data now but not updatable
* You can browse and change data but it wouldn't update
* at backend unless you prepare it to be updatable

* Make v_emp updatable
CursorSetProp('KeyFieldList','Employeeid','v_emp')
CursorSetProp('WhereType',1,'v_emp')
CursorSetProp('Tables','employees','v_emp')

* Specify fields' mapping
* Mapping is in pairs
* On left there is local field name and on right
* tablename.Fieldname on backend.
* employeeID employees.employeeid
* employeeID is local field name mapping to
* employees.employeeid on backend
* In other words if set to be updatable
* v_emp.employeeID would update employees.employeeID at backend
* Note: Include key field in this list even though you might not
* want it to be updatable
TEXT to m.lcUpdateNameList noshow
employeeID employees.employeeid,
Lastname employees.Lastname,
Firstname employees.FirstName,
Title employees.Title,
TitleOfCourtesy employees.TitleOfCourtesy,
BirthDate employees.BirthDate,
Hiredate employees.Hiredate,
Address employees.Address,
City employees.City,
Region employees.Region,
PostalCode employees.PostalCode,
Extension employees.Extension,
Notes employees.Notes,
ReportsTo employees.ReportsTo,
PhotoPath employees.PhotoPath
ENDTEXT
CursorSetProp("UpdateNameList", ;
Chrtran(m.lcUpdateNameList,Chr(13)+Chr(10),''),'V_emp')

* Specify fields that could be updated
CursorSetProp('UpdatableFieldList',;
"LastName, FirstName," + ;
"Title, TitleOfCourtesy, BirthDate," + ;
"Hiredate, Address, City, Region," + ;
"PostalCode, Country, HomePhone," + ;
"Extension, Notes," + ;
"ReportsTo, PhotoPath",'v_emp')
CursorSetProp('SendUpdates',.T.,'v_emp')
* We want all updates to occur in a batch
* so setting to table level buffering
CursorSetProp('Buffering',5,'v_emp')

Browse Title 'Editable fields are all except Id and Photo'
* Update backend with changes
If !Tableupdate(2,.T.,'v_emp')
Aerror(arrWhy)
Display Memo Like arrWhy
EndIf
* Requery data from backend to see if it has been really updated
SQLExec(lnHandle,'select * from dbo.employees','afterupdate')
SQLDisconnect(lnHandle)
Select afterupdate
Browse

For a starter to SQL server, easiest is Remote Views IMHO. Create a connection in a database (you can use connectionstring as in SPT sample). Using that connection create remote views in designer (do not forget to mark 'Send Updates' if you'd want updates to persist). Those views are just like any other local table/view from your POV (with default buffering of 3 - you can change buffering to say 5 and views are always buffered).

These should be enough to wet your appetite. You asked for articles and such but I don't know what to suggest and where:) I'd just start in a test folder and ask specific questions when something doesn't seem to work the way it should. Aerror() would be your best friend in that case. IMHO it's an illusion that you could write the same codebase for both backends. Both have extensions of their own to ANSI SQL and you'd want to leverage them. However you could keep these in classes and call one or the other depending on the provider.

Good luck and don't be afraid, at first it sounds to be complex and you have many hurdles but it is really a fun once you start. Do not forget to download SSE (SQL server 2005 express) with its management studio (alternatively you might get SQL server developer edition for 49$). They have tools that would let you write and test needed SQL query blocks.


 
 
Markish





PostPosted: Visual FoxPro General, Guidance Required for VFP - MSSQL upgradation... Top

Thanks Cetin,

I started with the RV's....In the multiuser env. I land up in some troubles....resulting to update conflict....Like

- One user editing a record

- another user deleting the same record at the same time....

How to go about this.....like using rlock() in fox....Can u guide me in this...

-Markish



 
 
CetinBasoz





PostPosted: Visual FoxPro General, Guidance Required for VFP - MSSQL upgradation... Top

Markish,

Even within VFP rlock() approach is not very good IMHO. Your application's needs would really shape it. Once I posted a sample code on universalthread demonstrating "rlock" for VFP/MSSQL table in asp.net. You might search it there. The idea was using an intemediate helper table that keeps 'locks' with uniqueids per user and table/record that are 'locked'. Nothing was really locked and a user could keep a lock for a predefined maximum period (unless reacquire the lock before max timeout reached - defense against disconnections, get the lock and go to home etc style scenarios). However it was for demonstration purposes only. In general optimistic buffering and first write wins approach works for most applications. For example in your case, what if it was edited and committed first then immeditealy deleted by another user. Would it matter it was deleted while being edited or just later (It might depending on application). You can catch it in tableupdate() and check aerror(). VFP help has a section detailing such and similar conflict scenarios and I think there is an ConflictChecker class in solution.app. VFP has support for offline views, read it too. In an offline scenario it'd be more obvious, one user might edit the data while it's offline and another user might delete the data online. Offline user wouldn't know if it's deleted or not till she/he reconnects and has no way to "lock" the record for editing (ie: think you took the data offline on to your notebook and went to a village where no internet or alike connection is available). OTOH this might be a bank like "transaction" and you might really need it. SQL server (I don't know Oracle at all) has a series of isolation levels and transaction support. If you don't need such a thing, they're complex for a starter IMHO. First think about your application design. If you really need it intemediate 'lock' tables might be the solution (batches of SQL is automatically transactional and you could still wrap in explicit transactions - transactions are overused when someone doesn't really need it and I just don't want to direct you in a wrong way from the start. If you want it at least for testing execute a "begin transaction" command on your connection, however initially do it just for testing, and use only when you are comfortable with them, otherwise you'd also need to understand 'deadlock' situations and management well).

PS: Off the record, despite some myths you might be pleased later, if you use GUID primary keys for your tables. They have pros and cons where pros outweigh IMHO.


 
 
Markish





PostPosted: Visual FoxPro General, Guidance Required for VFP - MSSQL upgradation... Top

Thanks a lot... I'll take it forward... and let you know when I'm struck

 
 
Craig Berntson





PostPosted: Visual FoxPro General, Guidance Required for VFP - MSSQL upgradation... Top

Andy Kramek has developed some excellent data access classes. You can get them at http://www.tightlinecomputers.com/Downloads.htm