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

    Execute sql

    Hudi

      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
          beverly

          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 )

           

          Beverly

          • 2. Re: Execute sql
            karendweaver

            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.

             

            Karen

            • 3. Re: Execute sql
              beverly

              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.

              Beverly

              • 4. Re: Execute sql
                Hudi

                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"  ; "" ; "")

                 

                Thanks!!

                • 5. Re: Execute sql
                  Hudi

                  AHA!

                   

                  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
                    beverly

                    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.

                     

                    Beverly

                    • 7. Re: Execute sql
                      beverly

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

                           http://www.filemakerhacks.com/?tag=executesql

                      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://en.wikipedia.org/wiki/SQL

                      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?\""

                       

                       

                      Beverly

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

                        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
                          beverly

                          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.

                          Beverly