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.