Converting from FM 9 to MySQL and keep both

Hi All,

Here it is the starting point: Clinical research DB in FM 9 adv and FM
server 9 adv both on Windows platform.
The database has 10 tables, ~ 600 variables and ~ 1000 records.
I would like to convert the entire database to MySQL for a couple of
reasons:

1) Learning more about SQL
2) Being able to construct more complex queries
3) bring the Database on a *nix/LAMP system

I have read a lot of treads, many people actually ask how to do that
but there are only few replies.
One of the most common is:

1) Build another DB in MySQL, corrispondent to the FM one
2) Export all the records from FM in .cvs
3) Import in MySQL (usually with the help of PHPmyAdmin)

in alternatively someones used FmPro Migrator.

I tried to look at the Migrator but it sounds quite complex (I have to
use also perl scripts) so I would prefer a more straightforward way.

Beside this, I would like to let the two databases talk so that MySQL
is updated every let us say 20 minutes from FM in a sort of automatic
way.
I think that this can be done with ODBC and I installed successfully
ODBC drivers for FM 9, but now I do not know exactly how to proceed.

Any help would be greatly appreciated !

Happy holidays : )

Diego

Re: Converting from FM 9 to MySQL and keep both

Diego B wrote:
> Hi All,
>
> Here it is the starting point: Clinical research DB in FM 9 adv and FM
> server 9 adv both on Windows platform.
> The database has 10 tables, ~ 600 variables and ~ 1000 records.
> I would like to convert the entire database to MySQL for a couple of
> reasons:
>
> 1) Learning more about SQL
> 2) Being able to construct more complex queries
> 3) bring the Database on a *nix/LAMP system
>
> I have read a lot of treads, many people actually ask how to do that
> but there are only few replies.
> One of the most common is:
>
> 1) Build another DB in MySQL, corrispondent to the FM one
> 2) Export all the records from FM in .cvs
> 3) Import in MySQL (usually with the help of PHPmyAdmin)
>
> in alternatively someones used FmPro Migrator.
>
> I tried to look at the Migrator but it sounds quite complex (I have to
> use also perl scripts) so I would prefer a more straightforward way.
>
> Beside this, I would like to let the two databases talk so that MySQL
> is updated every let us say 20 minutes from FM in a sort of automatic
> way.
> I think that this can be done with ODBC and I installed successfully
> ODBC drivers for FM 9, but now I do not know exactly how to proceed.
>
> Any help would be greatly appreciated !
>
> Happy holidays : )
>
> Diego

mantra: FM is not meant to be a front end for MySQL
trap1: if you want server to execute anything in the way of scripts,
make sure EVERY script step is web compatible. Read the server help.
trap2. be aware that data shared between FM and SQL is not 'live' , what
you see is not necessarily what you may have. Reload in MySQL is usually
needed to force updates.



You have 600 variables; how many fields?

An alternate to export via csv, is just to script across data to MySQL,
loop through the ESS shadow table records, pushing the data to a
related FM TOC. How pratical this is will depend on how many fields you
have.


Suggestion; build yourself a test db pair to explore the basics involved
in what you want to do
Suggestion; avoid doing anything remote; FM downloads for every sort
etc, and can be very slow to execute what you might expect to be immediate.

Re: Converting from FM 9 to MySQL and keep both

> mantra: FM is not meant to be a front end for MySQL

Yes, fully agree. In facts I would like to have two different (but
parallel) databases:
one in FM, the corrispondent in MySQL. The last one should receive a
live update
from FM...is that crazy ? (No Irony here, just a question)


> trap1: if you want server to execute anything in the way of scripts,
> make sure EVERY script step is web compatible. Read the server help.

Not sure here. Do you mean scripts for pushing data from FM to MySQL ?

> trap2. be aware that data shared between FM and SQL is not 'live' , what
> you see is not necessarily what you may have. Reload in MySQL is usually
> needed to force updates.

When you talk about "live" you mean istantaneous update ? Well, that
would be important
for me, with a tolrance of ~ 10 minutes...again, is that a crazy
idea ?

>
> You have 600 variables; how many fields?

Around the same

>> An alternate to export via csv, is just to script across data to MySQL,
> loop through the ESS shadow table records, pushing the data to =A0a
> related FM TOC. How pratical this is will depend on how many fields you
> have.

But this means that I have to rebuild the database on MySQL entirely
anyway, correct ?

>
> Suggestion; build yourself a test db pair to explore the basics involved
> in what you want to do

Good idea, I am planning to do exactly that.

> Suggestion; avoid doing anything remote; FM downloads for every sort
> etc, and can be very slow to execute what you might expect to be immediate=


My idea is to update data 'live' between windows (FM 9 server) and
Linux (MySQL server)
how much 'slow' could be this interaction ?


Thank you very much for the help and suggestions, I will take
advantage of those !

Diego

Re: Converting from FM 9 to MySQL and keep both

Diego B wrote:
>> mantra: FM is not meant to be a front end for MySQL
>
> Yes, fully agree. In facts I would like to have two different (but
> parallel) databases:
> one in FM, the corrispondent in MySQL. The last one should receive a
> live update
> from FM...is that crazy ? (No Irony here, just a question)

updates are not actually 'live' in the conventional sense, MySQL needs a
reload to display teh data added from the FM interface. There is mention
of this 'live' in the documentation


>
>
>> trap1: if you want server to execute anything in the way of scripts,
>> make sure EVERY script step is web compatible. Read the server help.
>
> Not sure here. Do you mean scripts for pushing data from FM to MySQL ?


any script run from server must be fully web compatible or it will fall
over. The Server help details what feedback you get; which may be
virtually none, i.e. the script just stops.




>
>> trap2. be aware that data shared between FM and SQL is not 'live' , what
>> you see is not necessarily what you may have. Reload in MySQL is usually
>> needed to force updates.
>
> When you talk about "live" you mean istantaneous update ? Well, that
> would be important
> for me, with a tolrance of ~ 10 minutes...again, is that a crazy
> idea ?

I haven't found out what automaically triggers a reload/refresh, or how
often. The documentation does warn that data may be out of synch.

public technical brief entitled "Introduction to External SQL Sources"
on FileMaker's Support Website. You can view it
http://www.filemaker.com/downloads/p...ief_ess_en.pdf.

1. Data Set
No data-broadcasting of ESS datasources...just "periodic refreshes"
which can result in inconsistent data. Reference: Pg 24

"FileMaker Pro 9 periodically refreshes its view of SQL data, but there
are times when the data visible in FileMaker Pro may be out of step with
the most current SQL data."




>
>> You have 600 variables; how many fields?
>
> Around the same

hmm, I'm guessing you pass almost every field manipulation through a
variable


>
>>> An alternate to export via csv, is just to script across data to MySQL,
>> loop through the ESS shadow table records, pushing the data to a
>> related FM TOC. How pratical this is will depend on how many fields you
>> have.
>
> But this means that I have to rebuild the database on MySQL entirely
> anyway, correct ?

I was thinking more along the lines of a 'division of data' .


Looking at your original post, your stated aim was to convert your
existing FM database into MySQL " I would like to convert the entire
database to MySQL"

Perhaps consider it this way, in FM you really only want to push the
primary data to MySQL, the derived secondary/tertiary... calculations
etc. would need to built in MySQL to achieve a full conversion.


>
>> Suggestion; avoid doing anything remote; FM downloads for every sort
>> etc, and can be very slow to execute what you might expect to be immediate
>
> My idea is to update data 'live' between windows (FM 9 server) and
> Linux (MySQL server)
> how much 'slow' could be this interaction ?

pushing data between servers is rapid (using a server schedule) compared
to running the same FM script that the FMS schedule runs by a large
factor. 2 secs cf. 4-5 minutes run from in the database client. Again a
simple test db and a few comparisons I have found to be been invaluable.

regards

Chris

Re: Converting from FM 9 to MySQL and keep both

------ CUT------
> pushing data between servers is rapid (using a server schedule) compared
> to running the same FM script that the FMS schedule runs by a large
> factor. 2 secs cf. 4-5 minutes run from in the database client. Again a
> simple test db and a few comparisons I have found to be been invaluable.
>
> regards
>
> Chris

Thank you very much Chris for all the suggestions and insight.
I am working on the conversion right now, maybe I will write an HowTO
here if anyone is interested.

Diego