FileMaker ODBC Fetch Forward Error
After scouring the internet for this mysterious “Fetch Forward” error I sometimes get when migrating FileMaker records to other database formats via ODBC and Perl DBI, I discovered what causes it.
The error is seemingly random and goes like this:
[FileMaker][ODBC FileMaker Pro driver][FileMaker Pro]Unknown error (SQL-HY000)
[FileMaker][ODBC FileMaker Pro driver]An attempt to fetch forward has failed for table: TABLENAME (SQL-HY000)(DBD: st_fetc/SQLFetch err=-1) at myperlscript.pl line 123.
This occurs when FileMaker drops the ODBC connection or is unable to respond in a timely manner.
Solutions are as follows:
1) Set up your access script in a while loop so that when it aborts due to an error it will resume after x amount of time.
2) Minimize the number of indexes (if you had to recover a corrupted database check for indexes in your field definitions, FileMaker likes to create them for you).
3) Minimize the number of calculated fields and summary fields. Only use what is absolutely necessary.