3 Replies Latest reply on Apr 15, 2010 6:17 AM by mrvodka

    Simple ODBC SQL query:  Where on Earth is the result?

    gczychi

      Title

      Simple ODBC SQL query:  Where on Earth is the result?

      Post

      Hi,

       

      I have just got FM Pro Advanced 11 (Mac) in order to exchange some data with a remote MySQL server and I have searched for hours now, how to fetch the result of a simple MySQL query, say «select * from table where id=1».

       

      Using a FileMaker script, I can execute this query, but where in the heavens is the result? How can I get it into a field or into a calculation or into another script or into a function?

       

      The only thing that I have found is that you can get the result using an AppleScript, issuing a shell command, and so on and so forth (www.macintouch.com/readerreports/filemaker11). Come on, this cannot be true, can it?

       

      Thanks so much for any insight in this matter.

       

      Gary

       

      P.S.: I understand that I could import new records or make a new database altogether with the contents of the MySQL database. But this is not what I need. I just want to occasionally check, update some fields here or there — pretty easy stuff.

       

        • 1. Re: Simple ODBC SQL query:  Where on Earth is the result?
          solventerprises

          Execute SQL, I believe, doesn't return any SELECT results. You can insert, update or delete based on the examples in the help menu. So that's not what you are looking for.

           

          I think what you are looking for is an ESS table not a script step. So, first add your MySQL database to the system DNS. Then add the database to your FileMaker file by going to File > Manage > External Data Source. Then open Manage > Database and go to the Relationships and add a table occurrence for your MySQL table you want to pull data from. Since it now acts like a FileMaker Table Occurrence you can now script your query just like it was any other table and pull the results you are looking for.

          • 2. Re: Simple ODBC SQL query:  Where on Earth is the result?
            gczychi

            Hi,

             

            thanks for the clarification. This sums it up pretty well. If I want to or not, I'm forced to stick whatever FileMaker has to offer.

             

            I still can't imagine how can someone seriously consider to suppress the result from *the* most used sql command?

             

            Thank you very much,

             

            Gary

             

            • 3. Re: Simple ODBC SQL query:  Where on Earth is the result?
              mrvodka

              The hardest concept from the transition from the SQL world to FileMaker is not understanding that FileMaker is context sensitive. A layout is based on a table occurrence, which is based on a table. Results of a "found set" can be brought out via a layout. Again you must keep in mind that it is context sensitive.

               

              There is no direct results only per se as in the SQL world which just has a returned results with no attachments. If you can grasp this concept then Execute SQL not having SELECT should make sense.

               

              Now in FileMaker there is an option to Import via ODBC and import that into an existing or new table.