SQL statement is too long

fubizitch:

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?

TSGal

FileMaker, Inc.

Could you possibly post the SQL query and/or screenshot the exact error message?

(in addition to what TSGal asks)

beverly

fubizitch:

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.

TSGal

FileMaker, Inc.

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.

MySQL :: MySQL 5.7 Reference Manual :: 23.5 Using Views

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.

beverly

fubizitch:

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.

TSGal

FileMaker, Inc.

if this is not already in a KB, it would be handy to have them there.

thank you!

beverly

try the views, first?

beverly

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.

beverly

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