AnsweredAssumed Answered

ODBC error - MySQL - Define Relationships

Question asked by sgastevep on Dec 24, 2011
Latest reply on Dec 26, 2011 by 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

Outcomes