which desktop/embedded RDBMS w/ VFP? 2

[davisonpro]

I am inheriting a VFP7 application and I'd like to begin migrating it away from the native FP dbf/ndx/cdx. to a SQL solution.

It is a desktop application that cannot/will not be migrated to a web-based app - it is frequently used in the field where connectivity is sketchy at best.

I have found some discussions about this topic but they are several years old.

I'm looking for something that will have unlimited/royalty-free distribution for the runtime.

What are others using? What is working and what should be avoided? Is there a more appropriate place for me to be posting this question?

Thanks!
Al

 

[Mike Lewis]

It would help to know something about your size requirements: the size of the database, the number of users, and so on.

If your requirements are fairly modest, then Microsoft's SQL Server Express might be a good choice. It's royalty-free (in fact, it's completely free), and it works well with VFP. But there are fairly low limits on the overall database size (4 GB, if memory serves) and on the number of simultaneous queries.

If your volumes are higher, it might be worth looking at MySQL, which is free, but open-source. You'd have to be sure that an open-source licence would be suitable for a commercial product like yours.

One other possibility that comes to mind is PostgresSQL. I don't know much about it, but I know of VFP developers who are happily using it.

Probably, your best bet would be to download some evals of these products, and spend some time deciding which you feel most comfortable with.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[MrDataGuy]

Also it would help to understand why the migration from VFP to another DB backend is wanted? For example is it because you want 3rd party applications to have easier data access (if so what DB do these 3 party apps want?) better security, larger table size, the first steps to a complete rewrite in another language (and which and why the rewrite) etc.

Do you know if the application was written using good N-Tier architecture? For example are there only one class that does the actual interface to the database, or is there lots of places in the code where there are statements that say things like “Use <Table>” and ‘Select <Fields> from <Table>….”. How many tables are in the current application? And do you expect the new Database to have the same number of tables or will there be a significant change because of things like data normalization? Are you considering a hard switch over or a gradual change from one to the other, e.g. for some time 2 databases will be accessed by your application? How many copies of the application are there: 1, many?


Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[davisonpro]

Thanks for the fast and informative reply!

I've been looking at all 3 of the products you mentioned but I wanted to see if perhaps I was missing one of the better ones.

My individual database sizes never exceed 1 gig and, while I'm like most folks and like speed a lot, it's not a huge consideration - stability and reliability are vastly more important.

 

[MrDataGuy]

stability and reliability are vastly more important"

Is there some area of the VFP database that in your experiance is not stable or reliable?

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[davisonpro]

MrDataGuy:

I have a very long history with all kinds of xBase systems and an equally long history various SQL RDBMS systems. I greatly prefer the latter. I was a PowerBuilder developer for about 10 years so, I'm more comfortable with the Sybase/SQLServer/Oracle environment.

I don't currently have an issue with either reliability or stability with our installed base but I do have major issues when we issue our quarterly updates and/or when our clients are moving their systems to new workstations or servers due to the ridiculous number of disparate files involved. I'd like to get away from having 75-100 files to move around as opposed to a single DBMS file.

The system that I'm inheriting does not even have SQL commands in the source code so, I'm going to have to "translate" all of those over the next year and I'd really prefer to do that translation against something that is closer to ANSI SQL and/or T-SQL.

I'm preparing for the EOL of VFP and I'd like to get all the back-end stuff done first.

Hope that info helps.

Thanks!
Al

 

[MrDataGuy]

OK, I now know a little more of where you are coming from. In my mind it is wanting to the application to work toward your strengths.

Back to the original question.
“I'm looking for something that will have unlimited/royalty-free distribution for the runtime.”

Mike Lewis suggestions are good, however I have found that the simplest solution may be just an Access Database (Note limited size and I would only use is for a single user or very low volume multiple users.)

Do consider writing a data access layer to the app. If do right you can switch from back end to back end and support multiple simultaneous backends. Years ago I worked on a commercial app that used VFP, MS-SQL and Oracle. All SQL commands were via a table that held the SQL commands (Knid of like a roll-your-own views) we found that ~95% of the SQL commands were the same for all of the backends. Truth be told we treated the back ends as big dumb backends, the logic and heavy lifting was done in the Biz Layer in VFP, no Views. Stored Procedures, Special data types etc. where used. This was we had lots of flexibility on backend choices.

I just went back and reread the posts. And while what I wrote in the last paragraph is true, I am guessing that it does not really apply in your case. I would consider Access because it is so easy to install, e.g. just drop the MBD file and go, no worries about installing Express, mySQL etc. Later if the data needs start to get larger then express or full MS-SQL can be used.


Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[davisonpro]

Thanks for your replies, MrDataguy!

In all my experience, the worst choice for me would be Access even if it meets all my requirements (on paper). In my last job, I actually banned Access from the enterprise because I found myself having to constantly recover and then replace Access databases. The COA was exorbitant!

With this model, I need to find anything within reason to shave as much support as possible since I'm pretty sure that this project is going to be mine and mine alone. Even eliminating 1 support call per day is worthwhile.

I'm not a hard-core "anything but MS" guy. I'm not a hard-core anything kinda guy. I'll probably just use SQL Server Express but, I have started this search with lots of lead-time. I'm looking at a 12-24 month cycle before I really have to make a choice and the investment.

I'd love to hear from others, too!

Al