We help IT Professionals succeed at work.

DBF to SQL

fmoore0001
fmoore0001 asked
on
Guys, with help here I wrote a SQL to DBF convertor sometime back.  Now I have need of writing in the other direction.

One of our clients wants to run on either MySQL or Sybase (the latter has a neat DBF supported system that can use DBF files up to 6 gig).  We have figured out how to do CursorAdaptors to connect to the SQL tables (DBF or SQL with a boolean IF statement), but do not want to do a table by table ground up creation of the SQL database.

What are your suggestions, or code, for making an SQL database out of a DBF database.

Frank
Comment
Watch Question

CERTIFIED EXPERT
Commented:
You may try Upsizing Wizard in VFP. It will create SQL database from VFP database in several steps.

To create SQL scripts from VFP data structures is also relatively easy.
Olaf DoschkeSoftware Developer
CERTIFIED EXPERT

Commented:
"but do not want to do a table by table ground up creation of the SQL database"

I don't understand why ou make that a condition. Any code creating a database will do so table by table.
Are you thinking of a generic solution? Then the upsizing wizard is a solution. It might, it might not work out. Have a go, because you don't loose much time trying and if it fails you can still start writing your own migration.

As you already have cursoradapters, all you need is configure them updatabel, append dbf data to the cursors - table by table - and tableupdate().  

Bye, Olaf.
Commented:
You may already know this, but a database design based on DBFs is quite likely to be unsuitable for a SQL DBMS. A whole host of fundamental differences apply, not least the fact that DBFs supported ordered access to data and row by row operations without keys - SQL generally does not or at least makes those things extremely difficult and inefficient. Upsizing a DBF should therefore involve a complete review and potential redesign. Don't rely on wizards or automated operations to do that for you.
Olaf DoschkeSoftware Developer
CERTIFIED EXPERT
Commented:
dportas,

totally agreed. The wizard pavel and I talked about is not converting application, only uploading the data. And what you say about dbfs is true, in a modern vfp application you also have primary and foreign keys, indexes, relations, triggers, stored procedures, referential integrity, cascading delete etc. and more, foxpro is a grown up database, and good database desing is good in sql server, too. You are right that dbfs and the way to adress them often is done totally different and in the row by row operations. But reading in data from sql server into a recordset in VB or a datareader in .net and many other bindings to data works on row by row processing of the result or random access to some row number.

You need a major rewrite, if you did go a a too simple route with dbf, not having a n-tier architecture. A major rewrite of the application itself is a normal thing. Knowing fmoore0001 a bit from his question I would predict for him, that this will be an issue. Nevertheless if migration of data is decided, a way is the upsizing wizard. It's a pure data upsizing wizard. Restructuring can then be done on the sql server side, too.

Bye, Olaf.

Author

Commented:
Thanks, Guys.  Kinda what I expected.   Maybe we have to wait for PolorFox.
CERTIFIED EXPERT

Commented:
Be careful. Global warming will kill all polar foxes. :-)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.