9 Replies Latest reply on Apr 18, 2012 7:20 AM by beverly

    ExecuteSQL Function Note (v12_

    LSNOVER

      FYI, in testing the ExecuteSQL Function, when doing basic summary queries on an ESS Table, it seems that Filemaker may be utilizing the SQL database indexes. Need to do some more testing to verify. This will make this funciton extremely useful in version 12.

       

      Preliminary kudos to FMI on this!

        • 1. Re: ExecuteSQL Function Note (v12_
          LSNOVER

          Have to take back my Kudos.  My apologies for the premature optimism.  My test set was being inadvertantly filtered and very small.  Attempting my test on a larger SQL data set shows that the Execute SQL function is NOT running against the base ESS SQL Database, but is actually loading the data into Filemaker's cache to be evaluated first.  This causes slow performance at best and a full crash of Filemaker at worst.  :-(

          • 2. Re: ExecuteSQL Function Note (v12_
            rhlilienkamp

            The ExecuteSQL function is not for ESS, but for using SQL on FMP tables.

            • 3. Re: ExecuteSQL Function Note (v12_
              LSNOVER

              There is nothing that prevents it from being used with ESS data, aside from the fact that the performance is abysmal.  This is something that FMI could remedy with a bit of work, and I sincerely hope that they will in the future.   In some of my testing, using the function on a large SQL Database actually causes Filemaker to crash and corrupt the Filemaker database.  Regardsless of the functions intent, this behavior is not acceptable.

              • 4. Re: ExecuteSQL Function Note (v12_
                Oliver_Reid

                Execute Sql translates the SQL into an internal Filemaker calc engine query. Then if applied to ESS data, it turns that back into SQL. Not the way to go.

                 

                I you want to apply a SQL query to ESS tables, create a "view" in the data source. Much, Much, faster than doing the filtering and joining inside FM.

                • 5. Re: ExecuteSQL Function Note (v12_
                  LSNOVER

                  Hi Oliver:

                   

                  I understand what it's doing, but there's nothing in Filemaker that prevents it.  Also, if you "abuse" the function on a SQL database, you can catastrophically crash Filemaker (will be testing server soon too).  This is not the friendly Filemaker way.  They need to retool this function to work properly/efficiently with ESS tables.  It would be a God send if they did.  In the mean time, use with ESS with EXTREME caution and prejudice.  ;-)

                  • 6. Re: ExecuteSQL Function Note (v12_
                    Oliver_Reid

                    Lee:

                     

                    Agree

                     

                    It would be exccllent to create "views" of data with SQL statemenst that would have TO appearin the RG. Yiu can sumulate with calc fields or placment fo a related field to some extent but one think I can't do is create an outer join without laborious scripting.

                     

                    I am wondering if in fact the new file structure does in factuse a real SQL format under the hood  - like Bento does, and the UI emulates the old filemaker db tools.

                    • 7. Re: ExecuteSQL Function Note (v12_
                      beverly

                      Oliver, I don't plan to use the ExecuteSQL() function with ESS. I can use the Execute SQL SCRIPT STEP and IMPORT SCRIPT STEP to also access ODBC sources. I can make the SQL call I want. Yes, these do have a "disconnect" from the external sources that you don't have with ESS. But they are useful!

                       

                      I also agree that VIEWS can be made in the SQL source to help get the "found set" what you want from ESS without having to jump through hoops on FileMaker's side. Keep in mind that you or your IT DBA must have access to the SQL  database to create the views. That's a "stopper" for many people!

                       

                      But I do plan to use the ExecuteSQL() FUNCTION within FileMaker to cut down those relationships, create UNIONS, make use of CONSTANTS (without creating the fields), and other useful things that one can do with the SELECT statement.

                       

                      Think of filtered portals or the virtual lists one can do using the ExecuteSQL() function...

                       

                      Beverly

                       

                      Lee:

                      Agree

                      It would be exccllent to create "views" of data with SQL statemenst that would have TO appearin the RG. Yiu can sumulate with calc fields or placment fo a related field to some extent but one think I can't do is create an outer join without laborious scripting.

                      I am wondering if in fact the new file structure does in factuse a real SQL format under the hood  - like Bento does, and the UI emulates the old filemaker db tools.

                       

                      • 8. Re: ExecuteSQL Function Note (v12_
                        LSNOVER

                        Hi Bev:

                         

                        The Execute SQL script step is actually way more powerful than the function.  The only problem is you have to setup an ODBC connection to the server on each workstation, which is a real PITA.   If FMI would allow this to run through the server's ESS ODBC Connection, this would be fantastic.  Will keep our fingers crossed for the next go round.

                         

                        Cheers!

                        Lee

                        • 9. Re: ExecuteSQL Function Note (v12_
                          beverly

                          Oh, I AGREE! If we could only use the SQL statements (all of them) AND the ESS... whoo hooo! Oh yeah and IN fm to fm tables....

                           

                          Thanks, Lee.

                          Beverly

                           

                          Hi Bev:

                          The Execute SQL script step is actually way more powerful than the function.  The only problem is you have to setup an ODBC connection to the server on each workstation, which is a real PITA.   If FMI would allow this to run through the server's ESS ODBC Connection, this would be fantastic.  Will keep our fingers crossed for the next go round.

                          Cheers!

                          Lee