List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:October 31 2002 6:22pm
Subject:Re: how to import a database
View as plain text  
>From: tosca ballerini <ballerini@stripped>
>
>I want to import a
>database from filemaker to mysql: is that possible? and how?

It's a pain in the butt. FileMaker does not allow access to all the schema info you need
to do a fully automated CREATE TABLE, and its typing and relational mechanisms are
completely different. Forget about porting scripts and calculations.

If you are using FMP 6, I believe it comes with a decent ODBC plug-in. Earlier ones do
not, but there are third-party plugs. This is THE solution if you want to keep each
database working in sync, taking advantage of FMP's greatest asset: it's GUI.

For one-time, one-way transfer, you will have to manually CREATE TABLEs in MySQL to
correspond with the data columns in FMP that you wish to import. Then export those
columns in tab-delimited format.

If you have date and/or time fields in FMP, set your format to match MySQL's before
exporting. Otherwise, MySQL may get hopelessly confused. I ended up with everyone born on
January 1, 1901, for example. (Boy, that must make for one helluva birthday party... :-)

Be careful about the length of text fields, and make sure you have the appropriate field
types in MySQL. If you just automatically make every text field TEXT, your database will
be slow, slow, slow! Go to the trouble to use VARCHAR if your data will fit.

FMP has only one numeric type. As with text fields, you should go to the trouble to
analyze what is in your numeric fields and make them the proper MySQL numeric type.

You cannot import calculations, globals, field options, or split-key relations (perhaps in
MySQL 4, you can). And of course, NONE of the GUI stuff, like formats and layouts, will
port. You will have to implement those in either SQL or some foreign language.

>thanks in advace for the help

You've got your work cut out for you. I did a bit of this recently, and it is NO FUN! I'm
now considering paying to upgrade to FMP 6 just to get the officially supported ODBC.
Anyone have experience with this? Is it worth it?

I wrote a crude bit of FMP script that attempts to create a strawman CREATE TABLE
statement. It traverses the entire FMP table, and attempts to discover the maximum length
of text fields, for example. Email privately if interested.
-- 
---- SQL SQL SQL SQL SQL SQL SQL SQL  ----
: Jan Steinman -- nature Transography(TM): <http://www.Bytesmiths.com>
: Bytesmiths -- artists' services: <http://www.Bytesmiths.com/Services>
: Buy My Step Van! <http://www.Bytesmiths.com/van>
Thread
how to import a databasetosca ballerini31 Oct
  • RE: how to import a databaseFernando Grijalba31 Oct
Re: how to import a databaseJan Steinman31 Oct