1 2 Previous Next 18 Replies Latest reply on May 24, 2017 1:01 PM by fubizitch

    SQL statement is too long

    fubizitch

      When I try to import one of my table into the relationship graph from mysql, weird things happen.  I get a message stating "sql statement is too long" no matter what I do.  I can't add records nor even see the current data.

       

      This happens on a couple of my tables, but not on other ones in the same database.  I think it might be because some tables are larger than others, but I'm not sure.

       

      This happens in Filemaker 16 on Windows.  Anyone else have this issue?

        • 1. Re: SQL statement is too long
          TSGal

          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.

          • 2. Re: SQL statement is too long
            beverly

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

            (in addition to what TSGal asks)

            beverly

            • 3. Re: SQL statement is too long
              fubizitch

              Hi guys,

               

              I'm using the ODBC connector 5.3 for windows from here:

              MySQL :: Download Connector/ODBC

               

              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.

              • 4. Re: SQL statement is too long
                fubizitch

                Notice how the records are there, but no fields are populated.  When I click on an editable field, I get this.

                 

                Screen Shot 2017-05-17 at 1.53.57 PM.png

                 

                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.)

                • 5. Re: SQL statement is too long
                  TSGal

                  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.

                  1 of 2 people found this helpful
                  • 6. Re: SQL statement is too long
                    beverly

                    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

                    2 of 2 people found this helpful
                    • 7. Re: SQL statement is too long
                      fubizitch

                      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?

                      • 8. Re: SQL statement is too long
                        TSGal

                        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.

                        2 of 2 people found this helpful
                        • 9. Re: SQL statement is too long
                          beverly

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

                          thank you!

                          beverly

                          • 10. Re: SQL statement is too long
                            fubizitch

                            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!

                            • 11. Re: SQL statement is too long
                              beverly

                              try the views, first?

                              beverly

                              • 12. Re: SQL statement is too long
                                fubizitch

                                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...

                                • 13. Re: SQL statement is too long
                                  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

                                  • 14. Re: SQL statement is too long
                                    user25271

                                    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

                                    1 2 Previous Next