Help with a two-way MySQL <--> FM9 connection

Hello,

This story started with a FM database (FM9 AS, Windows, and FM9 adv.
win/mac clients).
For different reasons (not the last educational), I converted the
database to MySQL using ODBC and
a specific program that support the migration from ODBC source (FM) to
MySQL (SQLYog, wonderful piece of software).
Now I have two databases: FM is still the master, data are inserted on
that during the day using our intranet, and every morning at 4 AM the
database is automatically converted in MySQL (reachable through the
network and fully functional). Of course this is a one-way relation
(FM --> MySQL)
In the near future I will have to open the databases to external
clients, and I would like to use the web for that.
The scenario here will be something like the following:

1) Intranet: nothing changes, intranet clients still will continue to
use FM.

2) Internet: clients will use a PHP interface, inserting data on the
MySQL database (classic PHP/MySQL solution). Obviously, I could use
PHP/FM instead, but for several reasons I would prefer using MySQL for
the web counterpart.

3) Every day, or even 4-5 times per day, the two databases should
exchange new records so that, at 7 AM every morning both MySQL and FM
be fully upgraded. In other words, I would like a two-way relation (FM
<--> MySQL)

Unfortunately, my programming skills are very basic, but maybe I could
use SQLYog again. Unfortunately, while the program has no problems
migrating data from ODBC to MySQL, does not support the connection
between MySQL and the ODBC database (FM).
A soultion could be to use a Definitive MySQL database that collects
data from both the databases:

FM(ODBC) --> DefMySQL <-- WebMySQL(ODBC)

but I do not know if there other straightforward ways to get a bi-
directiponal connection between FM(ODBC) and MySQL(ODBC).

Any idea will be greatly appreaciated (as usual !)

Diego

On 2008-08-23 11:45:18 -0700, Diego B said:

> 2) Internet: clients will use a PHP interface, inserting data on the
> MySQL database (classic PHP/MySQL solution). Obviously, I could use
> PHP/FM instead, but for several reasons I would prefer using MySQL for
> the web counterpart.
>
> 3) Every day, or even 4-5 times per day, the two databases should
> exchange new records so that, at 7 AM every morning both MySQL and FM
> be fully upgraded. In other words, I would like a two-way relation (FM
> <--> MySQL)
>
> Unfortunately, my programming skills are very basic, but maybe I could
> use SQLYog again. Unfortunately, while the program has no problems
> migrating data from ODBC to MySQL, does not support the connection
> between MySQL and the ODBC database (FM).
> A soultion could be to use a Definitive MySQL database that collects
> data from both the databases:
>
> FM(ODBC) --> DefMySQL <-- WebMySQL(ODBC)
>
> but I do not know if there other straightforward ways to get a bi-
> directiponal connection between FM(ODBC) and MySQL(ODBC).
>
> Any idea will be greatly appreaciated (as usual !)

Synchronization issues, particularly in two directions, can be a bit touchy.

Biggest issue is: Are edits to existing records going to be allowed in
one or both systems? If so, then you'll have a mare's nest to
disentangle every time you synchronize.

Next to consider is creation of primary keys. Each system MUST create
key values that will be unique in BOTH systems. So you may need to
encode a system-origin string in your Keys, otherwise you may get
overlap if you're simply using serial numbers in one or both systems.

What I have done in the past is make one system the "source" of all
records. For example, in the FM, all new records are uploaded to the
SQL system. Then, having a full set of all records in SQL, the FM
system is wiped clean, and the ENTIRE set of records is downloaded to
FM. Synchronization done. However, this method has drawbacks if your
record set is large, if the import takes a long time, and if the sync
must done during working hours. The process described above worked fine
at 2am, when no one was working in the files.

If edits are taking place in both systems, then you have to write rules
as to which edits take precedence when the same record has been edited
in both systems. If those rules aren't codeable, then a human being is
going to have to view the records in question (with both edits) and
decide which ones will be written as authoritative. A human in the
process means the possibility of bottlenecks and/or errors.

If it's strictly new records you're dealing with, then use FM as the
manager of the process, using SQL statements to query for new records
and pull them down to FM, and then doing the same to push FM records up
to the SQL. Centralize the process in one app, and you'll have less
grief than trying to push data into FM using ODBC. That's not what FM
is designed for.

Try putting your SQL tables into the FM Relationship graph and working
with them that way. For purposes of synchronization, this should work
well.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

On Sat, 23 Aug 2008 12:02:05 -0700, Lynn Allen
wrote:

>On 2008-08-23 11:45:18 -0700, Diego B said:
>>
>> FM(ODBC) --> DefMySQL <-- WebMySQL(ODBC)
>>
>> but I do not know if there other straightforward ways to get a bi-
>> directiponal connection between FM(ODBC) and MySQL(ODBC).
>>
>> Any idea will be greatly appreaciated (as usual !)
>
>Synchronization issues, particularly in two directions, can be a bit touchy.
>
>Biggest issue is: Are edits to existing records going to be allowed in
>one or both systems? If so, then you'll have a mare's nest to
>disentangle every time you synchronize.
>
>Next to consider is creation of primary keys. Each system MUST create
>key values that will be unique in BOTH systems. So you may need to
>encode a system-origin string in your Keys, otherwise you may get
>overlap if you're simply using serial numbers in one or both systems.

Lynn, could you elaborate on this a little? For example, doesn't FMP
automatically create its own internal unique keys?

thanks

--
FW


FileMaker Pro 8.5 Advanced on Windows XP Pro SP2
FileMaker Server 8.0 on Windows 2003 Server R2

On 2008-08-23 15:48:29 -0700, FastWolf said:

> Lynn, could you elaborate on this a little? For example, doesn't FMP
> automatically create its own internal unique keys?

Er, no. And yes.

Internally, FM generates a Record ID, and one can externalize this
value with Get(RecordID). However, the record ID sequence contains
large gaps for reserved values which disturb some developers, and it is
reset whenever a set of records is imported into a clone. A
non-persistent key will disrupt any relationships based upon it,
clearly.

I generate my own key values for all records using an auto-enter calc
(text result) with the formula:

GetAsText(GetAsNumber(Get(CurrentTimeStamp))) & " " & Serial

The serial is there to ensure that there are no duplicates. The
Timestamp string, transformed into a series of numbers, is there to
ensure that I don't have to reset the Serial number value when I
upgrade the file. I don't care if there are duplicate serial numbers,
the timestamp will ensure uniqueness. And I don't care if more than one
record is created in a second, the serial is there to ensure
uniqueness. Duplicates could occur if someone created a bunch of
records, then upgraded the file, retreating back to previous serials,
and ALSO, had reset or had a clock that was overlapping the previous
machine's clock, and somehow got a serial that fell on the same second
as the previously created records. I regard this as unlikely enough not
to cause me worry.

These keys are the ones I use in all my relationships. Some developers
simply use serial numbers and reset them every time they upgrade a
file. With as many tables as I have in my solutions, this becomes
burdensome and too prone to developer error. I prefer the easy way of
never having to reset my serials during upgrades.

If one were doing two-way synchronization between two systems, one
could also append a "F" to the key generated in FM. That way the
records entered there could be distinguished from the SQL entered
records, which probably have only a serial value as a key.

BTW, the users never see these keys. They are always hidden and used
only for relating records.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

On Sat, 23 Aug 2008 17:12:48 -0700, Lynn Allen
wrote:

>On 2008-08-23 15:48:29 -0700, FastWolf said:
>
>> Lynn, could you elaborate on this a little? For example, doesn't FMP
>> automatically create its own internal unique keys?
>
>Er, no. And yes.
>
>Internally, FM generates a Record ID, and one can externalize this
>value with Get(RecordID). However, the record ID sequence contains
>large gaps for reserved values which disturb some developers, and it is
>reset whenever a set of records is imported into a clone. A
>non-persistent key will disrupt any relationships based upon it,
>clearly.
>
>I generate my own key values for all records using an auto-enter calc
>(text result) with the formula:
>
>GetAsText(GetAsNumber(Get(CurrentTimeStamp))) & " " & Serial

This is brilliant. Thanks for sharing this technique.

>... Duplicates could occur if someone created a bunch of
>records, then upgraded the file, retreating back to previous serials,
>and ALSO, had reset or had a clock that was overlapping the previous
>machine's clock, and somehow got a serial that fell on the same second
>as the previously created records. I regard this as unlikely enough not
>to cause me worry.

"Unlikely" in this case being defined as "practically impossible". You
set a high standard, Lynn, and I appreciate it. Thanks again.

--
FW

FileMaker Pro 8.5 Advanced on Windows XP Pro SP2
FileMaker Server 8.0 on Windows 2003 Server R2