14 Replies Latest reply on Jan 24, 2012 4:58 PM by jormond

    Weird ODBC error when accessing MySQL table via ESS

    cmdrsalamander

      Dear all,

       

      I have an ESS/ODBC error that I cannot fix. When I try access a MySQL table via ESS, I get the following error:

       

      ODBC Error: {MySQL][ODBC 3.51 Driver][mysqld-5.0.92-community-log]You have an erro in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'customers_id IN (24,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,' at line 1

       

      I think I did the ESS config right because other tables display with no issues.


      Any help would be greatly appreciated. (Translation: HELP!)

       


      Regards,
      Salman


        • 1. Re: Weird ODBC error when accessing MySQL table via ESS
          beverly

          Salman, are you making a SQL query yourself, or just using the MySQL table as an external occurence on the relationship graph? Are you sure there is a unique primary key in the MySQL table? Is there a relationship between this TO and any others on the relationship graph? Is there a relationship between this table and any others IN MySQL? Is it a table or view in MySQL?

          Thanks,

          Beverly

          • 2. Re: Weird ODBC error when accessing MySQL table via ESS
            cmdrsalamander

            Beverly,

             

            Matt O'Dell told me what to do. I had 251 fields in the customers shadow table from the MySQL database. He told me to "delete" some of them. Now 199 fields are coming over clean.

             

            Now, where would I go to press the "correct answer" button?

             

            Thank you,

            Salman

             

            • 3. Re: Weird ODBC error when accessing MySQL table via ESS
              colas

              Hello,

               

              I have the exact same problem.

              One particular MySQL table have 193 fields and I got the ODBC Error when using it as an external datasource in FileMaker 11. If I delete some of the fields to get around 160-170 fields total in FileMaker, it works.

               

              Where is the limit ? How many fields can handle FileMaker with mysql (and Actual Tech ODBC driver) ?

              Why are you coming clean with 199 fields, and I got an error with 193 fields ... Maybe the size limit of a query somewhere (fields name)?

               

              If I have too many fields in one table and can't "drop" some fields, should I use mysql views to get every fields in FileMaker ?

               

              Thanks,

               

              Colas

              • 4. Re: Weird ODBC error when accessing MySQL table via ESS
                beverly

                Wow! I guess I never worked with more than 50 columns through ESS. That's good to know that there is a problem with too many!

                 

                REMEMBER: if you "delete" the SQL fields in the FileMaker shadow table, they are not _gone_ from the SQL table. If you need to bring them back (for some reason), click the SYNC button.

                 

                Beverly

                • 5. Re: Weird ODBC error when accessing MySQL table via ESS
                  Mike_Mitchell

                  I wonder if it's a timeout issue? I know if SQL queries get too long, that can happen. Maybe as the ESS engine is writing the queries, the SQL database is giving up the ghost?

                   

                  Just a thought.

                   

                  Mike

                  • 6. Re: Weird ODBC error when accessing MySQL table via ESS
                    colas

                    Hello,

                     

                    I got an answer from Actual Technologies support.

                     

                    "We have learned that FileMaker has an internal limit of 5000 characters for SQL queries that it constructs to handle ESS reads and writes.  This is a limitation of FileMaker, not the driver. FileMaker, Inc, is aware of the limitation, and they will probably fix it (or increase the limit) in a future version of FileMaker."

                     

                    The workaround is to create views in mysql.

                     

                    I hope this will be solved in next FM Version.

                     

                    Colas

                    • 7. Re: Weird ODBC error when accessing MySQL table via ESS
                      LSNOVER

                      Beverley, it's not the number of columns per say, it's the size of the SQL command string that Filemaker is building behind the scenes in combination with how much data is coming back in those fields.  There is a limit of roughly 16K characters, based on the ODBC drivers (32K, but with double-byte characters being used by FM, it's halved).   If you can access the data through a view that you have control over, and alias the column names to make them shorter, you can often work around this issue to some extent.

                      • 8. Re: Weird ODBC error when accessing MySQL table via ESS
                        beverly

                        Well! I don't have large sets of data to return. Its one of those warnings I've been espousing since ESS came to FileMaker...

                         

                        Use of a "view" is the correct way to limit the data from SQL to start with.

                         

                        This is also how I get to ESS tables (views or not) - start from any layout (blank, even) that is NOT an ESS shadow table occurrence (manually or scripted):

                         

                        1. Enter Find Mode (yes, that's right, go to find mode first on the blank layout!)

                        2. Go to Layout (whichever layout has an ESS shadow table and has been set up to show Form View ONLY and the minimum number of fields)

                        3. enter your find criteria that would limit greatly the SQL data

                        4. Perform Find

                        5. you are already in Form View - if the set of data is sufficiently small, you may safely navigate to a List or Table View, if desired.

                        6. what you can do from there: insert records, update records or delete records depends upon the permissions you have on the SQL table through ODBC

                         

                        YMMV, of course! ESS was NEVER EVER intended to be a front-end to SQL, so tread carefully.

                         

                        Beverly

                        • 9. Re: Weird ODBC error when accessing MySQL table via ESS
                          LSNOVER

                          Beverly,

                           

                          Just to be clear, I'm not talking about large data sets in the scope of NUMBERs of records.  That's actually not a limitation of ESS (except from a performance standpoint).  This problem is the result of each record being too big.  Ie. lots of fields in the table or large text fields in the data, that would exceed the 16 to 32K limit.   Think of it as the data being too wide vs. too long. ;-)

                           

                          If your willing to carefully construct your SQL Views and security, you can use Filemaker as a "front-end" to SQL with certain inherent limitations.  It is much more work then simply utilizing the ESS Feature set, you must be able to make design changes and control the data on the backend, in order to limit the amount and types of data that are exposed to the Filemaker Application at any point in your process.  There are some limitations that are difficult or impossible to work around with ESS as it is currently implemented.  But there are certainly advantages to being able to work with SQL data extensively in the scope of the Filemaker interface as well.

                           

                          Cheers!

                          Lee

                          • 10. Re: Weird ODBC error when accessing MySQL table via ESS
                            jormond

                            Curious, where is the actual limitation coming from?  Is it a limitation of the driver?  Or the ESS feature in FileMaker? 

                             

                            I was going to start some testing this week, but any jumpstart would help.  lol

                            • 11. Re: Weird ODBC error when accessing MySQL table via ESS
                              LSNOVER

                              Hi Joshua:

                               

                              It's a combination of the driver and Filemaker's implementation.  I hope we may some improvements in this area in future updates to Filemaker, but part of the problem is out of Filemaker's control and does lie with the drivers.  In the mean time, you just have to be cognizant that it's there.  The size of the record cache is somewhat dependent on what flavor or SQL you are using and the drivers.  If you have tables with lots of fields, try to break them up into views that eliminate any fields that are not necesarry for a given area of functionality, and try to keep the field names short in the view.  If you can't create views, then you can delete unecessary fields after adding the Shadow table to the interface.  The problem with this is, every time you add new fields, you will have to re-sync the table in Filemaker, which brings all the fields back.  You then have to carefully delete the ones you don't need again.  (Again this has no bearing on the actual table in SQL, just what is accessible to Filemaker). 

                               

                              As Beverly alluded, FM is not really a high powered SQL front end, so the trick is to keep the size of the data that Filemaker has to deal with lean and mean (both in record count and in the size of each record).  With careful design and control of your views, you can put together some impressive functionality in Filemaker, but it takes a bit of work.  Be prepared to hit some blind alleys and don't let frustration get the best of you.  ;-)

                               

                              Cheers!

                              Lee

                              • 12. Re: Weird ODBC error when accessing MySQL table via ESS
                                beverly

                                And of course... This level of SQL server control means you are the administrator or on REALLY good terms with whomever -> the DBA.

                                 

                                So take all the pearls of wisdom from those of us with a foot in both worlds!

                                 

                                -- sent from my iPhone4 --

                                Beverly Voth

                                --

                                • 13. Re: Weird ODBC error when accessing MySQL table via ESS
                                  greglane

                                  As Lee mentioned, the ODBC driver is at least part of the issue. I once tested ESS access to a MySQL field with over a million characters. It worked, albeit slowly. I'd suggest systematically testing the limits in your specific environment (operating system, SQL database, storage engine, driver, and version of FileMaker).

                                   

                                  FWIW, I did some testing with the Execute SQL script step a few years ago to determine the length limits for SQL queries with different versions of FileMaker and various drivers. This testing was not at all related to ESS. The limits I found were with the Execute SQL script step and when importing from an ODBC data source via FileMaker Pro. You can read more about these limits here:

                                   

                                  http://www.skeletonkey.com/FileMaker_SQL_Length_Limits

                                  • 14. Re: Weird ODBC error when accessing MySQL table via ESS
                                    jormond

                                    I have used ESS with FileMaker quite extensively.  I'm always interested in the tests and results.  I'm not a big fan of surprises.

                                     

                                    Thanks for the link Greg.  Always love reading your stuff.

                                     

                                    Appreciate the input Lee and Bev.  Always good to hear your thoughts.  In this case, I am the administrator.  Fortunately, I haven't run into any limits yet that prevent me from getting done what I need.  So that's good.  But I've never been a person that trusts theory, I want proof. lol