10 Replies Latest reply on Sep 21, 2015 2:30 PM by sccardais

    Using Variables in SQL Query

    sccardais

      Is it possible to use variables in SQL queries?

       

      In my example below, If I replace $$P1End with a date wrapped in single quotes such as '9/20/2015', I get an accurate result. If I set a variable $$P1End with a date, the query returns ?

       

      ExecuteSQL ("SELECT COUNT (OrgID)

      FROM ABC_ORGs

      WHERE \"Date Ordered\" < $$P1End AND Status = 'Client'  ";"";"")

       

      Are variables not allowed in SQL queries or is my syntax incorrect?

        • 1. Re: Using Variables in SQL Query
          erolst

          sccardais wrote:

           

          ExecuteSQL ("SELECT COUNT (OrgID)

          FROM ABC_ORGs

          WHERE \"Date Ordered\" < $$P1End AND Status = 'Client'  ";"";"")

           

          Are variables not allowed in SQL queries or is my syntax incorrect?

           

          Yes they are, and well, who knows – you may have a problem with the date format that the variable holds.

           

          You can prevent this problem (and similar ones) by utilizing the “optional parameter” of ExecuteSQL(), e.g.

           

          ExecuteSQL ("

            SELECT COUNT (OrgID)

            FROM ABC_ORGs

            WHERE \"Date Ordered\" < ? AND Status = ?

            " ; "" ; "" ; $$P1End ; "Client"

          )

           

          where the arguments given in FileMaker syntax will automatically be formatted and/or quoted as necessary and injected in lieu of the placeholder '?'s.

          • 2. Re: Using Variables in SQL Query
            carlosilvia0

            Yes,

            You can use variable in executeSQL. You substitute le value with ? and append to the end of function the variable.

             

            Look the example:

            100 * Salary::Salary / ExecuteSQL("select sum(S.salary) from Employees E join Salary S on E.EmpID = S.EmpID where E.Department = ?"; ""; ""; Department)

            in URL:

            http://www.filemaker.com/help/12/fmp/html/func_ref3.33.6.html

            • 3. Re: Using Variables in SQL Query
              sccardais

              Perfect. Thank you very much.

              • 4. Re: Using Variables in SQL Query
                beverly

                You've gotten your answer. I'll tell you why. The variable, when INSIDE the quoted query is considered literal text. FM does not substitute if the variable is text. However, using the ? Parameter allows the calc engine to place the VALUE of that variable before evaluating the query.

                 

                Another method is to not quote it:

                 

                WHERE x= '" & $myvar & "' ...

                 

                See how I had to be mindful of the single quote? That's why the preference to use the ? parameter instead. FMP takes care of single-quoting text and not quoting numbers.

                 

                -- sent from myPhone --

                Beverly Voth

                --

                • 5. Re: Using Variables in SQL Query
                  sccardais

                  Thank you, Beverly.

                   

                  It's so helpful to know ""why" something works.

                  • 6. Re: Using Variables in SQL Query
                    carlosilvia0

                    If you use the quote then I suggest you use Evaluate() and  EvaluationError() function

                    • 7. Re: Using Variables in SQL Query
                      sccardais

                      Thank you. I will try your suggestions.

                      • 8. Re: Using Variables in SQL Query
                        beverly

                        If  you use the Evaluate() around your query (processed before ExecuteSQL), it will work, but is unnecessary when the ? parameters are used:

                         

                        I consider this INCORRECT:

                        _Q = Evaluate( " SELECT abc, def, ghi FROM $$tablVar WHERE abc = $$fieldVAR ")

                        or however it's being used.

                         

                        I consider this CORRECT:

                        _Q = " SELECT abc, def, ghi FROM " & $$tablVar & " WHERE abc = " & $$fieldVAR 

                         

                        BETTER:

                        _Q = " SELECT abc, def, ghi FROM " & $$tablVar & " WHERE abc = ? "

                         

                        Note that there are times when variables are used and the concatenation of the text (automatically evaluated, BTW) is used. This is useful to assign fields to variables so that the GetFieldName() will prevent your queries from breaking if the field (or table) are renamed. The other valuable place is for the IN() part of the WHERE clause.

                         

                        The EvaluationError() is for debugging and I only use it if I get "?" result (snippet of Let() calc):

                         

                        Let (

                        [ ... // assign vars

                        ; _Q = ....

                        ; _Result = ExecuteSQL (  _Q ; "" ; "" )

                        ]; If ( _Result = "?" ; "ERROR: " & EvaluationError ( _Result ) ; _Result )

                        )

                         

                        beverly

                        • 9. Re: Using Variables in SQL Query
                          carlosilvia0

                          Hi,

                          thanks for your suggestion. I wrote that if use ' (quote) or &(union) then use Evaluate() and EvaluateError(). In other case you use only ExecuteQuery().

                          • 10. Re: Using Variables in SQL Query
                            sccardais

                            Coincidentally, I noticed this excellent article by Kevin Franks related to the question I posted yesterday.

                             

                            He mentions a Custom Function that I wish I had known about yesterday!

                             

                            Found Set Awareness Revisited | FileMakerHacks