AnsweredAssumed Answered

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

Question asked by Dillik on Apr 17, 2012
Latest reply on Apr 27, 2012 by 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.

Outcomes