1 2 Previous Next 15 Replies Latest reply on Jul 17, 2015 6:06 AM by richardd

    Better way to pass SQL field name as a variable?

    wintertj

      Any FM SQL experts ever tried to pass a field name of a field in the SQL statement as a variable to a FM ExecuteSQL calculation?

       

      I'm trying to create a calculation that uses ExecuteSQL and passes pieces of the SQL string as a variable. Trying to create a modular statement that can be passed different field names (because the database contains a multitude of fields that the same exact calc needs to be performed on).

       

      So far, the only way I can get this to work is by making the whole thing a string inside a Let notation and then evaluating the variable created by the Let. This works but is cumbersome and verbose and has nasty escape characters and what not:

       

      Let (var1 = "ExecuteSQL(\"select count(*)

                     from TABLE

                    where "  & RESULT_TABLE::FieldName  & " = ?\";\"\"; \"\"; 55133)"  ;Evaluate(var1) )

       

      I was hoping that FM would allow the ? question marks to function as placeholders for other parts of the SQL statement other than just the SQL parameters in the where clause (i.e. as part of the statement), but this doesn't seem to work:

       

      ExecuteSQL("select count(*)

                     from TABLE

                    where ? = ?"; ""; ""; RESULT_TABLE::FieldName; 55133)

       

      In the above, the first ? question mark isn't substituting a SQL value, but part of the SQL statement itself. This is apparently a no go?

        • 1. Re: Better way to pass SQL field name as a variable?
          okramis

           

          Let (var1 = "ExecuteSQL(\"select count(*)

                         from TABLE

                        where "  & RESULT_TABLE::FieldName  & " = ?\";\"\"; \"\"; 55133)"  ;Evaluate(var1) )

           

          I was hoping that FM would allow the ? question marks to function as placeholders for other parts of the SQL statement other than just the SQL parameters in the where clause (i.e. as part of the statement), but this doesn't seem to work:

           

          ExecuteSQL("select count(*)

                         from TABLE

                        where ? = ?"; ""; ""; RESULT_TABLE::FieldName; 55133)

           

          In the above, the first ? question mark isn't substituting a SQL value, but part of the SQL statement itself. This is apparently a no go?

          You better do something like this:

           

          Let ( [

           

          _qry = "

          SELECT COUNT(*)

          FROM <<Table>>

          WHERE <<Field>>=?

          "

          ] ;


          ExecuteSQL (

          Substitute ( _qry

          ; [ <<Table>> ; YOURTABLE ]

          ; [ <<Field>> ; YOURFIELD ]

          ) //end substitute

          ; "" ; "" //column/row separators

          ; YOURFIELDVALUE //questionmark value

          ) //end executesql


          ) //end let


          Best Regards

          Otmar

          • 2. Re: Better way to pass SQL field name as a variable?
            wimdecorte

            What Otmar is providing will get you half the way.  If your FM fields or tables are not SQL-save (reserved keywords or contains non-supported characters) then you need to do some extra work.

             

            check out my devcon preso from last year if you have access to it for a full example.  If not then check the attached example.  It uses custom functions to use field references instead of hard-coded field and table names and quotes them to keep them safe.

            • 3. Re: Better way to pass SQL field name as a variable?
              wimdecorte

              The added benefit is that if you use the attached file for simple SQL queries you can actually log them and how long it takes to execute them.  I have a more advanced example that I will release after devcon.

              • 4. Re: Better way to pass SQL field name as a variable?
                wintertj

                Excellent, thank you guys. I have Otmar's solution working, avoids both headaches I'd created of string concatenation and escape characters with the fancy substitute function.

                 

                Fortunately, the database I'm working with uses SQL safe naming conventions as the data originated in MS SQL Server. Going to dig into the custom function in the attached file from wimdecorte next....

                 

                Thank you gentlemen!

                • 5. Re: Better way to pass SQL field name as a variable?
                  richardd

                  As does Wim, I use custom functions to build a SQL query with FileMaker field references.

                   

                  Using those functions, your sample would look like this:

                   

                  ExecuteSQL("select count(*)

                                 from " & sqlT( RESULT_TABLE::FieldName ) &

                               " where " & sqlF( RESULT_TABLE::FieldName ) & " = ? "; ""; ""; 55133)

                   

                   

                  sqlT -> returns the TABLE name derived from the field references passed

                  sqlF -> returns the FIELD name derived from the field references passed

                   

                  I in fact have three functions, which return either the just the field name, or just the table name, or both the table and field name, as needed. I have made them separate functions to keep them simple, and they will always quote the table/field since that never hurts.

                   

                  With these one can build the entire SQL query without hardcoding field names ( and potentially having the sql fail in the future because you have renamed the fields ), including the table names for the FROM statement, and proper sql formatted table and field references when you need to distinguish fields in one table from another because you are doing a join.

                   

                  The custom functions look like this: ( See the linked file which contains the custom functions so you can just import them )

                  https://www.dropbox.com/sh/mzw7shuacpmhvkw/AADSGzCyotX7TPlDeKqmd4f0a?dl=0

                   

                  /* sqlF ( field )

                    Input: A fully qualified field name e.g. MyTable::MyField

                    Output: The FIELD name portion, in quotes, e.g. 'MyField'

                    by: Richard Dormann

                  */

                   

                  Quote( GetValue( Substitute ( GetFieldName( field ) ; "::" ; "¶" ); 2 )  )

                   


                  /* sqlT ( field )

                    Input: A fully qualified field name e.g. MyTable::MyField

                    Output: The TABLE name portion, in quotes, e.g. "MyField"

                    by: Richard Dormann

                  */

                   

                  Quote( GetValue( Substitute ( GetFieldName( field ) ; "::" ; "¶" ); 1 ) )

                   

                   

                  /* sqlTF ( field )

                    Input: A fully qualified field name e.g. MyTable::MyField

                    Output: Quoted Sql formatted TABLE and FIELD name e.g. "MyTable"."Myfield"

                    by: Richard Dormann

                  */

                   

                  Quote( Substitute ( GetFieldName( field ) ; "::" ; "\"" & "." & "\"" ) )

                   

                   

                  Btw, I also use List() to build a sql string, which makes using the custom functions more intuitive. So, your example would look like this:

                  Let(

                    _SqlQuery = List(

                    "SELECT ";

                    " count(*) ";

                    "FROM " & sqlT( RESULT_TABLE::FieldName );

                    "WHERE " & sqlF( RESULT_TABLE::FieldName ) & " = ? "

                    );

                    ExecuteSQL( _SqlQuery; ""; ""; 55133)

                  )

                  • 6. Re: Better way to pass SQL field name as a variable?
                    wintertj

                    This is awesome. Will make my life much easier as I have about 20 queries to re write using passed field name parameters, many with quite a few conditions on the WHERE clause.

                    • 7. Re: Better way to pass SQL field name as a variable?
                      fitch

                      This was discussed quite a bit on filemakerhacks.com -- some excellent FileMaker SQL articles there. I use Kevin Frank's field/table custom functions from that site, they are essentially identical to richardd's functions. In the example below, they are #F and #T. My example is similar to the ones already posted, but I find the query easier to read if I write it very simply, and then use Substitute to finish it off.

                       

                      Let ( [
                        sql = "
                          SELECT COUNT(*)
                          FROM myTable
                          WHERE myField = ?
                          "  ;
                      
                          query = Substitute ( sql ;
                              [ "myTable" ; #T( RESULT_TABLE::FieldName ) ];
                              [ "myField" ; #F( RESULT_TABLE::FieldName ) ] ) ;
                      
                          result = ExecuteSQL ( query ; "" ; "" ; 55133 ) 
                        ]  ;
                      
                      result )
                      

                       

                      Again, the advantage of the custom functions here is that your calculation won't break if you rename your fields or table occurrences. The down side, however, is that the query is now context-dependent. To me that's usually not an issue, since the calculation is typically only useful in a particular context.

                      • 8. Re: Better way to pass SQL field name as a variable?
                        erolst

                        Tom Fitch wrote:

                        The down side, however, is that the query is now context-dependent

                         

                        In a cinch, you can still apply GetFieldName() directly, since, different than when wrapped into a CF, it accepts references to unrelated fields.

                        • 9. Re: Better way to pass SQL field name as a variable?
                          richardd

                          As erolst pointed out, GetFieldName() within a CF is context independent. 

                          I do not concern myself with context for the calc that builds the query. In fact, to make using the custom functions easier, I usually use a "to be used for sql only" TO in the relationship graph, the name of which is always prefixed with a "q_" followed by just the table name.

                          There are no relationships to such a TO and it makes them easier to find in the TO list when building the query ( especially if most of your solution still largely uses the anchor/buoy TOG construct ).

                          • 10. Re: Better way to pass SQL field name as a variable?
                            erolst

                            richardd wrote:

                            As erolst pointed out, GetFieldName() within a CF is context independent. 

                            Actually, I pointed out the opposite – or tried, at least …

                            • 11. Re: Better way to pass SQL field name as a variable?
                              richardd

                              Oops, yes you did point out the opposite. My mistake. I would argue then, based on observation and long usage, that GetFieldName() is as accepting of unrelated fields wrapped within a CF, as it is when not.

                               

                              if I were to try "evaluate now" in the Data Viewer of of an unrelated field reference, e.g. simply SomeTable::SomeField, I would of course get the "Field comes from an unrelated field..." error. But if I wrap it within a custom function call, e.g. sqlF( SomeTable::SomeField ), it works.

                               

                              In short, the method is safe within any context ( of the current file ), whether you use Wim's CF, FileMakerHack's CF, or mine,

                              • 12. Re: Better way to pass SQL field name as a variable?
                                rrrichie

                                Yes I stopped using the Custom Functions, cause the context dependency was driving me nuts in this one solution.

                                 

                                Setting up things with the Selector Connector model from Todd Geist et al, makes life easy for new solutions.

                                • 13. Re: Better way to pass SQL field name as a variable?
                                  erolst

                                  richardd wrote:

                                  In short, the method is safe within any context ( of the current file ), whether you use Wim's CF, FileMakerHack's CF, or mine,

                                   

                                  That is not correct: while a CF accepts any field reference (related or unrelated) in the Data Viewer and within a script, that is not the case in a calculation field – where the field reference is checked against the calculation's context and not accepted if it is unrelated (and, of course, “un-global”).

                                  • 14. Re: Better way to pass SQL field name as a variable?
                                    beverly

                                    Excellent, Tom!

                                     

                                    Perhaps this tip from David Jondreau might help with "context":

                                    http://fmforums.com/forums/topic/88426-interesting-quirk-for-unrelated-field-reference-in-field-definition/#comment-406029

                                     

                                    On Jul 16, 2015, at 4:42 PM, Tom Fitch <noreply@filemaker.com> wrote

                                     

                                     

                                    Better way to pass SQL field name as a variable?

                                    reply from Tom Fitch in Discussions - View the full discussion

                                    This was discussed quite a bit on filemakerhacks.com -- some excellent FileMaker SQL articles there. I use Kevin Frank's field/table custom functions from that site, they are essentially identical to richardd's functions. In the example below, they are #F and #T. My example is similar to the ones already posted, but I find the query easier to read if I write it very simply, and then use Substitute to finish it off.

                                     

                                    Let ( [

                                      sql = "

                                        SELECT COUNT(*)

                                        FROM myTable

                                        WHERE myField = ?

                                        "  ;

                                     

                                        query = Substitute ( sql ;

                                            [ "myTable" ; #T( RESULT_TABLE::FieldName ) ];

                                            [ "myField" ; #F( RESULT_TABLE::FieldName ) ] ) ;

                                     

                                        result = ExecuteSQL ( query ; "" ; "" ; 55133 )

                                      ]  ;

                                     

                                    result )

                                     

                                    Again, the advantage of the custom functions here is that your calculation won't break if you rename your fields or table occurrences. The down side, however, is that the query is now context-dependent. To me that's usually not an issue, since the calculation is typically only useful in a particular context.

                                     

                                    1 2 Previous Next