5 Replies Latest reply on Sep 9, 2015 8:49 PM by clivegreen

    ExecuteSQL

    clivegreen

      I have a very strange problem using the ExecuteSQL() function. I would really appreciate any advice you may want to offer!

       

      Some background:

      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.

       

      Situation:

      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.

       

      The Problem:

      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!!!