Solved

The Simple Way to SQL via Foxpro

Posted on 2011-03-23
14
727 Views
Last Modified: 2012-05-11
Guys,

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.

Frank
0
Comment
Question by:fmoore0001
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 16

Assisted Solution

by:Kalpesh Chhatrala
Kalpesh Chhatrala earned 100 total points
Comment Utility
SQL Server Express 2008 R2  is Free. In R2 Database Size Limit 10 GB.

check below link for VFP with SQL Server Sample

http://fox.wikis.com/wc.dll?Wiki~VisualFoxProDataFromSQLServer
0
 

Author Comment

by:fmoore0001
Comment Utility
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
0
 
LVL 41

Accepted Solution

by:
pcelba earned 200 total points
Comment Utility
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:

http://support.microsoft.com/kb/129847
http://fox.wikis.com/wc.dll?Wiki~UpsizingWizard
http://www.code-magazine.com/Article.aspx?quickid=0703052
http://www.redware.com/handbooks/vfpclientserverhandbook/visual_foxpro_database_maintenance.html

Also following books could help:

http://www.hentzenwerke.com/catalog/mysqlvfp.htm
http://www.hentzenwerke.com/catalog/csvfp.htm

0
 
LVL 12

Expert Comment

by:jrbbldr
Comment Utility
"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
0
 

Author Comment

by:fmoore0001
Comment Utility
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.
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 200 total points
Comment Utility
>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.
0
 

Author Comment

by:fmoore0001
Comment Utility
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?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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.
0
 

Author Comment

by:fmoore0001
Comment Utility
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.
0
 
LVL 12

Expert Comment

by:jrbbldr
Comment Utility
"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
0
 

Author Comment

by:fmoore0001
Comment Utility
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.
0
 
LVL 12

Expert Comment

by:jrbbldr
Comment Utility
"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
0
 

Author Closing Comment

by:fmoore0001
Comment Utility
Thanks guys.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now