1 2 Previous Next 16 Replies Latest reply on Apr 25, 2017 11:51 AM by Steven_FL

    Custom Function using Execute SQL

    Steven_FL

      Attached I made a simple file for this Custom Function and the same calculation as a Calculated Field

      As calculated field it works perfectly:

      ExecuteSQL (

      "SELECT     Name

      FROM         Test

      WHERE        Record_ID=?" 

      ; "" ; "" ;         Record_Request  )

      But the same calculation using Custom Function will not working:

      20170425 071509-Edit Custom Function.png

      Any one an idea what I'm missing / doing wrong?

      Thanks

        • 1. Re: Custom Function using Execute SQL
          coherentkris

          Table is a reserved word in SQL.

          I would wrap the whole thing in a let statement...

           

           

          Let (

          [

          _t = Table;

          _f = relationship_field;

          _w = which_record;

          _sql = "SELECT " & _f & " FROM " & _t & " WHERE " & _f & " =?"

          ];

           

          ExecuteSQL ( _sql ; "" ; "" ; _w )

           

          )

           

          This might not be perfect but you get the idea.

          1 of 1 people found this helpful
          • 2. Re: Custom Function using Execute SQL
            beverly

            If you are passing the values, especially field and table names, you need to use the fully-qualified name:

            table::field

            so use

            GetFieldName(___)

            when passing (or possibly in the custom function, too)

            beverly

            • 3. Re: Custom Function using Execute SQL
              Mike Duncan

              You will still need to quote field names that are reserved words. I use the quote function, as it takes into account all quoting that might be needed for me.

               

              Quote ( "Table" )

              1 of 1 people found this helpful
              • 4. Re: Custom Function using Execute SQL
                Steven_FL

                Dear Mike,

                Thanks which part of this custom function needs Quote ()

                ExecuteSQL (

                "SELECT         Locked_for_Field

                FROM         Table

                WHERE         Relationship_Field=?" 

                ; "" ; "" ;         Which_Record  )

                 

                Thanks

                Steven

                • 5. Re: Custom Function using Execute SQL
                  beverly

                  one more thing....

                   

                  A query is TEXT String, it is not a calculation.

                   

                  You can use:

                  " SELECT table.column ..."

                  and it works fine in a calculation that can evaluate the values

                  but in a Custom Function, because the parameters are inside the quotes, it will not substitute out. So try:

                  " SELECT " & Quote(table) & "." & Quote(column) & ....

                  then any passed parameters will be correct in the CF.

                   

                  beverly

                  1 of 1 people found this helpful
                  • 6. Re: Custom Function using Execute SQL
                    Menno

                    I agree with all previous replies. A few years ago before ExecuteSQL was introduced in FMP12 I used (and still do) a plugin to do FQL.

                     

                    The problem was that using FileMaker fieldnames in in SQL-statements are not robust and if you change a fieldname, somewhere in your scripts or fielddefinitions a FQL-statement stopped working.

                     

                    For that I created a set of CF's that would allow me change fieldnames, tablenames and not have me worry about reserved words.

                     

                    I have included the most important functions in your file and for demo-purpose I created an example calculation. Normally I would  not and do not recommend anyone to use eSQL in field definitions, but only use eSQL in scripts to create local/global variables.

                    1 of 1 people found this helpful
                    • 7. Re: Custom Function using Execute SQL
                      Steven_FL

                      Dear Menno,

                       

                      Wow this is so clean. Thanks a lot. I will definitely use this very cool.

                      Now how to I get this part to work as a Custom Function, ignoring what you sent me. Once I get how this works I can merge this and yours into one and I have the really perfect simple solution.

                      ExecuteSQL (

                      "SELECT         Locked_for_Field

                      FROM         Table

                      WHERE         Relationship_Field=?"

                      ; "" ; "" ;         Which_Record  )

                       

                      Thanks a lot.

                      Steven

                      • 8. Re: Custom Function using Execute SQL
                        Menno

                        Your function isn't recursive, so it is pretty easy to get it perfectly working in the dataviewer first. There you will also find the caveats, which usually are not so evident in the fielddefinitions.

                         

                        I would start in the DV with this:

                        Let ( [

                             Table = "Test" ;

                             Relationship_Field = "Record_ID" ;

                             Which_Record = Test::Record_Request ;

                             Locked_for_Field = "Name"

                        ] ;

                             ExecuteSQL (

                                  "SELECT "  & Quote ( Locked_for_Field ) &

                                  " FROM " & Quote ( Table ) &

                                  " WHERE " & Quote ( Relationship_Field ) & "=?" 

                                  ; "" ; "" ;  Which_Record )

                        )

                        And copy the result function in the let-statement

                         

                        You had one more error in your file:

                        The calculation for SQL_Result_with_CF was:

                        cf_SQL_Test ( "Test" ; "Record_ID" ; "Record_Request" ; "Name" )

                        Where it should have been:

                        cf_SQL_Test ( "Test" ; "Record_ID" ; Record_Request ; "Name" )

                        2 of 2 people found this helpful
                        • 9. Re: Custom Function using Execute SQL
                          Steven_FL

                          Dear Menno,

                          Thansk for the help. I have it like you said:

                          cf_SQL_Test ( "Test" ; "Record_ID" ; Record_Request ; "Name" )

                          and the Custom Function like this:

                          ExecuteSQL (

                          "SELECT         Locked_for_Field

                          FROM         Table

                          WHERE         Relationship_Field=?" 

                          ; "" ; "" ;         Which_Record  )

                          and its not working.

                          I feel in the custom Function something is wrong.

                          Thanks Steven

                          • 10. Re: Custom Function using Execute SQL
                            beverly

                            Yes there is something wrong, scroll up a bit to see my answer:

                             

                            beverly

                            • 11. Re: Custom Function using Execute SQL
                              Menno

                              Change you CF into this like I had in my previous reply:

                              ExecuteSQL (

                                        "SELECT "  & Quote ( Locked_for_Field ) &

                                        " FROM " & Quote ( Table ) &

                                        " WHERE " & Quote ( Relationship_Field ) & "=?" 

                                        ; "" ; "" ;  Which_Record )

                              You have used (for SQL) reserved words in you DB: "Name" is a reserved word, so that must be quoted. The other 2 (fieldnames) do not conflict, but just out of habit, I have quoted them too. And see beverly 's comment

                              2 of 2 people found this helpful
                              • 12. Re: Custom Function using Execute SQL
                                Steven_FL

                                Dear Menno,

                                 

                                Thanks and got it to work like this:

                                ExecuteSQL (

                                          "SELECT "  & Locked_for_Field &

                                          " FROM " & Table &

                                          " WHERE " & Relationship_Field  & "=?" 

                                          ; "" ; "" ;  Quote ( Which_Record) )

                                • 13. Re: Custom Function using Execute SQL
                                  beverly

                                  and this would be "ok" for a custom function where the values you pass get substituted, so I'd doubt your table name is "Table". But heed the warning and try not to use reserved words for any calculation.

                                   

                                  beverly

                                  • 14. Re: Custom Function using Execute SQL
                                    Steven_FL

                                    Dear Berverly,

                                     

                                    I get it you don't think it works but it does see attached file.

                                    Thanks for you constant input, it is really nice and very helpful.

                                     

                                    Have a great awesome day.

                                    Thanks Steven

                                    1 2 Previous Next