3 Replies Latest reply on Dec 26, 2011 5:37 PM by sgastevep

    ODBC error - MySQL - Define Relationships

    sgastevep

      Summary

      ODBC error - MySQL - Define Relationships

      Product

      FileMaker Pro

      Version

      11.0v4

      Operating system version

      Mac OS X 10.7.2

      Description of the issue

      When trying to define a relationship to an external data source (ODBC - MySQL) I receive an error about improper syntax in the MySQL command.

      In turning on the MySQL query logging, I see the malformed query:

      SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'mydb

      It needs a closing ' character on the database name.

      Steps to reproduce the problem

      Try to define a relationship with an ODBC MySQL table.  You get a list of tables, but get an error when you select one.

      Expected result

      I don't know, it has never worked, although I would expect it to add the table to the view so I could define relationships. :)

      Actual result

      error message.

      Exact text of any error message(s) that appear

      ODBC Error: [MySQL][ODBC 5.1 Driver][mysqld-5.5.18]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''mydb' at line 1

      Workaround

      None, but here are the queries issued by FileMaker:

      (This one works - gets list of table names)
      SET NAMES utf8
      SET character_set_results = NULL
      SET SQL_AUTO_IS_NULL = 0
      SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' )

      (this is when you select a table, it fails with the error above)
      SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'mydb
      set @@sql_select_limit=DEFAULT
      SELECT COUNT(*) FROM mydb.mytable
      SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'mydb

        • 1. Re: ODBC error - MySQL - Define Relationships
          sgastevep

          As a side note, the ODBC driver/DSN definition is working OK as I can do an "File->Open" and give it a SELECT query to bring in data rows from the MySQL databse. I can also use Excel to get data from this ODBC source.

          • 2. Re: ODBC error - MySQL - Define Relationships
            sgastevep

            Still not an "ANSWER" per-se, but I also noticed that the FIle-Open and Import steps apparently are "broken" in that they show the table names but not any columns.  Again, hand-entering an SQL works here, but not for the relationship graph where it has to construct these statements itself.

            One more data point, though...  I installed an SQLite3 ODBC Connector from here:  http://www.ch-werner.de/sqliteodbc/

            It is not supported in the relationship graph, but when I use that ODBC connector in File->Open or Import I do see table names and can use the tools in FileMaker to build the SQL statement instead of having to type it all by hand.

            • 3. Re: ODBC error - MySQL - Define Relationships
              sgastevep

              Ok, seems this is, after all, a bug in the MySQL connector.  I had tried 5.1.8, then compiled 5.1.9 myself and yet got a different error.  Although other programs are using it fine, apparently something with how FileMaker is using it causes problems.  I just downloaded the last 3.51 series connector for the Mac and it seems to be working.