7 Replies Latest reply on Feb 14, 2015 12:12 PM by malachydevlin

    executesql

    malachydevlin

      Hi, I have an oddity in ExSQL.

      If I make a calculated field = to e.g

      ExecuteSQL ("SELECT SURNAME FROM CUSTOMERS WHERE ID=4" ; "" ;""  )

      then I get the surname "Bloggs".

       

      If (in a script step) I say set field (or even variable, show message) = to

      ExecuteSQL ("SELECT SURNAME FROM CUSTOMERS WHERE ID=4" ; "" ;""  )

      the exact same statement.

       

      I get no error but only "?"

       

      strange?

        • 1. Re: executesql
          BruceHerbach

          The issue is you need fully qualified field names in a script.   As a field calculation you can use the shortcake because you are looking at a field in the table. 

           

          HTH

           

          Sent from my mobile device... Please excuse typos.

          • 2. Re: executesql
            BruceHerbach

            You might try using Seedcode's SQLexplorer. I find it very helpful.

             

            HTH

             

            Sent from my mobile device... Please excuse typos.

            • 3. Re: executesql
              erolst

              Bruce Herbach wrote:

               

              The issue is you need fully qualified field names in a script.

               

              For a FileMaker field reference – not for a field reference within the SQL statement (but, of course, you'd need it in a parameter).

              • 4. Re: executesql
                erolst

                malachydevlin wrote:

                I get no error but only "?"

                 

                '?' is an error, which indicates that the statement is syntactically wrong, rather than couldn't find a result.

                 

                Are you sure it is the exact same statement in both cases? The SQL statement in ExecuteSQL() works context-free, and since you're not using any Filemaker references (which are evaluated in the current context) as a parameter but a literal value, it should work just the same.

                • 5. Re: executesql
                  planteg

                  Hum,

                   

                  I may be off a bit, but it looks to me that ExecuteSQL() does not like parameters passed in the query. In you example, that would mean having the query as ""SELECT SURNAME FROM CUSTOMERS WHERE ID=?" and the substitute 4 for the parameter. I sometimes had the "?" when I was giving the parameter right in the query.

                   

                  Maybe ExceuteSQL() returning "?" means use ? for your parameters . I hope that in 14, we will be able to get the real error message somehow. I know someone described a way to get that message in the Data Viewer, but I never succeeded with that, unofrtunately.

                  • 6. Re: executesql
                    electon

                    It may well be a typo in the expression, gets me every time.

                    In eSQL data types must match for the passed parameter and field on which the WHERE clause is used.

                    If you're using it the way planteg explained, then Filemaker will take care of that for you.

                    Anyway, I think we're not shown the real code here, unless the use case is always about id=4.

                    You could wrap the eSQL within EvaluationError () function and look what error that gives. It sometimes returns errors not shown in the filemaker error list. Evaluating in Data Viewer may help until you get it working, then copy and paste takes care of that for me.

                    • 7. Re: executesql
                      malachydevlin

                      Well I got it, funny how you can miss some simple things.

                      To answer the replies.

                      Yes its the exact same query and none of the excellent suggestions worked.

                      problem was that the table is from a separate data file and is called "Customers" so that's why it worked in a calculated field.

                       

                      HOWEVER in my front end the reference in the relationship graph to the field was called "MyCustomers" so that's why it couldn't find such a table. duh!

                      Explains why it works in the back end..

                      so thanks guys for helping and sorry I missed this and hopefully others will watch out for it.