Two-way MySQL <--> FM9 connection Options, part II

Hello again,

Unfortunately, I cannot post in the previous thread opened some days
ago (and I do not know why),
so I will try a new thread to keep talking on the matter:


First of all thank you Lynn, for the explanation and insight.
Sometime ago, learned right here and from your words how to use a
timestamp as
part of the ID, and now it is the standard for me :-)
Regarding the synchornization, yes it is bi-directional and yes,
it will concern only new records creation (no modification of existing
records
so far).
If I understood correctly I have two ways to keep everything
centralized:
1) Import new records created in MySQL through the web in FM using
ODBC/External SQL statement (but you do not like this way)

2) Using ESS, link all the MySQL tables to FM, maybe getting rid of
the original FM tables,
recreating relationships and using FM as a front-end for MySQL (I
know, this is deprecated
but for synchornizations purposes should work fine...no ?)

Regarding solution 1, Importing through ODBC, I am sure FastWolf could
help (might you
kindly show the procedure you use for that, in particular, after have
selected the records in MySQL, how
can you insert those new records in FM ? I am sorry, I know some SQL
but mainly for querying purposes)

Regarding solution 2, using ESS I have two questions:

a) If I substitute the original FM tables with the MySQL ones (names
of tables and fields are exacting the same)
will I be able to link the "new" fileds to the old layout ? and How ?

b) I would like to know some experiences here about the performance of
FM using ESS. We are talking of really few records in a database
perspective
(just 1000) but the database will develop in future and I would not
like to have to rewrite everything because it gets too slow...

Thank you always for the help,

Diego

On 2008-08-25 16:20:52 -0700, Diego B said:

> If I understood correctly I have two ways to keep everything
> centralized:
> 1) Import new records created in MySQL through the web in FM using
> ODBC/External SQL statement (but you do not like this way)

I'm not sure I said I didn't like that, perhaps I didn't explain well.
Actually importing into FM from SQL works a treat, and has since FM6.
The import from SQL is very fast. What I don't like is the idea of
running some routine in the SQL tables to PUSH data into FM through
ODBC. This is not what the ODBC driver is designed for, and FM doesn't
like it much.
>
> 2) Using ESS, link all the MySQL tables to FM, maybe getting rid of
> the original FM tables,
> recreating relationships and using FM as a front-end for MySQL (I
> know, this is deprecated
> but for synchornizations purposes should work fine...no ?)

FM isn't supposed to be used as a front end for SQL, particularly if
there are other users entering & editing data through ANOTHER front
end, in this case the web. There are record-locking issues and edit
issues, and while you say that "at this time" there is no plan to allow
edits in the data, only new record entry, I suggest you not trust your
users (clients) farther than you can throw them, as they *always* get
ideas later.

I personally would not try to use only SQL tables. Many FM operations
will proceed MUCH faster in native FM tables rather than trying to
transfer finds and sorts into SQL. And I shudder to think what might
happen with a looping script.

>
> Regarding solution 1, Importing through ODBC, I am sure FastWolf could
> help (might you
> kindly show the procedure you use for that, in particular, after have
> selected the records in MySQL, how
> can you insert those new records in FM ? I am sorry, I know some SQL
> but mainly for querying purposes)

If you send a SELECT query to the SQL table, the return IS an import of
records fulfilling the query conditions. So if you ask for all records
created between a certain timestamp and a second timestamp, and 150
records satisfy that condition, you'll get an import of 150 records.

You'll need an "intermediate" processing table, as you will not be able
to directly accept that data into your main FM table. SQL data has
particular formatting that often needs post-processing once you get it
into FM. Like timestamps are wrong-way to and need translation, and
there may be codes in the SQL data ("20" = "Active") that you need to
convert before feeding into the FM table. This processing table will
be the one that issues the SQL statement. Usually one builds the SQL
statement in a global or variable, inserting query conditions such as
creation time or category or "new" record field mark.

Then once the import is done and the post-processing proceeds, you can
do a direct import into the FM table the users want to use. What I have
done in the past is in the processing table, I have a bunch of fields
named the same as the SQL columns which receive the import, and a bunch
of calculated fields with the same names as my FM table fields. The
formula in the calc field is something like "fc01" (SQL column name)
which is a direct translation.

This way, much of the processing can be done in field definitions, and
you can use "Matching Names" import into FM, so if you add another
field later, you don't have to redo the import.

--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

On Aug 25, 8:00*pm, Lynn Allen wrote:
> On 2008-08-25 16:20:52 -0700, Diego B said:
>
> > If I understood correctly I have two ways to keep everything
> > centralized:
> > 1) Import new records created in MySQL through the web in FM using
> > ODBC/External SQL statement (but you do not like this way)
>
> I'm not sure I said I didn't like that, perhaps I didn't explain well.
> Actually importing into FM from SQL works a treat, and has since FM6.
> The import from SQL is very fast. *What I don't like is the idea of
> running some routine in the SQL tables to PUSH data into FM through
> ODBC. This is not what the ODBC driver is designed for, and FM doesn't
> like it much.
>
>
>
> > 2) Using ESS, link all the MySQL tables to FM, maybe getting rid of
> > the original FM tables,
> > recreating relationships and using FM as a front-end for MySQL (I
> > know, this is deprecated
> > but for synchornizations purposes should work fine...no ?)
>
> FM isn't supposed to be used as a front end for SQL, particularly if
> there are other users entering & editing data through ANOTHER front
> end, in this case the web. *There are record-locking issues and edit
> issues, and while you say that "at this time" there is no plan to allow
> edits in the data, only new record entry, I suggest you not trust your
> users (clients) farther than you can throw them, as they *always* get
> ideas later.
>
> I personally would not try to use only SQL tables. Many FM operations
> will proceed MUCH faster in native FM tables rather than trying to
> transfer finds and sorts into SQL. *And I shudder to think what might
> happen with a looping script.
>
>
>
> > Regarding solution 1, Importing through ODBC, I am sure FastWolf could
> > help (might you
> > kindly show the procedure you use for that, in particular, after have
> > selected the records in MySQL, how
> > can you insert those new records in FM ? I am sorry, I know some SQL
> > but mainly for querying purposes)
>
> If you send a SELECT query to the SQL table, the return IS an import of
> records fulfilling the query conditions. So if you ask for all records
> created between a certain timestamp and a second timestamp, and 150
> records satisfy that condition, you'll get an import of 150 records.
>
> You'll need an "intermediate" processing table, as you will not be able
> to directly accept that data into your main FM table. SQL data has
> particular formatting that often needs post-processing once you get it
> into FM. Like timestamps are wrong-way to and need translation, and
> there may be codes in the SQL data ("20" = "Active") that you need to
> convert before feeding into the FM table. *This processing table will
> be the one that issues the SQL statement. Usually one builds the SQL
> statement in a global or variable, inserting query conditions such as
> creation time or category or "new" record field mark.
>
> Then once the import is done and the post-processing proceeds, you can
> do a direct import into the FM table the users want to use. What I have
> done in the past is in the processing table, I have a bunch of fields
> named the same as the SQL columns which receive the import, and a bunch
> of calculated fields with the same names as my FM table fields. The
> formula in the calc field is something like "fc01" (SQL column name)
> which is a direct translation.
>
> This way, much of the processing can be done in field definitions, and
> you can use "Matching Names" import into FM, so if you add another
> field later, you don't have to redo the import.
>
> --
> Lynn Allen
> --www.semiotics.com
> Member Filemaker Business Alliance
> Long Beach, CA

Wow !
As always clear explanation of great ideas.
I will go for ODBC/SQL statements then,
although the "cleaning up" process of the improted records
from MySQL is not totally clear yet.
I would really appreciate some templates or links to a practical
scenario, but maybe I am asking too much now.
Anyway, thanks a bunch for the help, I will start working
on the project soon.

Diego

On Tue, 26 Aug 2008 07:00:24 -0700 (PDT), Diego B
wrote:

[snip]

>I will go for ODBC/SQL statements then,
>although the "cleaning up" process of the improted records
>from MySQL is not totally clear yet.
>I would really appreciate some templates or links to a practical
>scenario, but maybe I am asking too much now.

"Asking too much" ... lol ... I personally am descended from a long
and distinguished line of luck-pushers, and my partner's ancestors in
the New World were selected by some of the finest judges in England
.... but I digress ...

I think what Lynn means by "cleaning up" the data is taking the data
you get from SQL and making them compatible with your FMP solution.
The way I do this is: I import the SQL data into a table set up to
receive them. Then for each field that needs "cleaning up" I create a
calc field that performs whatever operations needed to do that. To
borrow Lynn's example, a typical date in SQL might be in the format
yyyy-mm-dd, but your FMP solution (and your users) might want their
dates in the format dd/mm/yyyy. So you would pull the yyyy-mm-dd date
from SQL into one (non-calc) field, then run a function on it in a
second (calc) field to reformat it to dd/mm/yyyy. That's an example
of the basic concept of "cleaning up" SQL data for FMP, if I
understand it aright.

hope this helps

--
FW
RL's Axiom #14: "The result of doing nothing is, invariably, nothing."

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

On 2008-08-26 11:10:56 -0700, FastWolf said:

> I think what Lynn means by "cleaning up" the data is taking the data
> you get from SQL and making them compatible with your FMP solution.
> The way I do this is: I import the SQL data into a table set up to
> receive them. Then for each field that needs "cleaning up" I create a
> calc field that performs whatever operations needed to do that. To
> borrow Lynn's example, a typical date in SQL might be in the format
> yyyy-mm-dd, but your FMP solution (and your users) might want their
> dates in the format dd/mm/yyyy. So you would pull the yyyy-mm-dd date
> from SQL into one (non-calc) field, then run a function on it in a
> second (calc) field to reformat it to dd/mm/yyyy. That's an example
> of the basic concept of "cleaning up" SQL data for FMP, if I
> understand it aright.

Exactly.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

Diego B wrote:
> Hello again,
>
> Unfortunately, I cannot post in the previous thread opened some days
> ago (and I do not know why),
> so I will try a new thread to keep talking on the matter:
>
>
> First of all thank you Lynn, for the explanation and insight.
> Sometime ago, learned right here and from your words how to use a
> timestamp as
> part of the ID, and now it is the standard for me :-)
> Regarding the synchornization, yes it is bi-directional and yes,
> it will concern only new records creation (no modification of existing
> records
> so far).
> If I understood correctly I have two ways to keep everything
> centralized:
> 1) Import new records created in MySQL through the web in FM using
> ODBC/External SQL statement (but you do not like this way)
>
> 2) Using ESS, link all the MySQL tables to FM, maybe getting rid of
> the original FM tables,
> recreating relationships and using FM as a front-end for MySQL (I
> know, this is deprecated
> but for synchornizations purposes should work fine...no ?)
>
> Regarding solution 1, Importing through ODBC, I am sure FastWolf could
> help (might you
> kindly show the procedure you use for that, in particular, after have
> selected the records in MySQL, how
> can you insert those new records in FM ? I am sorry, I know some SQL
> but mainly for querying purposes)
>
> Regarding solution 2, using ESS I have two questions:
>
> a) If I substitute the original FM tables with the MySQL ones (names
> of tables and fields are exacting the same)
> will I be able to link the "new" fileds to the old layout ? and How ?
>
> b) I would like to know some experiences here about the performance of
> FM using ESS. We are talking of really few records in a database
> perspective
> (just 1000) but the database will develop in future and I would not
> like to have to rewrite everything because it gets too slow...
>
> Thank you always for the help,
>
> Diego


One thing that you should perhaps investigate in advance, is the
limitations imposed by server schedules. Given the scenario outlined so
far, it would not surprise me if ultimately much of the data to and fro,
you would be thinking of happening automatically (i.e server scheduled
scripts. Look at the web incompatible steps closely; they are not only
web incompatible, but server schedule incompatible. So it may be
necessary to loop push ODBC data to FM (can be painfully slow), rather
than import, because import does not run from a server schedule...


I would suggest building a simple start to end test process build before
working up the details, to find any holes in the expectations.

On Aug 26, 6:28*pm, Chris Brown
wrote:
> Diego B wrote:

> One thing that you should perhaps investigate in advance, is the
> limitations imposed by server schedules. Given the scenario outlined so
> far, it would not surprise me if ultimately much of the data to and fro,
> you would be thinking of happening automatically (i.e server scheduled
> scripts. Look at the web incompatible steps closely; they are not only
> web incompatible, but server schedule incompatible. So it may be
> necessary to loop push ODBC data to FM (can be painfully slow), rather
> than import, because import does not run from a server schedule...
>
> I would suggest building a simple start to end test process build before
> working up the details, to find any holes in the expectations.- Hide quoted text -
>
> - Show quoted text -

Oh oh, we got a problem here :-(
I necessarily need to make this as an independent (scripted ?)
procedure automatically started every day, and maybe connected to a
button inside
the database so that every user, opening the app. can perform an
"update" of the records
imporitng those added throught the web in MySQL (I do not expect they
will be many anyway).
I have complete control on the "server" (Win XP pro) but if I cannot
use FM server to schedule this, then I am lost...
how could I script an automatic "import" (it looks like importing is
much better
then pushing data into FM...) ?

Diego

On Wed, 27 Aug 2008 06:28:27 -0700 (PDT), Diego B
wrote:

>On Aug 26, 6:28*pm, Chris Brown
>wrote:
>> Diego B wrote:
>
>> One thing that you should perhaps investigate in advance, is the
>> limitations imposed by server schedules. Given the scenario outlined so
>> far, it would not surprise me if ultimately much of the data to and fro,
>> you would be thinking of happening automatically (i.e server scheduled
>> scripts. Look at the web incompatible steps closely; they are not only
>> web incompatible, but server schedule incompatible. So it may be
>> necessary to loop push ODBC data to FM (can be painfully slow), rather
>> than import, because import does not run from a server schedule...
>>
>> I would suggest building a simple start to end test process build before
>> working up the details, to find any holes in the expectations.- Hide quoted text -
>>
>> - Show quoted text -
>
>Oh oh, we got a problem here :-(
>I necessarily need to make this as an independent (scripted ?)
>procedure automatically started every day, and maybe connected to a
>button inside
>the database so that every user, opening the app. can perform an
>"update" of the records
>imporitng those added throught the web in MySQL (I do not expect they
>will be many anyway).
>I have complete control on the "server" (Win XP pro) but if I cannot
>use FM server to schedule this, then I am lost...
>how could I script an automatic "import" (it looks like importing is
>much better
>then pushing data into FM...) ?

Oh it is, that's why getting data out of SQL is sometimes called a
"pull". You import from SQL using an ODBC connection. The driver is
on the FMP install CD. Use the Import Records (for pulls) and Execute
SQL (for anything else) script steps. Use calc fields to construct
the actual queries.

hope this helps

--
FW

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

On Aug 27, 12:48*pm, FastWolf wrote:
> On Wed, 27 Aug 2008 06:28:27 -0700 (PDT), Diego B
> wrote:
>
>
>
>
>
> >On Aug 26, 6:28*pm, Chris Brown
> >wrote:
> >> Diego B wrote:
>
> >> One thing that you should perhaps investigate in advance, is the
> >> limitations imposed by server schedules. Given the scenario outlined so
> >> far, it would not surprise me if ultimately much of the data to and fro,
> >> you would be thinking of happening automatically (i.e server scheduled
> >> scripts. Look at the web incompatible steps closely; they are not only
> >> web incompatible, but server schedule incompatible. So it may be
> >> necessary to loop push ODBC data to FM (can be painfully slow), rather
> >> than import, because import does not run from a server schedule...
>
> >> I would suggest building a simple start to end test process build before
> >> working up the details, to find any holes in the expectations.- Hide quoted text -
>
> >> - Show quoted text -
>
> >Oh oh, we got a problem here :-(
> >I necessarily need to make this as an independent (scripted ?)
> >procedure automatically started every day, and maybe connected to a
> >button inside
> >the database so that every user, opening the app. can perform an
> >"update" of the records
> >imporitng those added throught the web in MySQL (I do not expect they
> >will be many anyway).
> >I have complete control on the "server" (Win XP pro) but if I cannot
> >use FM server to schedule this, then I am lost...
> >how could I script an automatic "import" (it looks like importing is
> >much better
> >then pushing data into FM...) ?
>
> Oh it is, that's why getting data out of SQL is sometimes called a
> "pull". *You import from SQL using an ODBC connection. *The driver is
> on the FMP install CD. *Use the Import Records (for pulls) and Execute
> SQL (for anything else) script steps. *Use calc fields to construct
> the actual queries.
>
> hope this helps
>
> --
> FW
>
> FileMaker Pro 8.5 Advanced on Windows XP Pro SP2
> FileMaker Server 8.0 on Windows 2003 Server R2- Hide quoted text -
>
> - Show quoted text -

Thank you for the prompt reply, and I have to say that ODBC is already
setup
and running in my FMAS. The problem now is: how can I automate the
import script
in FM if the server version does not let me do that (Import/Export
operations are not schedulable
in FMAS 9...) ?

Diego

On 2008-08-27 14:01:02 -0700, Diego B said:

> Thank you for the prompt reply, and I have to say that ODBC is already
> setup
> and running in my FMAS. The problem now is: how can I automate the
> import script
> in FM if the server version does not let me do that (Import/Export
> operations are not schedulable
> in FMAS 9...) ?

Robots.

Meaning a client machine set up with a scheduling plug-in like Troi
Events (there are other plugins that do scheduling). The machine is on
all night. The plugin triggers the script to run at a certain time, and
at the end of the script, the plugin is set to run the next time. The
import is done on the client machine, not the server. Importing is not
available on the server.

It's a pain in the butt to dedicate a machine this way, but many of us
have done so in order to run scheduled scripts.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA