9 Replies Latest reply on Aug 15, 2016 11:23 AM by beverly

    When ExecuteSQL?

    eric

      Someone asked me a question like this,

      "When do you use ExecuteSQL?"

      "(and when don't you?)"

       

      My incomplete answer is below, but please feel to add to it.

      (There are whole user group meetings, DevCon sessions, and blogs on this topic.)

      FileMaker 12 SQL: Crafting Structural Beauty | beezwax > blog

      Re-Thinking is the new Rebuilding | beezwax > blog @pengu

        • 1. Re: When ExecuteSQL?
          eric

          For me,

          I consider ExecuteSQL in places where I dread adding a table instance on the relationship graph.

          I don't use ExecuteSQL as a replacement anywhere I don't dread simply adding a table instance on the relationship graph.

          ExecuteSQL doesn't perform as well as native FileMaker and it's potentially brittle.

          I prefer to use it as an auxiliary utility, especially where I loathe adding relationships to the graph and other baggage to the file.

           

          For example:

          I display user ID numbers in several places to indicate who created or last modified a field. Some of these are my fields, but most are coming from PeopleSoft. I have a table called PS_NAMES that could translate all these ID numbers to names, but with 50 tables, having two ID numbers each, that would take a hundred instances of PS_NAMES to translate all those IDs.

          Or I could use ExecuteSQL.

          For this purpose I use a custom function called RequestSQL, which uses ExecuteSQL:

           

          FileMaker Custom Function: RequestSQL ( fieldDesired ; fieldKey ; valueKey )

           

          I could make unstored calculations out of this, but that's still too much work; so I decide just to add tooltips to the fields. Now I simply have a calculation that looks like this:

           

          RequestSQL ( PS_NAMES::NAME ; PS_NAMES::EMPLID ; Self )

           

          Or, based on that calculation, another custom function that looks like this:

           

          RequestName (Self)

           

          Then all I have to is copy and paste that function to every field where they want a quick lookup of a user's name.

          By the way, is there a solution this easy that will work in FileMaker Go?

          • 2. Re: When ExecuteSQL?
            philmodjunk

            Good response Eric

             

            One of the circumstances that tilt me in favor of executeSQL is when I am updating a "live" database. With this function, I can often get the job done via a script without having to go in and modify the data model--something I normally do not do unless I've taken the files down for their weekly maintenance upgrade. I might later replace it with a standard FM relationship based method during such a "down time"--especially if I don't like the performance that I'm seeing.

             

            But, plug, when I set up ExecuteSQL these days, I don't do it in a "brittle" fashion. How I do this and without quadrupling the amount of time to create the query is something that I have in the plans for my October DigFM presentation.

            1 of 1 people found this helpful
            • 4. Re: When ExecuteSQL?
              FileKraft

              if obviously your query matches a primary key another parameter could be added to append "FETCH FIRST ROW ONLY" to the SQL statement within your custom function ..

              • 5. Re: When ExecuteSQL?
                Benjamin Fehr

                Performance might be a issue. When and under which circumstances is executeSQL faster than a TO based calculation?

                You will get different answers for different kind of data consolidation or even when executed as PSOS.

                • 6. Re: When ExecuteSQL?
                  fmpdude

                  Do yourself a HUGE favor and don't try to get your SQL working in the brain-dead Data Viewer with its incessant "?" unhelpful messages and no code complete, etc. Hard to believe this tool hasn't improved at all.


                  Check out an external tool like RazorSQL. It connects to your live FMP database and is a relative joy to use. Get SQL Query statement complete, ACTUAL ERROR MESSAGES, and regular SELECT, UPDATE, DELETE, INSERT syntax. RazorSQL works with just about every other database out there, too.  There are other tools out there as well that do the same thing.

                   

                  ------

                   

                  Once you get your SQL working....THEN put the SQL into an ExecuteSQL statement and modify the syntax as necessary for replaceable parameters and such so it works in FM.

                   

                  But, as others have said, ExecuteSQL is SLOW compared to a native FMP Find. I avoid ExecuteSQL whenever I can do a native Find. For whatever reason, ExecuteSQL does not seem well optimized or doesn't take advantage of existing indexes or something. If you have a script that will run overnight or something like that, or an occasional SQL statement, that's fine. But for querying against large databases ExecuteSQL a no-go for me (note: some ExeucteSQL queries may be perfectly acceptable so you really, really, need to benchmark queries before putting (or using) ExecuteSQL).

                   

                  Finally, it's such a pain to get SQL working in the data viewer, I need to always use an external tool for any non-basic query. Because of this extra step, I avoid ExecuteSQL when I can.

                   

                  I'm a SQL guy and have used SQL extensively for over 20 years....just not often in FMP. I agree with what others said about ExecuteSQL being a good way to avoid creating yet another relationship in the relationship graph. ExecuteSQL is great for setting up a chart, for example.

                   

                  HOPE THIS HELPS.

                  3 of 3 people found this helpful
                  • 7. Re: When ExecuteSQL?
                    Benjamin Fehr

                    Check out an external tool like RazorSQL.

                    I will try ($100.-).

                    https://razorsql.com/

                     

                    Some more tips on external SQL-Tools?

                    • 8. Re: When ExecuteSQL?
                      fmpdude

                      Just that SQL is the thing to know as most databases, relational, anyway, use it.


                      If you do software development (.NET/Java) many environments also support database connections right in the tool so you never have to leave it. Do SQL with query assist (fields, table pop-ups), error handling, etc.

                       

                      Plus, using an external tool you get all SQL (UPDATE, INSERT, and DELETE, not just SELECT)!

                       

                      ---

                      There are some good plug-ins too, but I haven't used those.

                       

                      beverly can give you some tips with those SQL plug-ins.

                       

                      HOPE THIS HELPS.

                      • 9. Re: When ExecuteSQL?
                        beverly

                        search on the FMI site:

                        Search | Made for FileMaker

                         

                        search on Google:

                        'filemaker sql plugin'