9 Replies Latest reply on Jul 14, 2012 5:29 PM by Malcolm

    Using ExecuteSQL and FM checkbox lists

    kmtenor

      In Filemaker, we have often leveraged the ability to have a checkbox field containing a list of parameters linked to a second table, and then displaying related records from the second table for all parameters chosen. In SQL, this is usually handled as follows:

       

      SELECT a.name, a.address

      FROM addresses a

      WHERE a.city in ('Austin','Houston')

       

      It turns out that ExecuteSQL can handle exactly the above query. However, I cannot find any way to get it to accept the "in" statement passed in as a parameter.

       

      I have tried everything I can think of:

       

      1. Send it in natively (which delimits the list with paragraph markers) - NOPE
      2. Send it in after using a "substitute" function, just replacing paragraph with comma (Substitute ( chklistField; ¶; ",")) - NADA
      3. Make the "substitute" a little fancier by enclosing all items in single quotes ("'" & Substitute ( chklistField; ¶; "','") & "'") - NOTHING

       

      So, thinking slightly outside the SQL box, I did come up with the following, which is ugly, but does work (and works well):

       

      Let ([

      $cityList= "'" & Substitute ( chkListField; ¶; "','") & "'";

       

      $$query = "SELECT a.name, a.address

      FROM addresses a

      WHERE a.city in (" & $runIDList & ")"

      ];

       

      ExecuteSQL($$query;"";"")

      )

       

      (I globalized the query in order to see it in the Data Viewer, since this runs as part of a script)

       

      Does anybody have a better way? Has anyone found a way to use the parameter passing to simply send a list in? Or should we bring this up to Filemaker as "Feedback"? It seems like lists should be modified appropriately to be incorporated into ExecuteSQL statements, or at least re-handled properly when they're seen a parameter to an "In" statement.

       

      Thanks.

       

      -Kevin

        • 1. Re: Using ExecuteSQL and FM checkbox lists
          fmpros

          Hi Kevin,

           

          So, boiled down, what you're looking for is a way to pass a list of values into the

          query properly quoted/delimited, right?

           

          Your "ugly" isn't so ugly.  You've taken a straight list and "prepped" it for the SQL

          function.  I do this all the time but I use SQLRunner plugin so the syntax is a little

          different.

           

          Question: what if one of the values in your list has a single quote in it : D'Angelo

          Your delimiting will fail then.  You would have to do a little more robust parsing of

          the list: "'" & Substitute ( $theList ; [ "'" ; "''" ] ; [ "¶" ; "', '" ] )...etc.... but of course that

          may or may not work depending on the value makeup....

           

          I also keep all my SQL statements as text in a table and abstract the call to SQL in case

          that day comes when I change plugins or go native with FMP.  Then at startup I load the

          SQL text into $$globals and make my call: SQL ( Evaluate ( $$NAME_KEY_OF_SQL_STRING ) ; <my delimiters> )

          A text string in the SQL table might look like this :

           

          "SELECT field1, field2 FROM theTABLE WHERE field3=" & epSQLQuote ( $theComparator )

           

          But my rules include that I prep things before sending it off to the SQL function so.....sorry, to finally

          get to the point, I don't think it unusual to do some prepping in a calc LET before calling SQL.

           

          For the list you might want to do a quick loop and "epSQLQuote" each list value so as to take care

          of the random single quotes.

           

          Loop

          Set Variable ( $newList ; $newList & epSQLQuote ( GetValue ( $originalList ; $aLoopCounter ) ) & "¶" )

          Do the increment thing and exit loop thing......

          EndLoop

           

          where epSQLQuote just happens to be the plugin function I use to manage SQL quotes.

           

           

          Best,

          William

          • 2. Re: Using ExecuteSQL and FM checkbox lists
            greglane

            Hi Kevin,

             

            I haven't found a "better" way than you suggested. Do be sure to escape any single quotes in your data, as William suggested. A city like Bois D'Arc would break your query.

             

            I have tried some other approaches, though none are necessarily better. One would be to create a recursive custom function that takes a SQL query and a list of values as parameters. Each iteration of the function could pop the top value off the list and execute the SQL statement with a single argument. The end result would be a combination of the results from each query.

             

            Another approach would be to build a semicolon separated set of values and a corresponding comma separated set of question marks. Then calculate the entire ExecuteSQL function as a string and pass it to an Evaluate function. The quoting can be a little tricky, but this approach will work with up to 997 arguments. Here's an example:

             

            Let( [

              $placeholders = "?, ?, ?";

              $values = "1; 2; 3";

              $query = "select count(*) from sqltest where rec IN (" & $placeholders & ")";

              $ex = "executesql ( \"" & $query & "\";\"\"; \"\"; " & $values & ")"

            ];

            Evaluate($ex)

            )

             

            I'd love to have a better way, but at least we have a few options.

             

            Greg

            • 3. Re: Using ExecuteSQL and FM checkbox lists
              kmtenor

              Thanks, William and Greg.

               

              I guess I've just been spoiled over the years by the open source tool we use for data transforms, which seemingly magically "knows" what to do when a parameter is being used as part of an "in" statement, and apparently formats it correctly somewhere in the background.  It's not a FileMaker tool per-se (we use it to move data from SQL to Filemaker), but as long as we just hand it a comma-delimited list of strings, it apparently takes care of the rest, because it's never failed.

               

              It just seems like we're doing an awful lot of the SQL engine's work, having to "prep" the data for the SQL call.  Given that most developers who are not currently versed in SQL are going to likely be expecting functionality to be equivalent for these kinds of lists, I just think there's room for improvement in FileMaker's handling of the passed parameter behind the scenes.  I suspect as we all move further into ExecuteSQL, we'll find more places that we will hope for improvement, as well (UPDATE and DELETE, anyone?).

               

              You both make an excellent point about escaping for single-quotes.  The examples I gave here are generalized from code we're using to get at data over which we have very good control, so escaping single-quotes hadn't even dawned on me.  We're actually trying to get at distinct subsets of data across multiple selected data sets, so having ExecuteSQL in combination with a checkbox list available to us is a huge step forward, no matter how much massage we have to do to get there.

               

              Thanks again.

               

              -Kevin

              • 4. Re: Using ExecuteSQL and FM checkbox lists
                fmpros

                Glad to have been of help.

                 

                I agree that over time the SQL functionality will most likely (hopefully) expand.

                As mentioned I use a 3rd party SQL plugin and may or may not switch to the

                built in function; it will depend upon benefit vs cost of switching, if I need to make

                serious syntax changes to my SQL table.

                 

                Right now I limit myself to SELECT.  I ran into some problems that I understand to

                have been table locks when an UPDATE or DELETE is issued..... FMP just sits

                there waiting I guess but never seems to recover.  This is something that will have

                to be handled by the FileMaker crew before expanding. 

                 

                A question I have at this point is: are INSERTS liable to the same problems that

                UPDATE AND DELETE have?  INSERT is also very handy in the FileMaker setting.

                I haven't had time to seriously test this yet.

                 

                Best,

                William

                • 5. Re: Using ExecuteSQL and FM checkbox lists
                  kmtenor

                  We tend to shy away from plug-ins because of the size of our deployment (200+ seats) and the possibility that any of our solutions could one day be accessed from an iPad without modification.  We are also multi-platform, which makes plugins even more (less) fun.

                   

                  As far as I know (longtime user, never been certified), INSERT becomes an issue not due to record locking but because of the Manage Database process.  We do a lot of writing to FileMaker via JDBC, and the process we use there can get snagged if someone is working in the Manage Database window for a database we're trying to write to.  Forutnately, there are only two of us who ever go in there, so it's generally pretty easy to identify the problem when it comes up.

                   

                  We occasionally experience issues with record locking on update and delete from the JDBC side, but for the most part, between data separation and preventing fields from being modified in Browse mode, record locking contentions are few and far between for us.

                   

                  -Kevin

                  • 6. Re: Using ExecuteSQL and FM checkbox lists
                    fmpros

                    How does the separation model in and of itself prevent these problems?

                    Or do you mean that by using the globals method and a "Save" button

                    record locking is always at a minimum because it's under program control.....

                    there's never a user sitting with the cursor in a field playing a video game on the side...

                     

                    Thanks,

                    William

                    • 7. Re: Using ExecuteSQL and FM checkbox lists
                      kmtenor

                      I guess it's not the separation model that's making our lives easier.  It's more the fact that most of the data we're updating or deleting is one-way data - for reporting only.  So, when we place that data on the layout, all of the information is being denied Field Entry in Browse mode, and therefore never gets locked out. 

                       

                      It's a balance, for sure - but I've been in meetings where the 1:00 data update hits, and the data changes on the screen right before our eyes!  Generally when that's happening, we're looking at related data while we're either sitting on an anchor record, or through a portal.  Either way, all records coming from the other table are not allowing field entry or in Merge Fields.

                       

                      Hope that makes sense.  We're a bit into the weeds.  I'd be happy to show you our solutions if you're going to DevCon.  Our group will be easy to recognize with our Hancock Lumber shirts on!

                       

                      -Kevin

                      • 8. Re: Using ExecuteSQL and FM checkbox lists
                        fmpros

                        I will keep an eye out; will be interesting to discuss the issues.

                         

                        Best,

                        William

                        • 9. Re: Using ExecuteSQL and FM checkbox lists
                          Malcolm

                          Did you try the Quote() function?

                           

                          $cityList= Quote( Substitute ( chkListField; ¶; "','") );