3 Replies Latest reply on Jan 10, 2013 12:55 PM by LSNOVER

    Alternative to hardcoding execute(sql) function

    Hudi

      Hey,

       

      I know there are some methods to writing sql functions so that the field names are not hardcoded, causing them to break if they were ever to be changed.

       

      This is one such method: http://www.filemakerhacks.com/?p=4924

       

      Any other (Better ?) ideas as to how to do this. Unfortunately, I wrote a good amount of these functions without doing this and I'll have to go back and redo them.

       

      Many thanks

        • 1. Re: Alternative to hardcoding execute(sql) function
          Hudi

          From doing minimal research, this seems to be the most commonly used CF for ensuring code doesn't break when you change filed names. It returns the field name in quotes. How is that helpful if filemaker only accepts "\Field\" as the correct syntax.

           

          Am I doing something wrong?

           

          Thanks

           

          /*

          GFN [for GetFieldName] returns only the name of the field, without the relationship portion. As opposed to GetFieldName() which also returns the relationship. This is to be used in SQL queries to protect the field references from breaking if the field is renamed.

           

           

          The Quote() function allows us to reference "SQL reserved" field names without having to manually escape them.

          */

           

           

           

           

           

           

          Let ( [

          a = GetFieldName ( field ) ;

          b = Substitute ( a ; "::" ; ¶ )

          ] ;

           

           

             Quote ( GetValue ( b ; 2 ) )

           

           

          )   //   end let

          • 2. Re: Alternative to hardcoding execute(sql) function
            darrenburgess

            Welcome to the gnarly world of coding SQL in FileMaker!  First off, I would suggest that you start using arguments.  I realise this this does not actually address your question, but arguments are part of the picture in terms of making your ExecuteSQL functions less brittle.  Arguments allow you to specify full TO/field names for the right side of the WHERE statements.  Arguments match up one for one where ever you have a question mark in the query.   Here is an example:

             

            Let([

              query = "

                                          SELECT addr_street1, addr_street2, addr_city, addr_state, addr_postalCode

                                          FROM Addresses

                                          WHERE id_foreign = ?

                                          " ;

             

                      sql = ExecuteSQL ( query ; ", " ; "" ; Contacts::id )

             

              ] ;

             

            sql

             

            )

             

            Post some examples of your code so we can offer more specific suggestions.

             

            Also, very important - Check FIRST that your query does not have any SQL reserved words.   Your problem may not be where you think it is, and you could be spending a whole lot of time debugging a part of the code that is not broken.  My collegue Anders Monsen created a utility to do so for you that can be found here:

             

            http://www.mightydata.com/blog/sql-reserved-words-in-filemaker/

             

            and some debugging tips are here:

             

            http://www.mightydata.com/blog/filemaker-sql-nightmares-tips-for-debugging-sql-queries/

             

            We offer other SQL related article in our blog as well.  Good Luck!

             

            Darren Burgess

            http://www.mightydata.com

            1 of 1 people found this helpful
            • 3. Re: Alternative to hardcoding execute(sql) function
              LSNOVER

              Hudi:

               

              Unless your doing relatively simple queries, trying to preserve the names of the fields is going to be frustrating and slow.  Filemaker does not provide any formal means of "data binding" (i.e. associating SQL Fields with Filemaker Fields), or any real "meta-data" capabilities beyond these simple functions.  ExecuteSQL is a great feature, but it has to be kept in scope.  It is proving to be very popular with the Filemaker Community, so hopefully that will spur FMI to improve and add to the feature set going forward. 

              1 of 1 people found this helpful