Outgrowing Visual Foxpro

Visual Foxpro was a platform of choice for small and medium applications for decades. I started writing things in Foxpro when it did not belong to Microsoft yet. I believe when it was in version 2.0. In a time when there was no Windows and machines had less than 1 Mb of Ram and a 80 MB harddisk was big. Back then the concern was to keep program and data small. After all those 80 Mb on your Novell Server had to last for a few years. I had a customer back then using SBT and entering about 300 invoices/day with about 15 lines per invoice on average and back then we used about 15 MB a year to store that information…..

Fast Forward to 2010. Said customer still does those 300 invoices on average a day, SBT is long gone, but the software has grown, been updated, improved, Moved to VFP9.0. Many features added, much more information stored. Now those 300 invoices/day run up close to 2 Gb of hard disk space a year. Not a big deal with the size and price of todays Hard disks. But it introduced quite a few new problems.

  1. VFP tables are limited to 2 Gb space. So if one wants to store multiple years of data one has to sooner or later make some hard decisions. Like do we archive some of the data, Do we split the table horizontally or vertically etc.
  2. Performance: VFP is based on Flat files. So if the table(s) and index files are big it takes considerable amount of time to send them to the workstation over a network. The code resides usually on a server with the data. So the workstations access the code files from the server and then they access the flat data/index files. All resulting in a considerable amount of network traffic. The bigger the tables get the slower the system gets noticeably.
  3. Difficulties with access over the Net. Yes one can use VFPOLE and a web server or .Net. Or pcAnywhere or Logmein. But why duplicate a screen as a web page or tie up one or multiple computers with a remote access software. Because using VFP over a VPN over the net is out. I tried it just for “fun” once and printing an invoice over a 256kb DSL connection took 4 Minutes
  4. Microsoft is discontinuing VFP. Granted there is a chance of them changing their minds and there are some people around that are working on projects that will compile VFP code into .NET or other platforms. BUT…. are they going to complete it? Are they going to stay around? Are they going to address the 2GB table size restrictions? And then we are going to do a rewrite in .NET….

So especially in view of points above I made the decision to move on and find a new platform. So my first idea was to go to MS SQL. There are upsizing wizards for that included with VFP and after all I wrote quite a few things for MS SQL in the late 90’s namely the whole backend for a day trading system including real time stock analysis etc.

So then how did I get here to MYSQL?

MS SQL fell apart mostly because of what I call financial realities and how it worked out also – ease of use – at least in my case. Granted Microsoft gives the express edition away for free. But its pretty useless in the above scenario as a 4Gb Database limit is worse than a 2GB table size limit. And people like to store a lot of data…. Plus Microsoft has the tendency of forcing you to upgrade. I bought Office 2000, it worked fine for me. Now I got a Vista machine and a Windows 7 Laptop and office 2000 will not even install… So now I am using Open Office…

So I laid out a plan for the migration.

  1. Move the VFP data onto a MYSQL Server
  2. Split the VFP code into Client/Server portion and write the MYSQL procedures and Triggers to pretty much equal the ones in the VFP database
  3. Keep VFP as a front end for now and alter the form code to access the remote MYSQL database instead of the Foxpro database. This should allow to take care of the aforementioned problems and overcome the size and performance limitations that currently exist with the use of VFP plus allow good performance in accessing the data over the Internet/VPN
  4. In the future if necessary leave VFP and turn the whole application into one that runs on PHP/APACHE/MYSQL. This would allow great flexibility as to available platforms to run on and allow any device capable of “doing websites” to access the data.

Next Chapter: “First Contact” Setting up VFP and MYSQL to interact

Tags: , ,

9 Responses to “Outgrowing Visual Foxpro”

  1. Tom Brander says:

    Found myself laughing as I read your post going through the same thought process myself but due to the same issues with Access as a product. Explored MSQL and most of their suite, SSIS etc., what a mess… Went to MYSQL and Birt report writer with Eclipse,, then picked up Python (a work in progress) along with Django,, Have a lot more learning ahead but nor more problems with stupid Microsoft games,, Migrated my site off MS Live and my e-mail to Google Apps..(off of Live mail (I stupidly belived Microsoft when they offered “free Domain Names for life”) and am slowly trying to use mostly a combo of open office and Goggle apps… along with more powerful Python tools for industrial strength stuff.. all in all a much more pleasant environment to work in. Have not moved old code but not writing anything new with MS junk.

  2. admin says:

    Yes thats why. Way to often I ran into a scenario where everything was working great. Then a computer blows up and you get a new one with the newer OS because the old one is discontinued and then you get a message. “this feature does not work with your Windows 2000 Server” then you try to find a patch or solution and wind up telling the customer “either this machine can’t do this or you have to upgrade your server and every other computer in your place” And he says “are you nuts – that’s going to cost me ….. and all that for one bad computer.” Then you try to install Win2k on the new machine just to find you are missing some drivers ….. Who needs those headaches. The average Joe does not want to replace all his computer equipment every few years. So I am working on becoming platform independent. In worst case I want to replace one machine – the server if necessary. Should not matter what he is running on the WS or if he is using his Ipod.

  3. anand says:

    hello mr.martin
    i am anand kulkarni from india . i am one of the member of foxite.com . i saw ur thered regarding vfp and mysql . this is very good artical . this is very helpful for the begineer al so . i am also using vfp and mysql as back end by using spt method every thing is going fine . please make available about vpf & mysql by SPT connection .
    thanks for this .
    regards
    anand

  4. admin says:

    I discussed some of it in the recent Converting a simple form post

  5. francois says:

    I am so relieved in what I read here. I have a program (that my life depends upon) written in VFP. I am not a programmer and for that reason I sought alternatives to transfer the data to other data bases with no-code interfaces. However, to test stuff like this, takes enormous time and money and having to download, test, remove programs, just to simply get nowhere. I live in Port Elizabeth, South Africa and in all honesty; I invested four years ago in SBS 2003 and until today could not find no organization here that could assist me in making the server perform. But, the main problem always is that there are surprises, like licensing en having to invest in more and more utilities, just again to run into further surprises. And everything is costly. I cannot imagine how much I would have been out of pocket if I remained using people calling themselves “experts” not knowing in any event of the pitfalls (or not wishing to disclose it beforehand). I spend three days now to replace SBS2003 with XP Pro. At least I will be able to control my server again. But still no on-line collaboration, no VPN to enable me to work from wherever, and still wondering when VFP is going to conck in. I sincerely hope that I have stumbled upon your block with a reason and that is to find assistance in getting my program to work for me and enabling me to attend to what I should attend to, my business,and not sitting days and nights in front of a computer trying to figure out how to enable my staff to work productively.

    What you stated here is so very true. One last thing. I found that people knowing coding and business seemingly do not talk the same language. Instead of implementing what is needed in business, we have to follow what they prescribe. That is wrong.

    So, let’s see if your blog can help. You will appreciate my frustrations currently.

  6. hansem says:

    if I used foxpro database there’re no limit column when I save the records more than 50 column but if I used mysql server to save from foxpro there are limit column and then show warning “command contain unrecognized phrase/keyword” like code below :
    SQLEXEC(test,”LOCK TABLE LocalOnLine.vouchers WRITE”)
    SQLPREPARE(test,”UPDATE LocalOnLine.vouchers SET division=?v_dv,agent=?v_ag,guide=?v_gd,invoice=?v_iv,month=?v_mt,years=?v_yr,voucher=?v_vc,; bill=?v_vl,cgroup=?v_gr,client=?v_cl,client1=?v_c1,client2=?v_c2,remarks=?v_rk WHERE month=?v_mt and years=?v_yr and voucher=?v_vc and totalrp+totalus>0″)
    server=SQLEXEC(test)
    SQLEXEC(test,”UNLOCK TABLE”)

    IF server<0
    MESSAGEBOX('Update voucher '+v_vc+' '+v_vd+' '+ALLTRIM(v_nm)+' failed',16,'Warning')
    ENDIF

    what wrong with my code above and there are limit column to save from foxpro to mysql server ?

    please help me, what can I do to resolve that trouble.

  7. admin says:

    Difficult to say w/o seeing the rest

    try
    = AERROR(la_err)

    And then examinge the arra – it should give you a better answer on what the error is. Could be as simple as a mismatched variable.

    I do not use sqlprepare. I use straight passthrough. That Way I can better control the SQL.

  8. jmason says:

    Admin (large state agency) created new policy that all would use only MS Office for all tasks. No more FoxPro!
    I explained that all the systems I had written would not translate to ACCESS.
    No matter, they said: just rewrite everything.
    I said, “You dumb bastards rewrite it all, if you ever learn to write.” Then went to another agency offering 50% better pay to do less analysis.
    They sent some chump over 6 months later to get my help on a massive yearly report… I LAUGHED for a WHILE.

  9. admin says:

    if you use sqlexec or sqlprepare you are limited to 256 characters. To work around it put the statement into a string first likes

    sql = “Update ….”
    Sqlprepare(test, sql)

    etc

Leave a Reply