1 Reply Latest reply on Mar 26, 2015 11:13 AM by ChrisBishop

    "SQL statement is too long." issue in ODBC connection

    AndrewChang

      Title

      "SQL statement is too long." issue in ODBC connection

      Post

                Hi, all:

                We'd like to pull the data from Oracle to FileMaker Server, and we used FileMaker Pro to access that pulled data. The FileMaker Server was installed in Windows platform and the version of FMS is 13. The ODBC driver was installed in this Windows system where FMS 13 located, and the ODBC connection is working well in the System DSN I setup in "ODBC Data Source Administrator" in Windows. I can use Oracle SQL Developer and SQL*Plus to retrieve back the data well.

                In the Filemaker Pro side, I setup everything correctly in the "External Data Sources..." so that I can grab the table in Oracle database back correctly and add it in the "Tables" when "Manage" my FileMaker database. In order to show the filed value in the table, I created a new layout and used "Field Picker" to drag and drop one of the fields (let's say "TEST ID") from the Oracle database to this new layout. After I switch the layout to "Browse" mode, I found the number of the total records is correct, however,  the value was disappearing. When I clicked the area that is supposed to show the value, it popped up "SQL statement is too long." (Please see the attachment.) I google the issue, and it seems like due to the 4096 characters limitation in statement length in FileMaker Pro. (In this page : http://help.filemaker.com/app/answers/detail/a_id/7418/~/sql-queries-limited-to-4096-characters-in-filemaker-pro , which does not have the solution for the issue. ) 

                Therefore, I just wonder if anyone met this issue before, and please let me know how to solve this issue. Thank you very much for your help.

                Andrew

            

      odbc_sql.png

        • 1. Re: "SQL statement is too long." issue in ODBC connection
          ChrisBishop

          I am getting the exact same issue.  Am using FM Server on OS X Mavericks (13v5) using the Actual ODBC driver for MySQL, and am using version: 5.6.23 - MySQL Community Server (GPL) (also on OS X Mavericks).

          I have the database connected properly through the ODBC Manager and hooked up in the External Data Sources of FileMaker.  It is properly reading from our Artists table, which has 178 fields.  We're able to navigate the records, import, delete, etc. using it in a FileMaker layout/relationship.

          However, another table, our Shots table that has 526 fields, is giving the same issues as mentioned in the above post - even when viewing a single field and one record.

          I believe that a table with a lot of fields causes the SQL string to be too long.  I don't know exactly how FileMaker (or the ODBC driver) works, but my best guess is that it creates a SQL Select statement that names all fields in the shadow table, and then fails because the number of fields makes this statement extremely long.

          After removing many "shadow fields" from the shadow table in the FileMaker Fields Definitions, the error is gone and all is well.

          Is there any way to extend the maximum length of the SQL statement, or are we hitting a roadblock with the MySQL server itself?  Perhaps it's just a setting in MySQL to change, like max_allowed_packet, or something similar?