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.
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.
Figure 1: Test results
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.
Figure 2: Summary
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.