13 Replies Latest reply on Nov 27, 2014 7:25 AM by beverly

    Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?

    jgill@occu-med.com

      Due to areas outside of my control, I have a need to dynamically control the otherwise static arguments required for the ExecuteSQL function to work. To make a long story short, I am modifying an installation SeedCode ProMaps so that I can deal with a data type mismatch.

       

      During the course of my troubleshooting, I found that ExecuteSQL was returning a data type mismatch when I was attempting to search on the ID field of a table. The problem that I've encountered is that while I use numerical ID fields, ProMaps assumes the use of text ID fields. I used SeedCode's SQLExplorer to craft a query that works when using the arguments function of ExecuteSQL, but now I've hit a roadblock in crafting an ExecuteSQL function that can alter both the WHERE clause and the ExecuteSQL arguments so that ExecuteSQL functions correctly.

       

      The originally query is set via a Let statement, where the sc_searchFieldString and other variables are set:

       

      sc_searchFieldString = Case (not IsEmpty ($sc_searchFieldString) ; " WHERE " & Substitute ($sc_searchFieldString ; "¶" ; " AND "))
      

       

      The original query looks like this:

      "SELECT " & amapDataObject & "," & aArea & " 
      FROM " & aPROPERTIES &
      sc_searchFieldString 
      & " ORDER BY " & aArea & " ASC, " & aArea & " ASC "
      
      

       

      The problem was that the script was originally stringing together multiple variables that contained each field and value to be queried and then concatenating them together into a text string that contains the WHERE statement, the fields, and the values to be searched. Attempting to search ANY number field results in a data type mismatch. Their query is below:


       WHERE a."id_Parentrecord" = '1234' AND a."textfield1" = 'Visible' AND a."textfield2" = 'CA'
      

       

      After testing and making sure that this was the problem, my solution was to re-craft the query so that it takes advantage of the arguments function of ExecuteSQL. For whatever reason, I was unable to break the query with data type mismatches (or at least mismatches between text and numbers). The new query looks like this (I'll change the other table variables whenever I get the arguments working):

       


      // ------------ BEGIN EXECUTESQL BLOCK ------------ 
      
      Let ( [ 
      
      ReturnSub = "
      " ; // We need to swap out carriage returns in your results with a different character, so specify the character here. 
       is the default.
      SQLResult = ExecuteSQL ( 
      
      // ------------ BEGIN QUERY ------------
      
      "SELECT a."gm_MapData", a."gm_Area"
      FROM "Properties" a " &
      $sc_searchFieldString &"
      ORDER BY a."gm_Area" ASC" ; 
      
      // ------------ END QUERY ------ ------ 
      
      // ------------ BEGIN FIELD AND ROW SEPARATORS ------------ 
      
      " " ; "|*|" ; 
      
      // ------------ END FIELD AND ROW SEPARATORS ------------ 
      
      // ------------ BEGIN ARGUMENTS ------------ 
      // ------------ These arguments are pulled from the values you entered when running your query. You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------ 
      
      $sc_searchArgumentQueryString <- What I want to use as my arguments replace
      
      // ------------ END ARGUMENTS ------------ 
      
       ) ] ; 
      
      // ------------ BEGIN CARRIAGE RETURN SUBSTITUTIONS ------------ 
      
       Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] )
      
      // ------------ END CARRIAGE RETURN SUBSTITUTIONS ------------ 
      
      )
      
      // Compliments of SeedCode… Cheers!
      
      // ------------ END EXECUTESQL BLOCK ------------ 
      
      

       

      And $sc_searchFieldString is now replaced with:


      WHERE a."id_ParentRecord" = ? AND a."textfield1" = ? AND a."textfield1" = ?
      

       

      When it comes time to parse the information into a value that should work as an argument for ExecuteSQL, I'm simply replacing the pilcrows with a ';'


      $sc_searchArgumentQueryString = 
      Substitute ($sc_searchFieldArguments ; ¶ ; ";" )
      

       

      Obviously this isn't working, but I'm wondering what I'm doing wrong.

        • 1. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
          wimdecorte

          jgill wrote:

           

          And $sc_searchFieldString is now replaced with:


          WHERE a."id_ParentRecord" = ? AND a."textfield1" = ? AND a."textfield1" = ?
          

           

           

          When it comes time to parse the information into a value that should work as an argument for ExecuteSQL, I'm simply replacing the pilcrows with a ';'


          $sc_searchArgumentQueryString = 
          Substitute ($sc_searchFieldArguments ; ¶ ; ";" )
          

           

           

          Obviously this isn't working, but I'm wondering what I'm doing wrong.

           

          Why ";"?  Values need to go where the "?" arguments go.

          • 2. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
            jgill@occu-med.com

            I'm not replacing the '?' (argument) with ':', I'm trying to replace the entire arguments parameter of ExecuteSQL with a variable that contains the arguments parameter for ExecuteSQL.

             

            The WHERE query is already replaced with a variable, now the problem is that because I can have a dynamic query that could consist a varying number of AND clauses, I have to control the arguments dyamically as well.

             

            Correct me if I'm wrong, but if I have a ExecuteSQL SELECT statement that reads (bare with me on the formatting here):

             

            "SELECT a.\"gm_MapData\", a.\"gm_Area\"
            FROM \"Properties\" a " &
            WHERE a."id_Parentrecord" = ? AND a."textfield1" = ? AND a."textfield2" = ?
            ORDER BY a.\"gm_Area\" ASC" ; 
            

             

            I need to have 3 arguments; one for id_parentRecord, one for textfield1 and one for textfield2.  If I hard code the arguments within ExecuteSQL, then they read:

             

            "1234" ; "textfield1_searchQuery" ; "textfield2_searchQuery"
            

             

            However, because the WHERE clause is actually being controlled via a variable that can contain fewer or more statements than just those 3, I cannot hardcode the arguments, right?

            • 3. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
              wimdecorte

              Sure you can,  You can make the whole ExecuteSQL() a string and then call the Evaluate() function on it.

              1 of 1 people found this helpful
              • 4. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                BruceHerbach

                You can either use set variable in script steps before running the query or add lines to the Let statement at the top,  that create variables for the arguments.

                 

                So the top would look like:

                Let([

                 

                $X1 = "1234";

                $Tx1 = "Visible";

                $Tx2 = "CA";

                .

                Rest of Seedcode stuff goes here.

                .

                 

                Then just replace  the origianal hardcoded where statement

                 

                WHERE a."id_Parentrecord" = '1234' AND a."textfield1" = 'Visible' AND a."textfield2" = 'CA'

                 

                with

                 

                WHERE a."id_Parentrecord" = $X1 AND a."textfield1" = $Tx1 AND a."textfield2" = $Tx2

                 

                 

                You can of course use any variable name you want.

                HTH

                • 5. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                  wimdecorte

                  Bruce Herbach wrote:

                   

                  WHERE a."id_Parentrecord" = $X1 AND a."textfield1" = $Tx1 AND a."textfield2" = $Tx2

                   

                   

                  The downside is that you can't take advantage of FM automatically quoting numbers and text and formatting dates and time into SQL-ese like this...

                  • 6. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                    steve_ssh

                    Hello jgill,

                     

                    From your very first statement I gather that there are parts of this puzzle which you can not control, and parts which you can modify.

                     

                    Thus it leaves you to figure out how you can run the query as desired (i.e. where your PK value is treated as a numeric value) given the set of limitations.

                     

                    It is not clear to me which parts of the puzzle you can freely modify, and which you can not.

                     

                    I'd like to ask this:

                     

                      1) Are the pieces that you can modify limited to just the values of:  $sc_searchFieldString and $sc_searchArgumentQueryString ?

                     

                      2) Or, is the only piece that you can modify just the value of:  $sc_searchArgumentQueryString ?

                     

                      3) Something else other than #1 or #2 above?  Do you have any freedom to modify the entire SQL statement ?

                     

                      4) Is it true that the ExecuteSQL call that is concerned is being invoked in a context that you can not freely modify ?

                     

                      5) Is the role that SQLExplorer plays here just a tool that you are using to achieve a result, or are you somehow locked into using the query that this tool generates?

                     

                     

                    Any clarification on the above might help.  I have read and re-read your post to see if I could answer the above questions based on the content of the post, but it still is not clear to me.

                     

                     

                    An observation:

                     

                    If you are free to modify the SQL query, have you tried changing the code so that the following line:

                     

                       WHERE a."id_Parentrecord" = '1234' AND a."textfield1" = 'Visible' AND a."textfield2" = 'CA'

                     

                    Instead reads as follows? :

                     

                        WHERE a."id_Parentrecord" = 1234 AND a."textfield1" = 'Visible' AND a."textfield2" = 'CA'

                     

                    (Note the removal of single quotes around the numeric value.)

                     

                    It seems plausible that if you were somehow able to swap in your modified query of:

                     

                        WHERE a."id_ParentRecord" = ? AND a."textfield1" = ? AND a."textfield1" = ?

                     

                    that it might just as easily be possible to swap in the variation I mention above where the numeric value is not quoted.

                     

                     

                     

                     

                    Since I am having a hard time wrapping my head around the constraints that you face, it is difficult for me to know if this suggestion is really of any help to you.  I hope that perhaps it is.

                     

                    One or two more options seem possible to me, but, rather than taking multiple stabs at a problem that I don't quite get, I'll wait and see what you might have to say about the above.

                     

                     

                    Very best regards,

                     

                    -steve

                    • 7. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                      jgill@occu-med.com

                      I think this is the most flexible approach so far, but I'm having difficulty getting the ExecuteSQL function to evaluate.  My understanding is that I need to turn the entire string into literal text and then have Evaluate run it, but I'm having difficulty crafting the literal string.

                       

                      Using the data view, this is the actual ion of the ExecuteSQL query that I'm attempting to run:

                      "ExecuteSQL ( SELECT a.\"gm_MapData\", a.\"gm_Area\" FROM \"Properties\" a " & $sc_searchFieldString &" ORDER BY a.\"gm_Area\" ASC" & ";" & $sc_fieldSeparator & " ; " & $sc_rowSeparator & " ; " & $sc_searchArgumentQueryString & " )"
                      

                       

                      This is the Result:

                      ExecuteSQL ( SELECT a."gm_MapData", a."gm_Area" FROM "Properties" a WHERE a."id_company" = ? AND a."clinicstate" = ? AND a."visibleStatus" = ? ORDER BY a."gm_Area" ASC;!~! ; !•! ; 1771 ; CO ; Visible )
                      

                       

                      However when I wrap that statement in Evaluate, I get an error stating that "The specified field cannot be found"

                       

                      Does the RESULT need to be a the literal string or does the Expression need to be the literal?

                      • 8. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                        wimdecorte

                        This is the relevant part of a script that I use in my solutions. (I have one script that runs all ExeucteSQL()s so that I can log them and analyze for performance)

                         

                        # dynamically make the ExecuteSQL syntax to allow for a variable number of ? to be fed in

                        #

                        Set Variable [$SQLquery; Value:"ExecuteSQL( " & Quote( $_SQLquery ) & " ; " & Quote( $delimField ) & " ; " & Quote( $delimRecord )]

                        If [not IsEmpty( $_~SQLparameterList )]

                                  Set Variable [$max; Value:ValueCount( $_~SQLparameterList )]

                                  Set Variable [$counter; Value:1]

                                  Loop

                                            #

                                            Set Variable [$param; Value:GetValue( $_~SQLparameterList ; $counter )]

                                            Set Variable [$SQLquery; Value:$SQLquery & " ; " & Quote( $param )]

                                            #

                                            Set Variable [$counter; Value:$counter + 1]

                                            Exit Loop If [GetAsNumber( $counter ) > GetAsNumber( $max )]

                                  End Loop

                        End If

                        Set Variable [$SQLquery; Value:$SQLquery & ")"]

                        #

                        #

                        Set Variable [$start; Value:Get ( CurrentTimeUTCMilliseconds )]

                        Set Variable [$result; Value:Evaluate( $SQLquery )]

                        Set Variable [$end; Value:Get ( CurrentTimeUTCMilliseconds )]

                        1 of 1 people found this helpful
                        • 9. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                          jgill@occu-med.com

                          steve_ssh wrote:

                           

                          Hello jgill,

                           

                          From your very first statement I gather that there are parts of this puzzle which you can not control, and parts which you can modify.

                           

                          Thus it leaves you to figure out how you can run the query as desired (i.e. where your PK value is treated as a numeric value) given the set of limitations.

                           

                          It is not clear to me which parts of the puzzle you can freely modify, and which you can not.

                           

                          I'd like to ask this:

                           

                            1) Are the pieces that you can modify limited to just the values of:  $sc_searchFieldString and $sc_searchArgumentQueryString ?

                           

                            2) Or, is the only piece that you can modify just the value of:  $sc_searchArgumentQueryString ?

                           

                            3) Something else other than #1 or #2 above?  Do you have any freedom to modify the entire SQL statement ?

                           

                            4) Is it true that the ExecuteSQL call that is concerned is being invoked in a context that you can not freely modify ?

                           

                            5) Is the role that SQLExplorer plays here just a tool that you are using to achieve a result, or are you somehow locked into using the query that this tool generates?

                             To answer most of you question at once, I'm not tied into using any one tool to accomplish this job.  About my only limitation is that I don't want to have to go and change my numeric ID fields to text fields in my solution as that represents the biggest unknown as far as unintended consqeuences are concerned.

                           

                          An observation:

                           

                          If you are free to modify the SQL query, have you tried changing the code so that the following line:

                           

                             WHERE a."id_Parentrecord" = '1234' AND a."textfield1" = 'Visible' AND a."textfield2" = 'CA'

                           

                          Instead reads as follows? :

                           

                              WHERE a."id_Parentrecord" = 1234 AND a."textfield1" = 'Visible' AND a."textfield2" = 'CA'

                           

                          (Note the removal of single quotes around the numeric value.)

                           

                          It seems plausible that if you were somehow able to swap in your modified query of:

                           

                              WHERE a."id_ParentRecord" = ? AND a."textfield1" = ? AND a."textfield1" = ?

                           

                          that it might just as easily be possible to swap in the variation I mention above where the numeric value is not quoted.

                           

                          I originally tried this, the problem being that the actual query statement is being passed via a variable.  This means that the result of that variable is by definition a text statement.  From my understanding of how ExecuteSQL works, if you choose not to use the ExecuteSQL arguments and instead directly pass the search parameters via the query's, then you are limited to SQL's native datatype limitations (i.e. numeric fields cannot search any numbers, number fields cannot search text, etc).  If you use Filemaker's arguments, then Filemaker takes care of any mismatches automatically.  Because the field that I'm querying is a number field and the SELECT statement is a text statement (even without the surround quotes), the query fails.

                          • 10. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                            wimdecorte

                            If you happened to be at this year's devcon, you can find the full approach in my session's demo file.  The script is called

                             

                            executeSQL( SQLquery ; ~SQLparameterList ; ~delim ; ~log )

                            • 11. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                              steve_ssh

                              Hi jgill,

                               

                              Thank you for indulging all of my questions.  I sincerely appreciate it.

                               

                              Perhaps it would be worth taking a look at the attached sample file.

                               

                              It offers simple counter-examples to the notion that an SQL query passed as text in a variable can not successfully search on numeric values.

                               

                               

                              For instance, the following works just fine, with the primary key value being numeric:

                               

                                Let([


                               

                                  $mySqlVariable = "SELECT A.FirstName, A.LastName FROM SampleData A WHERE A.pk_SampleDataID = 14 AND A.LastName = 'Smith' AND A.FirstName = 'Esperanza'"

                               

                                ];


                               

                                  ExecuteSQL( $mySqlVariable; ""; "" )

                                )

                               

                               

                               

                              Another observation:

                               

                              If there is still some reason why the numeric value in your query must be passed as a parameter, it still seems that you have the option of passing only that value as a param, and leaving the other two values embedded in the statement, i.e.

                               

                               

                                 WHERE a."id_Parentrecord" = ? AND a."textfield1" = 'Visible' AND a."textfield2" = 'CA'


                               

                              I believe that doing so would free you of the need to pass multiple SQL params into ExecuteSQL, thus allowing you to continue down the path of supplying a simple $variable value without the need to wrap things in an Evaluate() call.

                               

                              HTH & best,

                               

                              -steve

                              • 12. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                                jgill@occu-med.com

                                Bam!  This worked perfectly for me.  Thanks a ton.

                                • 13. Re: Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?
                                  beverly

                                  jgill, anothrer useful function is

                                   

                                       EvaluationError(expression)

                                   

                                   

                                  You can get an error code (if any) that may be helpful when you are otherwise getting "?" as a result.  I may do this:

                                   

                                   

                                   

                                       if ( $result = "?" ;  EvaluationError( $query ) ; $query )

                                   

                                   

                                   

                                  Beverly

                                   

                                  Sure you can,  You can make the whole ExecuteSQL() a string and then call the Evaluate() function on it