Go Premium for a chance to win a PS4. Enter to Win


The Simple Way to SQL via Foxpro

Posted on 2011-03-23
Medium Priority
Last Modified: 2012-05-11

The vast majority of my clients are happy to use DBF files as they are easy to access from other programs and license free.

However, I am now getting a number of clients who would prefer I use our VFP programs with a SQL back end for reliability.  As to licensing, well many of these IT guys just figure it is the company's money anyway.

I have to keep both groups happy.  And I am not sure how to approach this.  Any suggestions would be appreciated.

Question by:fmoore0001
  • 6
  • 3
  • 3
  • +2
LVL 16

Assisted Solution

by:Kalpesh Chhatrala
Kalpesh Chhatrala earned 400 total points
ID: 35196818
SQL Server Express 2008 R2  is Free. In R2 Database Size Limit 10 GB.

check below link for VFP with SQL Server Sample


Author Comment

ID: 35196871
kalpesh2804, thanks for the link, but this is more SQL Server access of Foxpro tables.  What I am looking for is a way to have my current Foxpro program, with its many thousands of lines of code, access an SQL database with a minimum of code changes, and retain my ability to access the DBF files.

Yeah, not much I want, right?!

I found an interesting site at www.easysql4fox.vallmind.com.  Maybe someone has had experience with their FLL.

LVL 43

Accepted Solution

pcelba earned 800 total points
ID: 35197558
This is not easy task and to say what exactly you have to do is almost impossible at this place... The data reliability will be better for sure, OTOH, you cannot expect the app performance will be better on SQL Server. The FoxPro performance is very good when peocessing local data and it beats SQL Server in many directions...

But your clients decided already, so...

You should look at Upsizing Wizard in VFP:


Also following books could help:


Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 12

Expert Comment

ID: 35197693
"but this is more SQL Server access of Foxpro tables."

Actually it is the other way around - VFP application access to the SQL Server data tables....

Using SQL Server data tables (or any other 'alien' data backend) is not too difficult to accomplish.

Your goal of having your single application be able to 'transparently' work with VFP data tables and also with SQL Server data tables, while admirable and definitely leaning towards minimum maintenance effort, will require a good amount of architectural changes to your code.

The ease of using VFP data tables generally lends itself to opening tables where needed in the code and then closing them, etc.   The ease of writing code in that manner can work against you in developing a multi-data source application.

To make something readily changeable you would be better served by having all of your tables opened in a FUNCTION - that would generally require architectural changes to your code.  

The same approach would be recommended for UPDATING tables or DELETING records, etc.

By doing things that way, you could use FUNCTION1 to open/update/delete records/etc. in VFP tables and FUNCTION2 to open/update/delete records/etc. in SQL Server tables using the different code.

And, while using VFP Remote Views is a relatively easy approach to utilizing 'alien' data tables, I'd personally recommend against it.   I have personally started that way only to find that the reliability was less than ideal.  Instead I have opted to use only SQL Pass-Through (SPT) to reliably work with the tables.   NOTE - it would not surprise me in the least to find that others might have differing opinions on that topic.

Another NOTE - making 2 distinctly different versions of your application (one for VFP data tables and one for SQL Server data tables) would be easier to accomplish.   The new code changes required to utilize SQL Server could just replace existing in-line code without a major architectural code change.

One thing you might want to look at would be to Google for:    vfp upscale "SQL Server"   where you will get a number of 'finds' about what is typically involved.

Another good reference would be to Google for:    
     client server applications with visual foxpro and sql server

Good Luck

Author Comment

ID: 35203876
My biggest problem is that there are more than 200 tables in this database, and all of the database control was form based, ie., there is Forms open and close all the table, a delight when building screens but now I see as a headache for the SQL connections.
LVL 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 800 total points
ID: 35204884
>and all of the database control was form based...

Well, that's were having a multi tier architecture in the first place helps, eg at least using views or nowadays cursoradaters (CAs) instead of direct table usage. There is no such thing as USE table in an SQL Server, not MSSQL, not MySQL, not other server RDBMS. You have to go through that change and now remotely query data.

Putting dbf tables in a DE of a foxpro form is skipping a middle tier in a three tier design. That's what's missing now.

Introducing remote views or CAs to the sql tables in the forms DE unfortunately only is half of a solution, as you could access all records in the past, for backwards compatibility you'd need to query all data of each table in the DE, which will cause a very slow initialisation of form of course. You need to filter / parameterize the remote data access to only pull the data you want to process/show in your form's session.

It's over with simply using a table.

Bye, Olaf.

Author Comment

ID: 35212844
Okay , Guys, let me go over what I perceive as the plan:

1)  We cannot use the DE in forms to open tables.   We will have to move the table openings to a PRG file;
2) If we use SQL, we need to create tables with the same names and the same (more or less) fields;
3) We have to set up some internal switch for the program to know if it is using DBF or SQL files;
4) Calls the the DBF or SQL database will have changed to know which type is in use,
5) I assume we do the same for report files?

This is simplified, but am I covering the basics?
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35213091
1. not totally wrong, but you can of course not put sql server tables into the DE, you can put remote views or cursoradapters conncting to sql server in the DE though, it's not a must to put data access soemwhere else, but it's in fact not a bad idea. Better put that in database access classes, not a PRG.

2. you can name tables and fields in sql server as you like, it's easier if you restrict yourself to names also valid for vfp, true, but you can use AS in both the FROM clause and the field list of selects to get the field names you want in vfp, while names can differ in sql server. The benefit of that is questionable, but could be driven by naming conventions you or a customer wants in regard to sql server data.

3. yes, if you want to support both databases. That's also a good reason to keep the names in both databses.

4.  Yes, Cursoradapter is a good basis for switching between backends. If names are identical you can switch by setting datasourcetype different mostly.

5. If your reports have a DE like forms and actively query data, yes. I prefer a prerun puting together the data for reports, but of course any data acess anywhere will need to be reimplemented in two ways.

Yes, this is basically covering the problem of a backend change.

Bye, Olaf.
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35213106
Finally, I reimplemented a large enterprise application from DBC backends to sql server backends and used a mixture of cursoradapter and sql pass through. cursoradapter mainly for 1:1 table access and sql passthrough to cover more complex sqls eg with recursion in case of readonly access or in case for mass updates, which are of course much faster done by update sql than by querying data to a CA cursor, updating that cursor and then writing back those changes.

Bye, Olaf.

Author Comment

ID: 35220659
Okay, another question.  WHAT SQL system?   I take it there are no free ones (I have heard that is one reason that Foxpro is DOA), and our company, for our clients, will probably vendor the SQL software to boot.

This is because it appears we are going to have to deliver both the SQL database and our program.  I am assuming we cannot, like our lovely DBF's, create the SQL program from Visual Foxpro.
LVL 12

Expert Comment

ID: 35222201
"I am now getting a number of clients who would prefer I use our VFP programs with a SQL back end for reliability"
"WHAT SQL system?"

Isn't that up to those customers of yours that you want to support?

Obviously the market share is dominated by M$ SQL Server - either the free version (Microsoft SQL Server 2005: Express or SQL Server 2008 R2 Express) for fewer needs (get the specs from M$) or the 'regular' version for larger needs.

Getting things to work in Micro$oft's SQL Server is the same no matter which version you use.  

However it would be up to you (and your application) to put a 'gauge' on it to somehow alert the customer to when their needs had grown to challenging the 'free' version limitations and needed to upgrade to the 'regular' version.

MySQL is also a free SQL Server and it has been widely used, but many customers have either never heard of it or they could be leary of it since it is not a market leading Micro$oft product.

Oracle, Informix, and others are also out there and possible candidates if desired.

Good Luck

Author Comment

ID: 35236070
The vast majority of my customers who want SQL have less than 100 million records and 3 or 4 users.  I do not mind becoming a vendor for one of the products, just want to keep the costs down for the small guys.  If Microsoft 2008 R2 Express will meet these specs I will use for the small and charge the big.
LVL 12

Expert Comment

ID: 35236814
"The vast majority of my customers who want SQL have less than 100 million records"

100M records in how many data tables?

If those records are, through proper data normalization, spread across a number of data tables, then perhaps the Express versions will likely work.

Additionally there is the size of the combined data tables to take into account.  
100M records of 1 small field per record does not take up much disk space, but 100M records of 200+ LARGE fields can take up much more disk space.

Again - look at the specs that Micro$oft publishes on the Express versions and decide if it would be appropriate for your needs.
Google for things like:  
         SQL Server 2005 Express Edition Overview  
         SQL Server 2008 Express Limitations
and read up on these packages.

Good Luck

Author Closing Comment

ID: 35403094
Thanks guys.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Do online retailers and e-commerce sites even need to bother with the season? Is it lucrative to update and prepare your site for Black Friday and Cyber Monday shoppers? We'll find out now.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question