3 Replies Latest reply on Apr 27, 2012 8:51 PM by Dillik

    ExecuteSQL's infinite loop (and DB corruption) after self-referencing query

    Dillik

      Summary

      ExecuteSQL's infinite loop (and DB corruption) after self-referencing query

      Product

      FileMaker Pro

      Version

      FMP 12.0v1

      Operating system version

      OS X 10.6.8

      Description of the issue

      Let's get this out of the way first: I used ExecuteSQL in a way that's obviously flawed in retrospect.  (Don't do this on a database you'd prefer to keep un-corrupt!)

      If you define a calculation field to contain a self-referencing ExecuteSQL statement, FMP will hang while applying your schema changes.  It's possible I didn't wait long enough before force-quitting, but it stands to reason FMP would be in an infinite loop at this point.  The result was a damaged file that was successfully recoverable as far as I can tell, but who wants to inflict a needless recover on their files?

      Steps to reproduce the problem

      Again, DON'T DO THIS IN A DATABASE YOU WANT TO KEEP.

      In a table called Sample, define a calculation field using this formula:
      ExecuteSQL ( "SELECT * FROM Sample"; ""; "" )

      In other words, the calculation field will be looking for all fields in the Sample table, which includes itself.  That's where the infinite loop comes in.  Presumably you could also enter a loop if the calculation field were named Calc and you asked for "SELECT Calc FROM Sample".

      Expected result

      FileMaker to realize I'm an idiot.

      Actual result

      FileMaker did what I asked and hung itself.

      Workaround

      If a calculation field references the same table that it's in, never ever select all columns.