FileMaker Pro SQL statement length limits

In a previous article, we shared some of the significant changes in ODBC-related features in FileMaker 11. This article digs deeper into the specific length limits of SQL statements when using the Execute SQL script step in various versions of FileMaker Pro.

How we tested

We started by creating simple test databases and hosting them with FileMaker Server Advanced 10 and 11 as well as MySQL 5.1. The databases were shared via ODBC and a DSN was created for each. Next, we created a simple FileMaker Pro database with a set of looping scripts that would attempt to add a record to each ODBC data source via the Execute SQL script step and a SQL statement similar to this:


INSERT INTO "myTable" ("myField") VALUES ('a lot of text...')

The script logged each attempt and then the length of the statement was increased until the INSERT failed or until a million-character statement was executed successfully.

Except where noted, we tested the MySQL data source with the latest ODBC driver available from MySQL. Some of the Mac tests used the Actual Technologies driver, as noted in the results. FileMaker Server Advanced 10 and 11 data sources were tested with the drivers supplied with each version.

Test results

Figure 1 lists the last successful result for each tested combination of FileMaker Pro version, system version, and data source. The Length column shows the longest statement executed successfully. The Next Result column shows the result of the next longest statement executed or "Limit not found" if no error occurred with a million characters.

Test Results
Figure 1: Test results

Findings

  • On a Mac, FileMaker Pro 9 and FileMaker Pro 11 each crashed consistently with statements longer than 32,767 characters when connecting to FileMaker Server Advanced 11.
  • FileMaker Pro 10 on both platforms consistently failed to execute statements longer than 4,096 characters when connecting to FileMaker Server Advanced 10. The script step did not produce an error, but no record was created.
  • FileMaker Pro 11 on a Mac crashed inconsistently when connecting to a MySQL 5.1 data source via the MySQL ODBC driver. Crashes began occurring with statements longer than 3,838 characters.
  • All other combinations resulted in either an error (at 32,768 or 262,145 characters) or did not reach a limit when tested with up to 1,000,000 characters.

    • Based on these tests, Figure 2 shows a summary of the maximum length for SQL statements executed with recent versions of FileMaker Pro and various data sources. These tests used the Execute SQL script step. The results likely also apply to an import from an ODBC data source, either manual or scripted. The results don’t necessarily apply to ESS data sources.

    Summary
    Figure 2: Summary

    Conclusion

    FileMaker's ODBC-related features are incredibly useful for connecting heterogeneous systems. However, pay careful attention to the length limits for your environment. Always test with larger-than-expected data to make sure your users don't encounter unexpected errors or crashes.

    Submitted by greg.lane on Tue, 05/25/2010 - 05:56