3 Replies Latest reply on Jan 10, 2013 8:28 AM by beverly

    Odd ExecuteSQL result

    darrenburgess

      Here is an interesting result and I wonder if the smarties out there have an explanation. The following SQL statement returns the logged in account name for the file. Now normally I would expect that using an SQL reserved word would cause the calc to fail and return '?'. USERNAME is an SQL reserved word and is also a field in the table EmailSMTPSettings. This field contains different data than is returned by the SQL statement. The SQL does of course return the correct result if I change the field name to avoid the reserved word.

       

      Let([

      query = "

      SELECT UserName

      FROM EmailSMTPSettings

      " ;

       

      sql = ExecuteSQL ( query ; ", " ; "" )

       

      ] ;

       

      sql

       

      )

       

       

      Darren Burgess

      http://www.mightydata.com

        • 1. Re: Odd ExecuteSQL result
          nickchapin

          Very interesting. It's obviously returning the fmp username of the current user (for each record in the table) regardless of whether the field exists or not (it doesn't in the table I tested). Of course, quoting the field name gets what you are looking for.

          • 2. Re: Odd ExecuteSQL result
            beverly

            Direct quote from "The Missing FileMaker 12 ExecuteSQL Reference:

             

            ======

            SQL has some functions that maybe used in the queries. Each SQL db may have a different set of functions. The last query contains the System function "CURRENT_DATE" and returns the date in the "YYYY-MM-DD" format. Other System functions that work within ExecuteSQL:

            • CURRENT_TIME = "hh:mm:ss" (24-hour time)

            • CURRENT_TIMESTAMP = "YYYY-MM-DD hh:mm:ss" (24-hour time)

            • CURRENT_USER = returns same as the Get ( AccountName ) function in

            FileMaker (also USER & USERNAME return the same value)

            ======

             

            The "reserved word" is a SQL function and used as such within the ExecuteSQL statement.

            You can get the pdf and example file: <http://www.filemakerhacks.com/?p=6605>

             

            Beverly Voth, author

            • 3. Re: Odd ExecuteSQL result
              beverly

              Nick is correct. IF you quote the field name which is also a SQL reserved word, you may get the results you seek.

               

              
              Let([
                       query = " SELECT \"UserName\"
                                    FROM EmailSMTPSettings
                                 " ;
                        sql = ExecuteSQL ( query ; ", " ; "" )
              ] ;
              sql
              )
              

               

              Very interesting. It's obviously returning the fmp username of the current user (for each record in the table) regardless of whether the field exists or not (it doesn't in the table I tested). Of course, quoting the field name gets what you are looking for.