3

I have FM11A (client only for now, but the project should run on a FM Server either 11 or 12-13) on a WinXP machine where I also run a MySQL server (5.5) for testing purposes.

I have a database fully working on filemaker and I am developing a mirror MySQL database to go online.

My aim is to be able to perform bidirectional sync between Fm and MySQL leaving the 2 databases as entirely independent entities (so I would avoid to have FM write directly on MySQL tables, i.e. having FM as front-end and MySQL as back-end).

I have been able to import the MySQL table (demographics) into the FM database (where another 'demographics' table is present) the 2 tables have exactly the same fields, and importing from MySQL-Demogr into FM-Demogr using ODBC\ESS works very well.

When I open FM and Import records from MySQL using the shadow table everything goes smoothly and I can see new records on the original FM table, as I wished.

Of note, I am also able to write data directly on MySQL-Demogr table using FM and writing on the shadow Table.

The problem comes when I try to export FM data to MySQL: apparently the ODBC\ESS system works very well in 1 direction (FM import from *SQL) but not the other (FM export to *SQL) I am still trying to figure out the most efficient (i.e. easy\quick and scalable) way to export records, originally inserted in FM, in MySQL.

  • The old way would be to script an export to .csv file from FM, then loading new data to MySQL, maybe using a temporary table inside MySQL. This is supposed to be very quick and is absolutely doable, although I would rather use ODBC\ESS if at all possible

  • The easiest way would be to export directly from FM into MySQL using the shadow table but it does not work:

a. Exporting from FM to the same file or to an ODBC source (MySQL) is apparently not possible (might you please confirm?) b. When I open the MySQL shadow table (MySQL-Demogr) from inside FM and Import new records (this time going from FM-Demogr --> MySQL-Demogr) it says records have been added in MySQL, but in the facts, nothing happened and when I go to MySQL the table is unchanged.

  • Another chance is to use filemaker with or without specific plugin to run an SQL query and let it access the MySQL-Demogr shadow Table through ODBC...I have looked into some examples available online and this is not entirely clean and cut to me, but I was reviewing records of 2003-2009, apparently in the pre-ESS era. Maybe with the new ExecuteSQL script step things are a little bit mopsr straightforward now ? If you have any advice on specific plugins (under $100) that could help me in this, I am also interested in making the investment

  • Finally, I could use a third package (either Excel or SQLYog) to run the SQL for me connecting the 2 databases (FM and MySQL) and make the script to run on a regular base. No issues with that but I would keep everything inside FM-MySQL if at all possible.

Thank you very much in advance for your help.

Jonathan Eustace
  • 2,469
  • 12
  • 31
  • 54
Diego
  • 127
  • 2
  • 13

1 Answers1

1

Since you mentioned you're open to 3rd party software, I'll mention MirrorSync (http://mirrorsync.com), which can do what you need. Disclaimer: I am the author of the software, so I am obviously biased ;-)

Jesse Barnum
  • 6,507
  • 6
  • 40
  • 69