I have a very strange problem using the ExecuteSQL() function. I would really appreciate any advice you may want to offer!
I am on a Mac running 10.10.5 Yosemite, with a legal copy of FMPA v14.
I have successfully connected to a MySQL database running on localhost (the MySQL root password has been setup OK).
I am using ActualTech's open source driver to make the MySQL database available to FileMaker.
To manage the MySQL database, I have a copy of Sequel Pro.
I have set up an external data source inside FileMaker, and connected without problems to several MySQL tables. I have FM layouts which allow me to directly edit the data inside my MySQL tables. And I have created some custom functions which use ExecuteSQL() to query data. Everything is working absolutely fine across the board ... except for one weird problem that I cannot solve for the life of me.
In MySQL, I have a language table with just 2 columns: languageID and languageCode.
Using Sequel Pro, I can perform this SQL query:
SELECT languageID FROM language WHERE languageCode = 'en-uk'
And it returns exactly the result I expect. However, using the same query inside an ExecuteSQL() statement within FMPA will return a "?".
What is weird is that I have other tables whose structure is identical, and other places where I am using ExecuteSQL() statements to do the same kind of thing. And these all work fine! Only the above query fails ... and only from inside FileMaker. I have no idea way.
Steps taken so far:
(1) Using Sequel Pro, I deleted the original language table, duplicated another table whose queries worked OK, renamed this table and its columns appropriately and then re-input the original column values;
(2) Deleted the entire external data source and then re-created it again in FileMaker, before re-creating the relationship graph, and repairing and checking all of the original layouts.
I am stumped on this one. Please put me out of my misery if you have any idea what I am doing wrong (or forgetting).
Many thanks in advance!!!