1 2 Previous Next 15 Replies Latest reply on Sep 19, 2014 6:48 AM by lkeyes

    Bonzai ExecuteSQL, Global Variable Problem

    lkeyes

      Hi....I'm working through the virtual list problem on page 6-60, Ex. 39 in the Advanced FileMaker training.

       

      Per the exercise instructions I created the following script.

       

       

      =========================

      Set Variable [ $$Array_SalespersonTotals ; Value:ExecuteSQL (

      "SELECT Salesperson, Count(Salesperson), Sum("Total")

      FROM "ORDER"

      WHERE DateOrdered>=? and DateOrdered<=?

      GROUP BY Salesperson" ; "|"; "¶" ;

      Date (1; 1; Year(Get(CurrentDate)));

      Get(CurrentDate)

      ) ]

       

      Set Variable [ $$Another_Global; Value:"My Variable Contents" ]

       

      ==========================

      Upon examining the results in the data viewer, I see $$Another_Global, but don't see $$Array_SalespersonTotals. I'd expect to see the global variable at least, even if their was an error in the Execute SQL statement so it returned a value of '?'.

       

      Any ideas why this is isn't working correctly?

       

      Thanks.

        • 1. Re: Bonzai ExecuteSQL, Global Variable Problem
          BruceHerbach

          This issue might be that the sql returned an empty answer.  As in no error, but no result.  Possibly the sales person had no sales?

          1 of 1 people found this helpful
          • 2. Re: Bonzai ExecuteSQL, Global Variable Problem
            mikebeargie

            Try changing: Date (1; 1; Year(Get(CurrentDate)));

            to: Date (1; 1; 2000);

            just to see if Bruce is right. It could be a sample data thing.

             

            Also, using Count(*) is much faster than counting a specific field in my experience.

             

            Also, you're escaping some field and table names with \" \", but not others. I'd try to be consistent, not sure if that would cause you issues though. Might want to do some side reading on making your SQL statements less breakable using GetFieldName() and such to concatenate in the names.

            1 of 1 people found this helpful
            • 3. Re: Bonzai ExecuteSQL, Global Variable Problem
              lkeyes

              Thanks Bruce and Mike...   I copied and pasted the code from the FileMaker 13 Advanced Training Manual,  ( so it *must* be correct...right?) 

               

              I thought it was interesting that the global variable didn't even show up with a null value, empty string or "?" when using the Set Variable script step. or, is it the case that if the set variable =$$Array_SalespersonTotal isn't initialzed with some value, the script step won't initialize an empty global variable...

               

              Will dig further and post results. 

               

              ---- Larry

              • 4. Re: Bonzai ExecuteSQL, Global Variable Problem
                mikebeargie

                In FileMaker, NULL = "" = blank, etc.. I really wish it would actually return a value for a NULL result.

                 

                So in essence, by setting a global variable with a null value, you are "unsetting" that variable. Similar to how you can unset a global variable with a calculation of "".

                 

                The calc could be working fine, but the data in the sample might be out of date.

                • 5. Re: Bonzai ExecuteSQL, Global Variable Problem
                  BruceHerbach

                  Larry,

                   

                  If you have advanced, you can run the script in the debugger and actaully run the SQL in the dataviewer to see what the result should be.

                  • 6. Re: Bonzai ExecuteSQL, Global Variable Problem
                    beverly

                    Test by setting to " " (text space) first?

                    While the results would appear to be blank, you can enter the field and upon "selection" have something.

                     

                    Or go with another character that might give you a clue. Or this for better communication:

                     

                     

                     

                    Set Variable [ $$Array_SalespersonTotals ; Value:

                     

                    Let (

                    [ _query =

                     

                    ExecuteSQL (

                     

                    "SELECT Salesperson, Count(Salesperson), Sum(\"Total\")

                     

                    FROM \"ORDER\"

                     

                    WHERE DateOrdered>=? and DateOrdered<=?

                     

                    GROUP BY Salesperson" ; "|"; "¶" ;

                     

                    Date (1; 1; Year(Get(CurrentDate)));

                     

                    Get(CurrentDate)

                     

                    )

                    ; result = Case ( _query = "?" ; "error"

                            ; is empty _query ; "nothing found"

                            ; _query

                    )

                     

                    ]; result // returns "error" or "nothing found" or the values

                    ]

                     

                     

                    Beverly

                    • 7. Re: Bonzai ExecuteSQL, Global Variable Problem

                      Hi Larry, Beverley, Mike & Bruce,

                       

                      I'm also seeing a "?" in the Data Viewer when I try to run the ExecuteSQL example. I've changed the first date to ( 1 ; 1 ; 2012 ) to ensure there's plenty of records from the Orders table.

                       

                      Beverley's Let statement is also not compiling with a "table not found" error showing up at the "is empty _query" step.  Making it "isEmpty _query" does not resolve that error for me.

                       

                      I'll be interested to see if any further fixes or adjustments come to light & repair the code .

                       

                      Regards,

                       

                      John

                      • 8. Re: Bonzai ExecuteSQL, Global Variable Problem
                        ariley

                        I think you might have to substitute the "/" for "-" for the SQL date.

                         

                        Also, try the sqldebug custom function. It's useful.

                        • 9. Re: Bonzai ExecuteSQL, Global Variable Problem
                          BruceRobertson

                          The "is empty" part of the result declaration is invalid.

                           

                          Note added parentheses. It should be:

                           

                          result = Case ( _query = "?" ; "error"

                                  ; isEmpty( _query ) ; "nothing found"

                                  ; _query

                          )

                          • 10. Re: Bonzai ExecuteSQL, Global Variable Problem

                            Hi all,

                             

                            Bruce Robertson's correction, plus a closing ")", has helped me to get Beverley's statement working.

                             

                            Many thanks Bruce,

                             

                            John

                            • 11. Re: Bonzai ExecuteSQL, Global Variable Problem
                              beverly

                              Yes, thanks Bruce! I need a 'code checker' on my email like there are spelling checkers.

                               

                              -- sent from myPhone --

                              Beverly Voth

                              --

                              • 12. Re: Bonzai ExecuteSQL, Global Variable Problem
                                lkeyes

                                Two items:

                                 

                                1. Indeed it appears that if you initialize a variable with "" it doesn't initialize the variable at all, so if the SQL statement returns an empty set then it won't show up in the data viewer. Didn't know that. Coming from a 3g programming background where you *had* to declare variables before you initalize them, this is a good thing to know.

                                 

                                2. I pasted the statement into the debugger, and after removing the second parameter, i.e. the final Get(CurrentDate), I did get some results.   Here's the statement.   I'm fooling with this with the 6b_Solution file that is included the FM13 Advanced Training download.

                                 

                                ExecuteSQL (

                                          "SELECT Salesperson, Count(Salesperson),

                                                    Sum(\"Total\") 

                                           FROM \"ORDER\" 

                                           WHERE DateOrdered>=? and DateOrdered<=?

                                           GROUP BY Salesperson" ;

                                 

                                          "|" ; "¶" ;

                                          Date ( 1 ; 1 ; Year (Get(CurrentDate )))

                                 

                                )

                                 

                                This returns data similar to what is shown in the training manual, but with fewer records.

                                 

                                One question about the parameters...  

                                 

                                If you use a ? within the SQL statement, and then add a parameter, at the end of the statement, wouldn't you expect to have a second parameter if you have a second '?' ?

                                If so, then, I would have thought the above statement would throw an error. 

                                • 13. Re: Bonzai ExecuteSQL, Global Variable Problem
                                  erolst

                                  lkeyes wrote:

                                  If you use a ? within the SQL statement, and then add a parameter, at the end of the statement, wouldn't you expect to have a second parameter if you have a second '?' ?

                                  If so, then, I would have thought the above statement would throw an error. 

                                  It seems that if a ? argument doesn't find a parameter by position, it uses the last one.

                                   

                                  This is why

                                   

                                    WHERE theName = ? AND theDate >= ? and theDate <= ?       

                                    " ;  "" ; "" ; "Jill" ; Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) )

                                   

                                  works as intended, but

                                   

                                    WHERE theDate >= ? and theDate <= ? AND theName = ?

                                    " ;  "" ; "" ; Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ; "Jill"

                                   

                                  does not.

                                   

                                  Of course, if for some reasons you can't or won't rely on that behaviour, or find it too confusing, use Let() to put a complicated (looking) parameter expression into a more digestible variable, and use a matching number of ? and parameter expressions.

                                   

                                  Let (

                                   

                                    myDate = Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ;

                                    …

                                      WHERE theDate >= ? and theDate <= ? AND theName = ?

                                      " ;  "" ; "" ; myDate ; myDate ; "Jill"

                                  )

                                  • 14. Re: Bonzai ExecuteSQL, Global Variable Problem
                                    jormond

                                    In FileMaker setting a variable to "" is the opposite of initializing it. That is how you de-declare a variable.

                                    lkeyes wrote:

                                     

                                    1. Indeed it appears that if you initialize a variable with "" it doesn't initialize the variable at all, so if the SQL statement returns an empty set then it won't show up in the data viewer. Didn't know that. Coming from a 3g programming background where you *had* to declare variables before you initalize them, this is a good thing to know.

                                    1 2 Previous Next