VFP migration path, MS SQL anyone ? 4

[Steve Yu]

Hello,

We have a 30-year old character based package coded in xBase and ported through various VFP versions to 9.0 currently, with little coding changes;
no forms, no popup menus, just plain old @ say get.
It has been heavily modified with myriad of features and enhancements and it performs very well.
Then Microsoft dropped support of VFP (I know it's years ago, but time flies).
Who cares you might ask; I just saw Window XP running the X-ray machine in my doctor's office the other day.
The package is too valuable and we are too dependent on it to risk it being discontinued and suddenly stops working in one day.
So we have started searching.
We have evaluated many 'modern' packages, including Netsuite and a close cousin AccountMate that's been ported from character mode to VFP forms, for our possible migration replacement. But none of them are suitable.
Two factors we are considering: backend data base and front end user interface whereas our current system is tightly coupled two in one.
We have concluded the evaluation and have begun the migration process by planning to deploy MS SQL as backend AND keeping the character based interface by replacing VFP seek/locate/replace/etc commands with VFP SQL path through commands.
It's going to be tons of rewrites and testing but we simply have had too much customization to start over with a canned package with modern interface and all the bell and whistles that we might not want or need.
I'd like to ask for comments and suggestions.
Thank ahead.

Steve Yu

 

[Steve Yu]

Vernpace,

Isolation levels, including Snapshot version, are designed to resolve conflicts that only occur in the client-server model.
By that I mean I believe this is how it works in general: the client(s) each gets a copy, works on it, and sends modified local copy back to server and let server handle and resolve the 'who gets to update first' issue; and depending on which isolation level is set, some client's request might be rejected and the application would need to be coded accordingly to handle these events.
In our good old xBase model, either rlock() flock() or a check-me-first field is used to ensure the record is locked before the replace command.
Very simple and efficient; we have staff members from multiple departments generating batches of orders, 50 at a time, and never had problem with duplicate order numbers (in sequential order) or the cumulative item order count not being properly updated.
Our current plan is convert backend to MS SQL and keep all the .prg logic as is, including the locking logic that has worked well; and we have built a small library of SPT commands that performs basic operations that would replace and/or support the current SEEK,APPEND BLANK,REPLACE,etc.
We have already installed a standard MS SQL server on a newly configured server under VMWARE and test converted a complete set of our current DBF's.
We have also just started and successfully converted several existing xBase .prgs, with the help of an outside consultant who have worked with VFP and SPT over 20 years. No change to 80x25 UI, no forms, no RV, no tableupdate, just same old plain @ say get.
Of course we've encountered many issues: date fields (we decided to use c(8) under SQL and a custom date function for query), reserved words, auto increment field instead of recno(), and a way to keep the existing SCAN/ENDSCAN/SKIP constructs by using cursors, which is very difficult and requires lots of rewrites. Doable but tough.
Now I'd like to pose an open question: is this worthwhile ?
Is the MS SQL so robust and superior to DBF for us to consider the conversion, even though the DBF based system has served us well and is performing well ?
Or is it because we are afraid DBF file structure may one day suddenly stop working ?

Verpace, on your VFP story of Countrywide being taken over by BOA, what was the reason BOA finally decided to abandon and rebuild 8 years after taking over the project ?
It seems to me 2016, the year you said BOA finally gave up on VFP kind of coincide with the announced end of VFP support by Microsoft.


Steve Yu, very concerned in Houston

 

[tomk3]

Hello Steve,

locking
for primary keys you got an answer from me and Mike, vernpace gave a link to how sql server handles locks.

Reason why we went to MS SQL

1) Problems with data
We got problems on using DBF in networks on customer sites, index corruption, phantom records on append,...
We could not set environments to correct that, local IT did not allow that., if I remmeber correctly there was some changes to be done whhich need an older SMB version which they did not allow.

2) size
we had to add storing and encrypting documents (scans, pdf) to our applications. We were not allowed to store them as (encrypted) files and just store filename, path,... as a link to a folder hierarchy (do not aks why), they had to become part of a database. That can easily break the 2GB limit of vfp (some database exceed 100 GB now), and yes, I know VFPA does not have this limit anymore.

3) Tools
Some clients with own IT want to have read access to (a copy) of data using external reporting tools. Most of the tools support MS SQL, few DBF.


Regards
tom

I remember ADS (advantage database server) which supports VFP locking and being a "real" database server. We used it since clipper days (easy because of clipper's rdd technology).We went away from it because customers wanted MS SQL. I do not know whether it is still available.

https://stackoverflow.com/questions...f-files-with-advantage-local-sever-and-foxpro

 

[Steve Yu]

Tomk3,

Thanks for the reply.
1) problem with data: yes, we do have similar issues from time to time, one of the reasons we want SQL Server
2) size: we also store only file names for .jpg images of our products in DBF so we can 1. get around the 2GB limit 2. avoid performance degradation; we store all our daily scans (invoices,packing lists,etc) in LaserFiche (a SQL based document management system) and build a 'hook' into that system to allow staff members to pull images easily from the xBase menu.
3) external reporting tools: no issue for us, this being a one and only in-house system.

Regards,

Steve Yu