10 Replies Latest reply on Jul 25, 2013 10:06 AM by philmodjunk

    ExecuteSQL() - AM I JUST NOT GETTING IT?!?

    JeffMillard

      Title

      ExecuteSQL() - AM I JUST NOT GETTING IT?!?

      Post

           Was so pleased to find this feature...and so amazed at how qwerky it seems to implement.  I built a VERY simple database example trying to do a select with a where clause. Without the where clause $$SQL1/$$RESULT1 works fine but the second query $$SQL2/$$RESULT2  it I get no data and no error.  

           Set Variable [$$SQL1;Value:"SELECT NAME FROM PRODUCTS"]
           Set Variable [$$RESULT1;Value:ExecuteSQL ($$SQL1;",";",")]
           Set Variable [$$SQL2;Value:"SELECT NAME FROM PRODUCTS WHERE NAME = PEAR"]
           Set Variable [$$RESULT2;Value:ExecuteSQL ($$SQL2;",";",")]

           PRODUCTS table has two fields KEY, NAME and records

           1,APPLE
           2,PEAR
           3,BANANA

           First QUERY returns APPLE,PEAR,BANANA

           Second a "?"

           What Gives? Also tried changing where clause to WHERE KEY = 1... no luck.

            

        • 1. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
          philmodjunk

               Your SQL syntax has an error and that's what the very unfriendly and uniformative ? is supposed to be telling you.

               Try this SQL expression:

               "SELECT NAME FROM PRODUCTS WHERE NAME = \"PEAR\""

               Otherwise the SQL engine is trying to find a field named PEAR and can't find it. (\" is how you insert quotation marks inside a quoted string in FileMaker expressions.)

               I recommend reading the SELECT query examples towards the end of the ODBC JDBC Guide you can access via FileMaker Help and also SeedCode's free SQLExplorer.

          • 2. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
            JeffMillard

                 Thanks for the quick response Phil but that doesn't seem to work in the context of formating the whole sql statement into a variable first. I can seem to get it to work if I put the condition text in the parameter section:
                  

            Set Variable [$$SQL2;Value:"SELECT NAME FROM PRODUCTS WHERE NAME = \"PEAR\""]
            Set Variable [$$RESULT2;Value:ExecuteSQL ($$SQL2;",";",")]

            DOES NOT WORK BUT

            Set Variable [$$RESULT3;Value:ExecuteSQL ("SELECT NAME FROM PRODUCTS WHERE NAME = ?";",";",";"PEAR")]

            WORKS.   I thought it better to format the whole SQL statement first so I could see exactly what I was executing but its not clear if this allows me the same conditional formating.  I am re-reading the Guide as you indicated...

                  

            • 3. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
              philmodjunk

                   Just double checked the examples in the JDBC ODBC guide, use single quotes ' in place of \" and it should work.

              • 4. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
                JeffMillard

                     Tried that too... all kinds of funky stuff to get the string to look right. I'm also not seeing any examples in the guide where the select statment is formated first (with conditional operators) and the ExecuteSQL is executed without anything in the parameters section.

                • 5. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
                  JeffMillard

                       Also-- to clarify, I am executing this against a normal Filemaker 12 Table.

                  • 6. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
                    gcatnine

                         Use a single quote

                    Set Variable [$$SQL2;Value:"SELECT NAME FROM PRODUCTS WHERE NAME = 'PEAR' "]

                    • 7. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
                      philmodjunk
                           

                                I'm also not seeing any examples in the guide where the select statment is formated first (with conditional operators) and the ExecuteSQL is executed without anything in the parameters section.

                           Correct. The guide documents the SQL syntax. It was never intended to document the ExecuteSQL funciton call syntax. It just so happens that the JDBC and ODBC queries interact with the same SQL engine as does Execute SQL so these SQL examples (select only) also work with ExecuteSQL.

                      • 8. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
                        philmodjunk

                             I played around with this in the following Demo File: https://dl.dropboxusercontent.com/u/78737945/SQLQuoteDemo.fmp12

                             I got this to work with one small syntax change. The demo file tests this expression via unstored calculation fields in the same table, a different table and in a script.

                             Here's the script:

                             Set Variable [$SQL ; value: "SELECT Products.Name FROM PRODUCTS WHERE NAME = 'PEAR' " ]
                             Show Custom Dialog [ ExecuteSQL ( $SQL ; "" ; "" ) ]

                             Note also that text comparisons in SQL are case sensitive. In FileMaker calculations, "PEAR" = "pear" is true. IN SQL, it is not.

                        • 9. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
                          JeffMillard

                               Good Show Phil!  One of those answers tho that makes one a little less happy because it is so arcane and undocumented.Clearly the parser works differently on constants in the ExecuteSQL function than when parsing variables WITH EXACTLY THE SAME SYNTAX AS THE CONSTANT!

                          • 10. Re: ExecuteSQL() - AM I JUST NOT GETTING IT?!?
                            philmodjunk

                                 Using ? and specifying "Pear" as a parameter did not work for me, got the dreaded ?, until I also modified it to use Products.Name.

                                 But I totally agree on the "arcane and poorly documented" aspects of this. A wizard for building queries built right in as a standard feature of FileMaker would be a huge improvement--one where you can graphically build the joins from your table occurrences and then be able to see and edit the SQL produced and be able to view the data produced by the query.