9 Replies Latest reply on May 27, 2016 6:17 AM by kamal1234

    sql query variables?

    kamal1234

      Dear Community Members,

       

      I am trying to use ExecuteSQL statement but I am not be able to pass variables to the query.

       

      I have seen may similar post for passing variables in query but I found them very advance and beyond my  understanding.

       

      Please can someone help me how to concatenate following so that It picks up the values. At present, it is printing as it is.

       

      update CPD_COMPWORKSDETAILS set DESCRIPTION="&PL_Competition::Description&", TITLE="&PL_Competition::Title&", AUTHOR="&PL_Competition::Author&", PUBLISHER="&PL_Competition::Publisher&", PRICE="&PL_Competition::Price&"  where CWID="&PL_Competition::CWID&"

       

      Thank you,

      Kamal.

        • 1. Re: sql query variables?
          Mike_Mitchell

          Well, there are two issues I see. Are you trying to use the Execute SQL script step (note the space), or the ExecuteSQL ( ) function? If the latter, then it doesn't support UPDATE.

           

          If the former, then you probably need to use single quotes around any string values (text fields), like this:

           

          "UPDATE CPD_COMPWORKSDETAILS SET " &

          "DESCRIPTION = '" & PL_Competition::Description & "', " &

          "TITLE = '" & PL_Competition::Title & "', " &

          "AUTHOR = '" & PL_Competition::Author & "', " &

          "PUBLISHER = '" & PL_Competition::Publisher & "', " &

          "PRICE='" & PL_Competition::Price & "' " &

          "WHERE CWID = '" & PL_Competition::CWID & "'"

          1 of 1 people found this helpful
          • 2. Re: sql query variables?
            user19752

            I tried to write to use ? for parameter in query, but it is not supported for script step !? Too bad...

             

            You need Substitute ( textvalue ; "'" ; "''" ) if the text value contain single quote...

            • 3. Re: sql query variables?
              kamal1234

              Thank you Mike,

               

              It worked perfectly for me. I even wrote much more complex queries  using your example above.

               

              My only modification was instead of using "SQL Text"  I have to apply above query into "Calculated sql text" just for the people who are very new to filemaker like myself.

               

              Thank you,

              Kamal.

              • 4. Re: sql query variables?
                beverly

                Mike was correct (single quote TEXT). Numbers however are not quoted in SQL.

                It can be helpful to use the Calculated SQL text as you see the FM calc dialog!

                However, you still need valid SQL syntax (for the db that will be reading the query).

                There used to be a query builder in older versions of FileMaker that was easy to use. Now there are several "builders" meant mostly for ExecuteSQL() function, but they might help get the syntax easier.

                 

                beverly

                • 5. Re: sql query variables?
                  kamal1234

                  Hello Mike,

                   

                  I have one follow up question, Your suggestion for query work nicely.

                  How I am going to deal with NULL values here.  In the query we always have single quotes around the values.

                   

                  "UPDATE CPD_COMPWORKSDETAILS SET " &

                  "DESCRIPTION = '" & PL_Competition::Description & "', " &

                  "TITLE = '" & PL_Competition::Title & "', " &

                  "AUTHOR = '" & PL_Competition::Author & "', " &

                  "PUBLISHER = '" & PL_Competition::Publisher & "', " &

                  "PRICE='" & PL_Competition::Price & "' " &

                  "WHERE CWID = '" & PL_Competition::CWID & "'"

                   

                  Thank you

                  Kamal.

                  • 6. Re: sql query variables?
                    beverly

                    it may depend upon the DB (and possibly the xDBC driver) on how to pass NULL

                    The empty field may be able to be used:

                    "Description = '" & PL_Competition::Description & "', " // the '' may work as is

                    or possibly:

                    "Description = " & If( isempty(PL_Competition::Description); "NULL" ; Char(39) & PL_Competition::Description & Char(39) ) & ", " // used Char() to make the single-quote not get lost!

                     

                    NOTE: the NULL will not have any quotes around it. When edited, example above returns: Description = NULL

                    beverly

                     

                    p.s. edited to add example with NULL

                    1 of 1 people found this helpful
                    • 7. Re: sql query variables?
                      djc728

                      Kamal:

                       

                      I'm just going to say that you might want to get a copy of Beverly Voth's "fabulous" PDF on using SQL with Filemaker.  It's a great reference with some excellent examples.  She's on this thread so you should be able to contact her directly.

                       

                      Dom

                      • 8. Re: sql query variables?
                        beverly

                        Thanks Dom! ExecuteSQL() is different in so many ways. The SQL queries to external source may be similar, but not quite.

                        The PDF (and example files) are for ExecuteSQL(). FileMaker SQL and xDBC docs are helpful, too.

                        (may also be available in other languages)

                        Click the Resources menu at the top of this forum.

                         

                        beverly

                        1 of 1 people found this helpful
                        • 9. Re: sql query variables?
                          kamal1234

                          Thank you Beverly and DJC728.

                          Thank you for pointing me towards correct direction.

                          Kamal.