9 Replies Latest reply on Oct 4, 2012 9:21 AM by beverly

    Execute sql


      First time using the new FMP12 execute sql function. I'm trying to get a list of ID's from a table but can't seem to do so.


      Btw, this is going to open up a whole new way of doing things around here if I can only get it to work.


      here is what I have


      ExecuteSQL ( "SELECT ID FROM Students WHERE _f_omit=1" ; "";"")


      (ID is a field

      Students is a table)


      Its pulling up a "?"


      Any help is appreciated!

        • 1. Re: Execute sql

          perhaps the name of the field needs to be quoted? Please see the ODBC user guide for some of the suggestions. This forum also has several questions and replies about ExecuteSQL (the function).


          Try this:

               ExecuteSQL ( "SELECT ID FROM Students WHERE \"_f_omit\" = ? " ; "" ; "" ; 1 )



          • 2. Re: Execute sql

            SQL does not like field names that begin with _  so the _f_omit field is likely your problem.


            You can use a custom function to wrap it in quotes and (Kevin Frank has a good one at his FileMaker Hacks website.


            But I have taken to using a naming convention without underscores as starting characters.  So if you change your field name from "_f_omit" to "z_f_omit" or (1_f_omit) etc., that will probably solve your current problem.



            • 3. Re: Execute sql

              Karen, in some places the leading number in a name can also be problematic. aa_f_omit would give you the sort-to-the-top (if desired) and a more "world friendly" name". The same applies to the name of the table - use care when naming.


              • 4. Re: Execute sql

                thanks for your help but I am still stumped on this issue.


                I simplified the query and am still getting a ? as a result.


                This is an actual table and field that I used for testing in a simpler way. Where am I going wrong?


                ExecuteSQL ( "SELECT Brand Name FROM Brand"  ; "" ; "")



                • 5. Re: Execute sql



                  Beverly, you were right, the field name needs to be quoted. I have not seen this anywhere in the examples on the many blog posts and videos out there.


                  This is what did the trick:


                  ExecuteSQL ( "SELECT \"Brand Name\" FROM Brand"  ; "" ; "")


                  Why are the quotes necessary for the field name?


                  anyway, I just posted this so that it might be of use to someone.

                  • 6. Re: Execute sql

                    Again you need to quote where there needs to be quotes. A field name of Brand_SPACE_Name is not a valid SQL name.

                    ExecuteSQL ( "SELECT \"Brand Name\" FROM Bran" ; "" ; "" )

                    There are several threads on this forum discussing the ExecuteSQL function. Many of them have links to articles, video(s) and/or sample databases showing the proper way to make the queries.



                    • 7. Re: Execute sql

                      Try this link to tagged articles for the ExecuteSQL() function.


                      When searching for the Function, remember that "FileMaker Execute SQL" is NOT the same as "FileMaker ExecuteSQL". The first is a script step and the other is what you are searching for, the function.


                      Why are the quotes necessary for the field name?


                      It's the format of the Structured Query Language whether you are in a SQL database (MS SQL, Oracle, MySQL, etc.) or any application that uses it.


                      http://www.filemaker.com/12help/html/func_ref3.33.6.html & http://www.filemaker.com/12help/html/create_db.8.9.html#1081048


                      Many developers have been preaching "alpha-numerics and underscore ONLY" for many years. If you cannot revise the names of your fields and tables for the sake of ExecuteSQL(), then you must quote them! The backslash+doubleQuote is a way to escape the doubleQuote inside an already quoted string.

                      "John said, \"Hello, brushfire! Do you understand?\""




                      1 of 1 people found this helpful
                      • 8. Re: Execute sql

                        Can you quote with single quotes or backticks --    'xxx'   or  `xxx`   ?


                        Note that the ODBC import script step provides a nice little query editor/validator if you also make you file an ODBC data source. Unfortunately it doublequotes every string and those quotes need to be escaped or changed to use with ExecuteSQL function.

                        • 9. Re: Execute sql

                          Can you quote with single quotes or backticks --    'xxx'   or  `xxx`   ?


                          A single quote would be a "literal" in SQL. 'xxx' would return xxx for every row found. Pretty handy when you need literals and CONSTANTS in SQL results. Concatenate like this:

                               lastname', 'firstname AS lfName

                          would return for the "column" lfName:

                               Reid, Oliver


                          or to find-by-constant:

                               WHERE type = 'Mailing Address' // a field "type" has the value "Mailing Address", no need for calculated constant to match!


                          I believe I tried the backtick (`) as MySQL uses this in its SQL, but FileMaker didn't like them.