8 Replies Latest reply on May 26, 2016 7:00 AM by user19752

    executeSql with multiple scripted dynamic parameters

    holstermedia@gmail.com

      I'm trying to build a query builder in filemaker. So then the queries can be saved and recalled nicely...

      The query side is working fine but I'm struggling to get multiple parameters to work.

       

      Here's my calculation:

       

      Let (

      [

      ~q=myTable::queryCalc;

       

       

      // test 1

      ~p1="%red%";

      ~p2="%blue%";

      ~p=~p1 & ";" & ~p2

      // fails

       

      // test 2

      ~p ="\"%red%\";\"%blue%\""

      // fails

       

      ];

      ExecuteSQL (~q; "" ; ""; ~p )

      )

       

       

      this currently fails with no ?

       

      If I replace:

      ExecuteSQL (~q; "" ; ""; ~p )

      with

      ExecuteSQL (~q; "" ; ""; "%red%";"%blue%" )

      it works...

       

      so why? in test 2 ~p is exactly "%red%";"%blue%" with all the quotes and semicolons!

        • 1. Re: executeSql with multiple scripted dynamic parameters
          beverly

          We don't see your ~q (queryCalc). So it may be difficult to determine.

          You can have a FIELD hold the entire let statement and it can be a calculated field. But you cannot have a "dynamic parameter" like you have.

          You might try something like this:

          Evaluate ( "ExecuteSQL ( ~q ; \"\" ; \"\" ; " & ~p & ")" ) // UNTESTED!

          or

          Evaluate ( myQueryField ) // this works and has been tested with my queries

          I use this last method in my example files. All queries are in a text field which is used to set the 'result' text field.

           

          beverly

          • 2. Re: executeSql with multiple scripted dynamic parameters
            holstermedia@gmail.com

            thanks - I'll stop trying to get that calculation to work then!

            I think I understand - so I have the query sitting in a text field (myQueryField) and then just use that to get the 'result' field.

            So it's a matter of syntax and escaping in that calculation for myQueryField.

             

            my parameters calc with 2 examples:

            "\"" & Substitute ( List ( wcSQLentry::text) ; ¶ ; "\";\"" ) & "\""

            1 - "%red%";"%yellow%";"%blue%"

            2 - "%red%";"10"

             

            my query calc with 2 examples:

            "select wcStock.stock_id from wcStock where " & Substitute ( List ( wcSQLentry::query ) ; ¶ ; " and " )

            1 - select wcStock.stock_id from wcStock where wcStock.title like ? and wcStock.title like ?

            2 - select wcStock.stock_id from wcStock where wcStock.title like ? and wcStock.sale_status = ?

            • 3. Re: executeSql with multiple scripted dynamic parameters
              coherentkris

              Pattern matching can be done with the LIKE sql operation but you are going to run into problems trying to use vars because i don't think LIKE can use variables and "%red%" is not equal to '%red%' (single quotes vs double quotes).

               

              ExecuteSQL ( "SELECT column FROM table WHERE column LIKE ?" ; "" ; "" ; "%this_text%" )

               

              will fail for either reason.

              • 4. Re: executeSql with multiple scripted dynamic parameters
                beverly

                yes, it's a matter of evaluation. Your substitute worked, because it evaluated the string and placed the ~p into the ~q. The Evaluate() function - as you've noticed - with the correct concatenation and placement of quotes (escaped as needed), does it's job and makes a 'normal' query that can be executed.

                 

                You might want to review the sql 'solutions' that help you build queries. They truly are 'dynamic' (allowing changes) and then get processed.

                (this last one has a great online documentation with more information: http://www.seedcode.com/pmwiki/pmwiki.php?n=SQLexplorer.SQLexplorer)

                 

                beverly

                • 5. Re: executeSql with multiple scripted dynamic parameters
                  beverly

                  co is correct. IF the values are parameters, though, FM's ExecuteSQL() function will take care of the single quotes as needed. So care must be taken if VARs are used as the parameters. I may need to cast with

                  GetAsText ( $myTxt )

                  GetAsNumber ( $myNum )

                  for example, when using them this way (as parameters in ExecuteSQL queries)

                  beverly

                  • 6. Re: executeSql with multiple scripted dynamic parameters
                    holstermedia@gmail.com

                    ah - the 'copy calculation' script in the seedcode example looks like it'll have exactly what I need...

                    thanks for the pointers...

                     

                    I've got it all working (in a temp hard coded way) for now and will expand it later using this - and it's great to see a query builder working in filemaker! Now users can create, save, share and manage finds properly!

                     

                    Example attached:

                     

                    Screen Shot 2016-05-26 at 12.17.49.png

                    • 7. Re: executeSql with multiple scripted dynamic parameters
                      beverly

                      Well, yes and no. If you use this to FIND the id of records with ExecuteSQL() and then use a method (GTRR, for example) to actually display those records, yes. If you just display "found information" (query results) in a text field (or perhaps print a report of the field), then it's mildly useful for "find". Or is if you use query+virtual list/table/report, then it's a yes!

                       

                      It is possible to use native (scripted finds) with such as your example, but maybe it's more fun to use ExecuteSQL(). It all depends on:

                      • how large is the queried dataset

                      • how will you use the results

                      • the ability to create the SQL 'code'

                       

                      The query builders are handy! Keep in mind they may not be able to get everything you want. ExecuteSQL() may be limited in some ways. There are work-arounds involving queries+virtual list/tables+queries. And there are many SQL plug-ins which give you more than you can do with just ExecuteSQL(). And there is always FM as an ODBC/JDBC source and use another app to make the queries in 'real SQL'.

                      beverly

                      • 8. Re: executeSql with multiple scripted dynamic parameters
                        user19752

                        It works.

                        In parameter "%this_text%" doesn't contain double quote in the value.