1 2 3 Previous Next 30 Replies Latest reply on Apr 20, 2016 9:16 AM by Jean-Francois

    Best practice: executeSQL or dummy field

    RussW

      As I continue to modify my older solutions to work on mobile devices, and as I try to remove off-screen windows in order to fetch data from unrelated tables, I have learned to use the executeSQL command. But there are some unrelated tables that have a host of fields I need to grab and move into temporary variables and constructing a SQL query is taking a lot of typing that is hard to read and parsing the comma delimited rows, well, that would be really awful.

       

      The shortcut as someone here showed me was using a dummy filed in both tables to make a one to many join. It is quick and easy and doesn't really waste any space, as if that really matters anymore.

       

      So, is there a "best practice" in these sorts of situations or is it just programmer's choice? I want this code to be as elegant as possible and be able to be picked up by a new programmer when I am too old to deal with it.

        • 1. Re: Best practice: executeSQL or dummy field
          bigtom

          So are you saying you are comparing ExecuteSQL to parsing a bunch of data into various fields from one calculated field?

           

          How many fields do you have to load into variables?

           

          I do not mind ExecuteSQL. Once you have a decent statement it is easy to copy and paste and only change a few things in each query as you need them. I prefer ExecuteSQL myself. It really depends on your situation.

          • 2. Re: Best practice: executeSQL or dummy field
            RussW

            It varies but as many as 8 to a dozen variables are kept global that I initialize from values stored in a table that, for this purpose, we'll call InitializationValues. It's stuff that the user may set up once when first using the app (e.g. default email address, SMTP server info) as well as "last state" values (e.g. last record accessed, print or email preference, last report date).

             

            If I use a dummy variable, I can just do a bunch of Set Variable statements to assign the values from the InitializationValues table to the respective globals. It, too, is a lot of typing but it is very readable.

             

            When I do the ExecuteSQL, it seems to be more difficult. Plus, the SQL command fails when I do as you say you do, just make some edits to the original script. For instance, When I say:

             

                 ExecuteSQL( "SELECT * FROM InitializationValues";"";"")

             

            it works. But as soon as I replace the asterisk with a set of comma delimited column names, it fails. When I add the variables back one at a time and test it (very time consuming), it's working. So far I am up to 4 fields.

             

            Not to confuse the issue, I am using $Delim as the field separator in the SQL query where $Delim = CR/LF = char(10) & Char(13). I'm hoping that I can then parse the values out by GetValue from the list and specifying the row number. When I print out the result of the query, it's printing as a list. Whether it will evaluate as a list I'm about to find out.

             

            Finally, I am not using the LET statement as I see so many others are doing. I'm not a big fan of LET statements unless I absolutely cannot avoid them. Could this be my problem? I'm just doing this:

             

                 Set Variable ($$result; ExecuteSQL( "SELECT * FROM InitializationValues";"";"") )

            • 3. Re: Best practice: executeSQL or dummy field
              jbrown

              If your ExecuteSQL statement is failing, you can try the custom function that debugs it. You can get it in the file found at: http://www.soliantconsulting.com/blog/2013/11/filemaker-query-builder

               

              The link to the CF is not working, but I believe the file contains it.

               

              That post shows a great query builder for practice.

               

              ExecuteSQL takes some getting used to, but I use it now all the time. In the beginning it took me a few minutes to write the code; now it is quick and I can't live without it☺.  I recommend you take the time to get proficient at it.

               

              You should be able to replace the * with a list of fields you want from that table.   "ExecuteSQL(" SELECT Field1, Field2, Field3 FROM SOMETABLE";"¶";"")

               

              The 2nd parameter is the field separator. You can simply type the paragraph symbol "¶" in between the quotation marks and it will create a list for you:  Example:  Result = "Jeremy¶Denver¶Left handed¶ jbrown@something.com". However if you're pulling multiple records, this won't work.  In this case, some other character is used to separate fields in a row (I use the "|") and then take one row at a time and substitute the | for the ¶. Then use get value to get the individual values. This is a brief, rough, summary of the Virtual List technique developed by Bruce Robertson.

               

              Curious, why do you not like the LET statement? In my early days of developing I hardly used it. Now, its another function I can't live with out. Though I can get carried away with it. Sometimes it is best to put each step of a calculation into variables so that those will show up on the data viewer. If you're using local variables inside the let statement you won't see those variables, those partial calculations.

              • 4. Re: Best practice: executeSQL or dummy field
                bigtom

                I have had better results with doing individual queries for each global variable. Still very fast. For example:

                 

                     Set Variable ($$result; ExecuteSQL( "SELECT fieldname FROM InitializationValues WHERE something = ?";"";""; parameter) )

                 

                Using the ? and parameters has always given me better result. Sometimes the query fails otherwise. The WHERE parameter would be your user key or something similar. This may help you with your queries in general.

                 

                I do this for 8 variables in a popover in portal rows to grab data from an unrelated table in a UI file dependent on another field in a data file and display the merge field in a popover on a layout. Works as fast as people can push the buttons.

                 

                Just copy and paste the query and change the field name or the parameters depending on how your other table is setup. No need for parsing this way. One field to one variable.

                • 5. Re: Best practice: executeSQL or dummy field
                  beverly

                  Not that this answers your question, but you might grab the pdf & sample files here on ExecuteSQL() to help understand what works and what doesn't:

                  The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

                  (don't let the "12" deter you, it's all valid for 13 & 14, too!)

                   

                  to give my opinion on your 'OR' dilemma:

                  If you have many values returned (i.e. many columns), you might look at the example given by the FTS (FileMaker Training Series Advanced) using ExecuteSQL and Virtual List to make a "temporary table" of these values.

                   

                  Database Skills, FileMaker Pro Training | FileMaker

                   

                  But also, your temporary relationship does give you a way to link normally "unrelated" tables. So, both methods are viable. It gets unwieldy if you have so many temporary relationships, that your graph looks like spaghetti. Try both methods and see what seems to be easiest, what seems to be fastest and what works for your situation.

                   

                  beverly

                  • 6. Re: Best practice: executeSQL or dummy field
                    Mike_Mitchell

                    Your problem may be coming from using forbidden characters or reserved words in your field names. Here's a handy reference:

                     

                    List of SQL reserved words | Drupal.org

                    • 7. Re: Best practice: executeSQL or dummy field
                      erolst

                      RussW wrote:

                      Finally, I am not using the LET statement as I see so many others are doing. I'm not a big fan of LET statements unless I absolutely cannot avoid them. Could this be my problem?

                       

                      You're totally right, why use Let()?

                       

                      Everything you can do with it can also be done with lots of redundancy & repetition, deeply nested functions and a host of utility fields …

                      • 8. Re: Best practice: executeSQL or dummy field
                        wimdecorte

                        erolst wrote:

                         

                        RussW wrote:

                        Finally, I am not using the LET statement as I see so many others are doing. I'm not a big fan of LET statements unless I absolutely cannot avoid them. Could this be my problem?

                         

                        You're totally right, why use Let()?

                         

                        Everything you can do with it can also be done with lots of redundancy & repetition, deeply nested functions and a host of utility fields …

                         

                        Russ, in case you missed, erolst was being sarcastic

                         

                        There is absolutely no reason to fear or avoid Let().   Why are you shying away from it?

                        • 9. Re: Best practice: executeSQL or dummy field
                          erolst

                          wimdecorte wrote:

                          erolst wrote:

                          Everything you can do with it can also be done with lots of redundancy & repetition, deeply nested functions and a host of utility fields …

                          Russ, in case you missed, erolst was being sarcastic

                           

                          Sarcasm indeed, but the things I listed are really how people coped in the pre-FM7 era to implement non-trivial calculations … once you grasp Let(), you'll never look back!

                          • 10. Re: Best practice: executeSQL or dummy field
                            RussW

                            Thanks to everyone. Jeremy, good suggestions. I only have one record int he table, but the substitute command is a clever way to get the parsing job done easily.

                             

                            BigTom: Won't popovers show up on the mobile device and be disturbing? The whole point of off screen windows is to do the dirty behind the scenes work without letting the user know. If I have flashing popovers, I'm afraid that the user will react badly. Am I missing something?

                             

                            Beverly, I did read your paper on ExecuteSQL. One of the first things I did before attempting to use it. Excellent by the way. I'll take advantage of the link you posted about training.

                             

                            Mike, using reserved words in the script was one of my first mistakes! I wasted several hours before I figured out the "group" was one of them. So, no, all the reserved words have been purged.

                             

                            As for the Let statement (and I am definitely giving away my age here), I think my aversion to the command goes back to the early days of coding in compiled languages. Let can be very difficult to read and nested lets impossible. Unless you work with the code frequently (which I rarely do), it can be really confusing as to what you intended to do in the first place. And passing it to others even harder. And just because the let statement can be so powerful, it's easy to get too clever and really make the code difficult to decipher. Back then when every byte counted, there wasn't much room for commenting and documenting the code. Sort of the same issue when programming in C vs something like VB. Nested C statements can be your undoing unless you are good at documenting your code. VB is much more readable should you get lazy with your comments.

                            • 11. Re: Best practice: executeSQL or dummy field
                              wimdecorte

                              RussW wrote:

                               

                              As for the Let statement (and I am definitely giving away my age here), I think my aversion to the command goes back to the early days of coding in compiled languages. Let can be very difficult to read and nested lets impossible.

                               

                              I agree that nested Let() calls are confusing but typically you'd find only one per calc.  Here's one that I use all the time to construct an ExecuteSQL() call and its only purpose is to make things readable:

                               

                               

                              Let(

                              [

                              _field = _FieldRef_getField ( <something> ; True ) ;

                              _table = _FieldRef_getTO ( <something> ; True ) ;

                              _matchfield = _FieldRef_getField ( <something> ; True ) ;

                               

                              _sql =

                              "SELECT " & _field &

                              " FROM " & _table &

                              " WHERE " & _matchfield & " = ?"

                               

                              ];

                               

                              ExecuteSQL( _sql ; "" ; "" ; something )

                               

                              )

                              • 12. Re: Best practice: executeSQL or dummy field
                                bigtom

                                I was mostly commenting on the speed at which using ExecuteSQL works. The smaller the tables the faster it seems to work.  Popovers are very appropriate in my use case to display data the users need.

                                 

                                With ExecuteSQL and global fields in this case you essentially do the "dirty work" in a script on the original window and layout without anyone noticing anything. Sorry it was not clear before.  No off screen windows needed. You may also consider replacing the global fields with global variables altogether.

                                 

                                FMGo does not do off screen windows. I think the best you can hope for is a frozen layout.

                                 

                                About the speed, if I am in one popover viewing data and tap the button in the next row...FM does 8 SQL queries across to a file on another server sets 8 global variables and refreshes 8 objects before the new popover springs to life. That is much faster than most layout changes in FMGo these days. Certainly faster than two layout changes or dealing with more Windows. I think it is because the queried tables are very narrow and have only 5-20 records each.

                                • 13. Re: Best practice: executeSQL or dummy field
                                  wimdecorte

                                  bigtom wrote:

                                   

                                  I was mostly commenting on the speed at which using ExecuteSQL works. The smaller the tables the faster it seems to work.

                                   

                                   

                                  That's not the case though... as I have shown in my devcon presentations.  Doing a straightforward SELECT on millions of records takes just a few milliseconds.  Same as doing the exact same query on a smaller table.

                                   

                                  The slowdown comes from:

                                  1- complexity added to the SQL statement (JOINs, SQL functions,...)

                                  2- or running the SQL query when you (as the one executing the query) have an open record on the target table (FMS sends you all of the data in the whole table so that the client can resolve the SQL statement - as opposed to the server doing the SQL query)

                                   

                                  In both of these scenarios the execution speed will be linear (sometimes even exponential) with the size of the table.

                                  But only in those two scenarios.  So I want to be clear that the size of the table itself does not automatically mean that ExecuteSQL() calls are going to be slower the bigger the table gets.  If your SQL statement is complex, it is going to be slow even on a small table.  If you have an open record on a table with just 10,000 records you will get a noticeable delay.

                                   

                                  As to #2: it is the main reason why I do not use ExecuteSQL() calls in field calc definitions, tooltips, conditional formats, visibility calcs and so on: because you have no control over the open state of the record so you have no control over the FMP-to-FMS behavior that will decide if FMS is going to send you all the data...

                                  The slowdown is not in the actual SQL query but in waiting for the client to get all of the data for all of the records from FMS,

                                  • 14. Re: Best practice: executeSQL or dummy field
                                    beverly

                                    And this is true in "big-guns" SQL servers. The more complex the query, the slower the execution. The more rows and columns can also be a factor (though less-so). Don't just blame FMP here...

                                     

                                    beverly

                                    1 2 3 Previous Next