2 Replies Latest reply on Mar 9, 2010 4:28 PM by jjbong

    Synching FM shadow table data with MySQL

    jjbong

      Summary

      Synching FM shadow table data with MySQL

      Description of the issue

      Not sure if this is a bug, or I'm just not doing it correctly. I could not find anything in the documentation, the knowledge base, or the forum. I am using FM10 as a presentation layer to MySQL, with generally good results. The problem I have is when I use the Execute SQL script step to do modifications in MySQL. What do I have to do to have the data appear immediately in FM shadow tables?When the Execute SQL operates on a small amount of data, a Commit Records and Refresh Window script step, the latter with flush cached join results, flush cached SQL data seems to work.However, when the Execute SQL operates on a large amount of data (1000 rows, say), it doesn't work. If I refresh (manually, with a script that just does the commit and refresh), the changes appear within two tries. I'd like to be able to do something in the script so it is automatic. The only thing I've found that does the trick is to pass the row count back from MySQL to FM, and then to loop doing commit and refresh, with .5 second pauses in between, until the count matches the Get(FoundCount). I'm suspicious of anything this non-deterministic. I have a relatively small test case that demonstrates the problem. 

        • 1. Re: Synching FM shadow table data with MySQL
          TSGal

          jjbong:

           

          Thank you for your post.

           

          If you send a SQL statement through the ODBC driver, it may take some time to filter the information.

           

          A better approach would be to edit through the shadow table instead of doing an Execute SQL since the link is already set.

           

          If I have misunderstood what you are trying to accomplish, please let me know.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Synching FM shadow table data with MySQL
            jjbong

            Thanks for your reply.

             

            The problem is that editing through the shadow table can take a very long time for a large number of records.

             

            What I am trying to accomplish is roughly the following. I have projects and materials. When a project is created, a subset of materials is assigned to the project (i.e., records are created in a separate assignment table with the project unique id) based on a flag in the materials record. Doing this the standard way through the shadow table takes many minutes. Shipping off an SQL statement to do it is almost instantaneous, except for the problem cited above. Even with my kludgey work-around, it takes maybe ten seconds.

             

            By the way, I have verified that the problem is not in the MySQL database, where the changes take place immediately. I have verified this by sending a second SQL statement that counts the records in the assignment table and ships it back to FileMaker through another shadow table. That count is available in FileMaker immediately, and is correct. What takes a while is for FileMaker to recognize the new set of records added.

             

            The problem is not that FileMaker takes a little bit of time, but rather that this is non-deterministic. If, for example, Refresh Window paused until all the records were recognized, that would be fine.