Thank you for your post.
What is the driver being used to access the MySQL database?
Can you describe the tables the don't work? How many fields? How many records?
Could you possibly post the SQL query and/or screenshot the exact error message?
(in addition to what TSGal asks)
I'm using the ODBC connector 5.3 for windows from here:
One table that doesn't work has 628 fields and 13565 records. Another one that doesn't work is 697 fields and 0 records.
The tables from the same database that do work: 242 fields, 1 record. 220 fields, 405 records.
That's why I think it might be the number of fields that are making the sql fail.
I don't know what the actual sql call is because it's an ess connection (live link to table in relationship graph). The error message is an error dialog box that simply says: "SQL statement is too long". That's all.
Notice how the records are there, but no fields are populated. When I click on an editable field, I get this.
I should clarify that the connection is using the Windows driver in Filemaker Server - the error message happens on both mac and windows Filemaker Pro. (Only with those two longer tables - the other 20+ shorter tables seem fine.)
1 of 2 people found this helpful
The query limit for MySQL is 8,192 characters. If the query exceeds 8,192 characters, then the error message "SQL statement is too long" is displayed.
As a workaround, reduce your query to limit the number of fields and/or records.
2 of 2 people found this helpful
Thank you for the update that you are using ESS. There should be a log of the queries (if you set up your ODBC to log them). The logs may tell you what query is being made by FileMaker.
For queries (ESS or your own SQL statements) that run into problems such as you have (too many columns, rows or length of data in columns), I tend to recommend that pre-queries be set up as Views (on the MySQL server). These can narrow down what you get with the ESS and that can be a real advantage!
If you are admin on the MySQL, then you may be able to set these up. If not, you'll need to contact DB admin/IT to do this for you.
Another tip (whether you use Views or not), is to
1. enter find mode (from another layout not tied to ESS tables)
2. go to a layout you need to search (this should be a form view layout for your data)
3. narrow your found set
4. perform find & enter browse mode
5. then and only then go to a list or table view if your found set is not so large.
This may or may not help you as you import from ESS into your table. Just some suggestions that have helped me in the past.
Thanks for the info and for possible workarounds. I think I should state our goal:
I'm trying to move some of our Filemaker tables over to MySQL. The reasons are for portability (syncing with several other buildings), prevention of record locking (since Filemaker records are automatically placed into a LOCKED state as soon as a user begins typing, which is hardly ever the desired behavior), high availability, and better interoperability with web sites.
We have many, many layouts and reports that already access these large tables, so would be nice to more easily switch over to an SQL-based back-end. Going forward, we'll be making calls from PHP directly and from FileMaker, and of course our PHP calls would be optimized, but we don't want to break our current layouts and reports.
An 8,192 character SQL statement call limit totally explains why our larger tables are failing even when few or no records exist. Why does this limit exist? It isn't specified in any of the documentation, and we've been preparing this move for months. Is there a setting to maybe increase this limit to something higher?
2 of 2 people found this helpful
These are the limits on the drivers that FileMaker can access:
MySQL for Windows: 8,192 characters
Oracle for Windows: 8,000 characters
SQLServer for Windows: 524,288 characters
Actual Technologies MySQL for Mac: 8,192 characters
Actual Technologies Oracle for Mac: 8,000 characters
Actual Technologies SQLServer for Mac: 8,000 characters
These values cannot be changed.
if this is not already in a KB, it would be handy to have them there.
OK, thanks for the info. Looks like we might have to start programming completely outside of Filemaker and perhaps use some way of syncing the databases together.
ESS would be very useful if it didn't have these limitations!
try the views, first?
Yah, we could try that - but I don't think the fields will line up the same if we were to do that, right? From my experimentation, the names are irrelevant when switching from an FM table to a MySQL one, so there have to be the same number of fields / order of creation for an easy switchover...
If you can query the MySQL directly, you can get the "field names" (columns) should that be your aim.
An export as csv (from MySQL) would give you the columns as well and be able to "map" to your FileMaker fields upon import there.
If your FileMaker field names are the same as the MySQL column names than 'matching' upon import will align the correct fields and columns.
If your FileMaker field names are not the same, then there is more work to do. Typically I use a temp table (as many as you need) to import from SQL and then change the field names (in the temp table in FileMaker) after import just for easier mapping to your fields.
Are you able to export from MySQL as .csv? and if so, does that import into FileMaker?
I don't mean to step into your problem with the limitations of the drivers. I just have run into these kinds of things. I also work with MS SQL and MySQL in my business (as a SQL db admin). There may be a few options for the SQL-FileMaker bridge. Let me know if you want to close the conversation on the reported issue.
Not sure this applies but I have always been able to get around the "too long" error by putting the full query in a text field and then set a variable to the text field and using the calculate query option to call the variable.
Another thing that works is query everything with an * and then only import what you want by de-selecting unwanted fields in the second set-up window