Converting Foxpro to use SQL

[PaulGillespie]

Hello all, i'm not normally in these forums as i'm an network admin but i've been asked to get some basic info for a collegue.

We have a foxpro 8 database that works very well but we are seeing performance and corruption issues due to the sheer size of the database. We are thinking of rewriting it to use a SQL backend but use foxpro for the front end.

Is there a simple conversion to do this or is it a complete rewrite of the database?

Thanks!

Paul

 

[JRB-Bldr]

You don't "rewrite of the database".
If needed, you typically rewrite an application which utilizes data tables which might reside in a database.

There are a few possible approaches depending on what other changes needed to be introduced at the same time.

Also it depends on whether or not the original application was using databases to access the individual data tables or not.

If databases are used, and if the data table structures were exactly the same then new replacement Remote Views could be created into the new SQL Server database data tables utilizing an ODBC connection. If this were applicable, then the change might not be very difficult.

However while working on a similar project I have encountered a number situations where SQL Server data table field types are not the same as those used in the original VFP data tables (for example SQL Server does not have a DATE field type - only a DATETIME field type, etc.). While this does not always introduce problems, it presents the possibility of a problem/challenge. Sometimes differences like this can be addressed in the Remote View Query - other times not.

There are a number of previous postings on similar topics. Unfortunately the Tek-Tips Forum Search engine hasn't worked for a while so you can't just Search the VFP forums on "Upscaling", but doing a Google Search on VFP "SQL SERVER" turns up a number of associated references.

Good Luck,
JRB-Bldr

 

[PaulGillespie]

Thanks for the quick reply! I had envisaged using an ODBC connector but was unsure in this context.

If anyone else has any opinions feel free to jump in.

Cheers

Paul.

 

[Mike Lewis]

Paul,

We are thinking of rewriting it to use a SQL backend but use foxpro for the front end.

Good idea. I would normally say that, if you are seeing data corruption, your first line of attack should be to identify the source of the corruption, and then eliminate it. But if the size of the database is also an issue, upsizing to a client-server back end will be essential (in VFP, each file is limited to 2 GB).

You don't mention which back end you are considering. Products like SQL Server and Oracle have much higher capacities than VFP, but some other back ends don't.

Is there a simple conversion to do this or is it a complete rewrite of the database?

It is definitely not simple. There are tools available for physically converting the data, but that's only one small step. You'll need to do a lot of work on your code. How much work depends on the way your existing data handling is structured. You'll probably also want to do some re-design to take full advantage of the client-server architecture.

To give you some idea of what's involved: I have been involved with three separate projects that involved migrating data from VFP to SQL Server. All three took several months, and unexpected problems arose in all three cases. On the other hand, they were all successful -- in the sense that the clients considered the effort was justified by the benefits.

Hope this helps.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[PaulGillespie]

Thanks very much for your input Mike. The corruptions, i believe, are due to a large transaction taking place which takes several minutes to complete, mean while, other users on the network are also running transactions and at some point there is a corruption. As i said i'm not a programmer so this is all just second hand knowledge. The change is required for performance and reliability, as out DBF tables will continue to grow and we can see no other solution.

We are thinking of using MS SQL server as it is widely supported and we heavily use other MS server technologies.

Cheers

Paul (also Edinburgh!)

 

[Mike Lewis]

Paul,

We are thinking of using MS SQL server as it is widely supported and we heavily use other MS server technologies.

That makes good sense. Whatever the pros and cons, the fact that both SQL Server and FoxPro are Microsoft products does have definite advantages.

Paul (also Edinburgh!)

Glad to hear from a local VFP shop.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndyKr]

>>If anyone else has any opinions feel free to jump in.

Can't add much to what has already been said, but there some alternatives that you might want to consider. Remote Views are all very well, but they are limited (for a start they can only use ODBC). VFP 8.0 introduced CursorAdapters which give much more flexibility including the option to use the OLEDB instead of ODBC.

Even more flexible, and powerful, is the SQL Passthrough approach but implementing that over an wxisting FoxPro application is likely to be the hardest of all options unless the original app was designed and built using a Client/Server architecture (i.e. two or three tier).



----
Andy Kramek
Visual FoxPro MVP

 

[PaulGillespie]

Thanks Andy, i'll add that to my list to investigate.

Thanks

Paul

 

[stirfry]

Paul

You might want to read
'MySQL Client-Server Applications with Visual FoxPro' which has just been published from

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

Andrew
(30 miles north of Edinburgh)

 

[PaulGillespie]

Thanks Andrew, right little local thing we have going on here!

 

[SGLong]

Another consideration is the CDX file - compound index tags. There is no SQL counterpart. I'm in the midst of migrating all of our VFP tables into MS SQL with remote views. If you are used to issuing a command like

USE countries IN 0 ORDER isocode

that will no longer work. When referencing a remote view you will now have to do something like

SELECT 0 
USE countries
INDEX ON isocode TAG isocode

To ease things I've created a table-driven routine - OpenView - that with a single line call can accomplish the same effect of the first command.

OpenView('countries','isocode')

Steve

 

[PaulGillespie]

Thanks Steve!

 

[opticalman]

I use remote views in my application. I use a LOT of them. They can be created and edited visually. The views can contain data from multiple tables. Plan your tables well, if you change the SQL table, you will need to edit every VIEW that calls it.

Jim Rumbaugh