7 Replies Latest reply on Mar 3, 2016 11:00 AM by benhirsch

    Select ODBC Data Source window always empty

    PaulHuffman

      Title

      Select ODBC Data Source window always empty

      Post

      I am using FM Pro 13 on a Windows 7 64 bit PC. I have several test ODBC Data Sources to local and remote MySQL databases and made ODBC connections to a local FM database "Contacts" from the FileMaker Basics training.  I can import records from these data sources.  However, I have been unable to set up ESS or get to these databases through Manage External Sources. When I go to Manage External Data Sources>New>ODBC and click the Specify button at the DSN line, I just get a blank window Select ODBC Data Sources as if none of the machine data sources are being picked up.  Same happens when I try to add a ODBC source through Manage Database>Relationships>Specify Table> Add an ODBC Data Source.

      I'm using the 32 bit MySQL 5.2.4 driver managed by odbcad32.exe as specified in the FM docs, and my local MySQL database is 5.6 Community release as specified by the docs, although testing with Import Records showed that the MySQL version was not as important as using a 32 bit MySQL ODBC driver. 

      My MySQL and my FileMaker ODBC data sources work with MS Access 2010.

      FM technical support suggested that my install of FM was bad, so I set up a FM Pro 13 trial version on different Windows 7 64 bit machine, and got the same empty Data Source windows. 

      FM technical support suggested that I might be having network issues.  That's why I set up local MySQL 5.6 community installs and populated test MySQL databases to isolate this possibility.  Same results going to localhost or 127.0.0.1. 

      What am I supposed to be seeing in the Data Source window?

       

       

      CaptureDataSource.JPG

        • 1. Re: Select ODBC Data Source window always empty
          erawson

          ESS requires your ODBC data sources to be System DSNs

          • 2. Re: Select ODBC Data Source window always empty
            PaulHuffman

            Making System DSNs got me a little farther, but now the Specify Table window is blank.  I ran odbcad32.exe as administrator, created a ODBC connection to a localhost test database, book_sc on MySQL 5.6 community and made a connection to a remote MySQL database bor running MySQL 5.1.56, both using the 32 bit 5.2.4 driver.  Both connections tested OK.  I was able to add both connections as External Data Sources.  But at Manage Database> Relationships> Add a table> Data Source,  I can add the Data Source by choosing one of my DSNs,  but the Specify Table window stays blank and OK is greyed out.   The screen capture shows a MySQL management tool, Navicat, and the tables that should be available in book_sc. Tried both specifying a user and password at the connection and leaving it blank. 

            I'm trying to follow the ESS instructions found in https://www.filemaker.com/downloads/documentation/techbrief_intro_ess.pdf but this document describes older versions of the MySQL drivers and an older version of FileMaker.

             

            • 3. Re: Select ODBC Data Source window always empty
              benhirsch

              I've already written up a similar scenario and situation but I want to say ditto on this problem. I can't seem to get the table/view list to populate. And the application is actually working against the MySQL database reading and writing. I just can't pull up the object list in the relationship graph.  Windows is the common denominator ... FMP 12, 13, 14,  Windows 7, 8 and 2012 R2 Server. When connecting with mac desktop or through a mac server, it works fine - the same app.

               

              Hope there's a solution out there.  Ben

              • 4. Re: Select ODBC Data Source window always empty
                benhirsch

                Looks like our backend Linux based MySQL version is 5.1.xx.  We have a plan to upgrade and test to see if that is the cause of the table listing coming back blank. Filemaker recommends at least 5.6.xx and Linux version is currently at 5.7.11.

                • 5. Re: Select ODBC Data Source window always empty
                  actualjon

                  I don't think the MySQL version is the problem.  There is very little (if any) difference between MySQL versions regarding core functionality like listing tables.  When I troubleshoot problems like this, the cause is usually either a) the MySQL user account doesn't have permission to list tables, or b) you're not connected to the MySQL database you think you are. 

                   

                  ESS looks for the DSN on the machine where the FM database is hosted.  You may have configured your local DSN to point to a development server, and your server DSN to point to production.  When you open your FM database locally, you will be looking at your development MySQL database, and may not see the tables you were expecting.

                   

                  User account permissions, FM database location, and DSN settings are the first I would look at.

                   

                  Jonathan Monroe

                  Actual Technologies - ODBC for OS X

                  • 6. Re: Select ODBC Data Source window always empty
                    benhirsch

                    Well, this same application runs fine on a Mac Based filemaker Server implementation.  When I say fine I mean the business functions all operate normally with backend ESS mySQL data and server. The Manage Database functionality/relationship view (list tables/views) to create a new object in the Filemaker database schema lists out all authorized objects.  The correct System DSN is setup and referenced.  The only variation in all of this (between the mac server FMS implementation and the windows 2012 r2 implementation is the ODBC connection to MySQL).  Whether in FMS mode (windows 2012) or standalone client (windows 8) or separate Windows machines including a virtual machine VMWare implementation (windows 7), the same symptom manifests with filemaker 14. The list of tables/views is blank in the relationship view of the database.

                     

                    I bring up the MySQL 5.1 version as a matter of verification as Filemaker states they support 5.6 or later.  Not sure what problems arise from earlier versions or they just failed to certify earlier versions.  We are prepared to upgrade the backend MySQL server to see if that clears the problem but for now this feels like a Filemaker (on windows and odbc) bug).

                    • 7. Re: Select ODBC Data Source window always empty
                      benhirsch

                      I think I figured it out.  The System DSN sets the MySQL database name in the configuration.  In Edit Data Source I defined a filter on the list of tables/views using Schema Name as that database name ... this didn't seem to be a problem on the Mac side...I'm suspecting it ignored the value placed in Schema Name: in the Edit Data Source dialog. But on Windows, it used that Schema name to manage the list of tables and views which according to the documentation from filemaker has something to do with the name associated with the table object ... not sure what that is. Anyway, I remove the Schema name filter and the list of tables/views appears.  (Mac ignored this, Windows FM evidently not).

                       

                      On another note, the filter by Type ... if you check Tables, it lists the views as well. If you check Views and not Tables, only the views are listed.  A little quirky in my opinion but I learned something today.  Leave Filter Tables blank unless you are using SQL Server?  The MySQL version was not the problem as you stated!  And Thank you!!!