The Simple Way to SQL via Foxpro

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.

Fank

"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

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.

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?

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.

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.

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

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  
                  or    
         SQL Server 2008 Express Limitations
and read up on these packages.

Good Luck

Thanks guys.