Migrating to mySQL from vfp database, whilst keeping vfp front end

[insania]

Hi,

I was just wondering if anyone had ever successfully migrated their VFP database to mySQL whilst keeping their VFP front end. If you have is there any advice you can give me regarding this?

Any help would be great

Thanks.

 

[Mike Lewis]

I was just wondering if anyone had ever successfully migrated their VFP database to mySQL whilst keeping their VFP front end.

Yes.

If you have is there any advice you can give me regarding this?

What specifically would you like help with?

(Sorry, I'm not trying to avoid giving you an answer. It's just that it's such a wide question, and there are so many issues to consider ... it would really help if you could be a bit more specific in your question.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[insania]

Well mostly its just general things like which connector is best, ODBC, ADO etc.

Is it difficult to use the connectors within VFP?

How can we actually get the old data from the VFP tables into the new mySQL tables?

Are there any practices you would avoid?

Is it very successful even if it does connect? Does it crash/disconnect/not recognise users...

We are also eventually going to change the front end to completely get away from using VFP, do you have any advice on which programming language to use? e.g. .net, delphi, web based languages...

Any help/advice you can offer would be greatly appreciated.

 

[Mike Lewis]

OK, I'll try to answer those points. I hope other forum members will chip in as well.

... which connector is best, ODBC, ADO etc.

Broadly speaking, there are three ways to connect to the back-end: remote views (RVs), SQL pass-through (SPT), and (in VFP 8 and above only) CursorAdapters. You can find more about all of these by reading the help topics on remote data access.

With RVs and SPT, you're going to have to use ODBC. This shouldn't cause any difficulties. ODBC also works fine with CursorAdapters, but you can do use OLE DB instead.


Is it difficult to use the connectors within VFP?

No. RVs are especially easy -- use them pretty much like a normal table -- although they have some limitations.

How can we actually get the old data from the VFP tables into the new mySQL tables?

In my experience, the only satisfactory way is to write a simple program (in VFP) to do the job. Write a series of CREATE TABLE commands (you could also write a program that generates the CREATE TABLEs automatically). Then write some code to loop through the records in each table, INSERTing them into the tables on the back end.

Another option would be to copy the VFP data to text files, then use MySQL's import features to import these, but then you'll have to manually create all your indexes, constraints, etc. Also, you might lose data, such as memo fields, that doesn't convert well to text files.


Are there any practices you would avoid?

Can't think of anything off-hand.

Is it very successful even if it does connect? Does it crash/disconnect/not recognise users...

The only problems I've experienced is when there are flaky network connections, or the server is badly configured. In general, the VFP side should work OK, provided you go about things the right way.

We are also eventually going to change the front end to completely get away from using VFP, do you have any advice on which programming language to use? e.g. .net, delphi, web based languages...

That depends on what your objectives are. Put another way, why do want to move away from VFP? It can do anything that those other languages can do, and it is a good deal more capable and has better performance than almost anything else you'll find.

Hope this helps. Come back of you want to follow up anything.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[TonyScarpelli]

Hey, Mike, thanks for the answers.

I'm on the same path, getting my FP tables into MySql and being forced to go away from VFP (company won't support it any more). I've been doing a lot of programming in VB .NET, and have to use both SQL Server and MySql.

There's an excellent tutorial at

http://www.vbmysql.com/articles/vb_mysql_tutorials/vb_mysql_tutorial-part1.html

that can be a start to learn how to use VB .NET and MySql.

My next problem was getting my FP tables into MySql. One can get FP into Access and MySql will directly import Access tables, but I haven't tried it yet. There is also a program that I found in these forums that can put FP tables into a text file, including memo fields (I used it for getting data into Excel), and that can then be pulled into MySql, but as you said you have to recreate the indexes.

As soon as I get a program ready to pull FP tables into MySql I'll post it.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[craigber]

If you're using MySQL, then ODBC is the way to go. You can connect via SPT or Remote Views or even CursorAdapters.

For the future, I would avoid Delphi. Borland has announced they will be selling all their languages. No buyer has been announced, but future support should be a question you ask.

Web is a possibility if it meets your needs. Even with AJAX, rich GUIs just can't be done.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[rkolva]

I've been watching this thread because we're also looking at re-developing a current FAT client system into a multi-tier, multi component (DLL, COM, etc) app with a middle (business logic) tier connecting to a database other than VFP. One of the options we were considering was also MySQL.

If MySQL isn't a good choice what would you suggest we consider?

Ralph Kolva

 

[Mike Lewis]

Ralph,

I've found MySQL perfectly satisfactory, but I think Microsoft's SQL Server is far better. It is more robust, has a far richer feature set, and has better performance. Then again, it's more expensive and has a longer learning curve.

Either product will give good service, provided you go about your n-tier design in the right way.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[craigber]

Mike,

SQL Server may not be more expensive. SQL Server Express may meet the needs and is free.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[insania]

Has anyone ever considered/used PostgreSQL? I was just wondering as this is a free database and its also going for the same functionality as Oracle.