The Simple Way to SQL via Foxpro


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.

Who is Participating?
pcelbaConnect With a Mentor Commented:
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:

Kalpesh ChhatralaConnect With a Mentor Software ConsultantCommented:
SQL Server Express 2008 R2  is Free. In R2 Database Size Limit 10 GB.

check below link for VFP with SQL Server Sample
fmoore0001Author Commented:
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  Maybe someone has had experience with their FLL.

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

"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
fmoore0001Author Commented:
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.
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
>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.
fmoore0001Author Commented:
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?
Olaf DoschkeSoftware DeveloperCommented:
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.
Olaf DoschkeSoftware DeveloperCommented:
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.
fmoore0001Author Commented:
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.
"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
fmoore0001Author Commented:
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.
"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
fmoore0001Author Commented:
Thanks guys.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.