ESS requires your ODBC data sources to be System DSNs
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.
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
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.
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.
Actual Technologies - ODBC for OS X
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).
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!!!