5 Replies Latest reply on Oct 9, 2013 2:05 PM by cresal

    ExecuteSQL local vs remote

    cresal

      Title

      ExecuteSQL local vs remote

      Post

           So I've come across a problem and I'm stumped. I have two versions of my database - one on my local machine, and one hosted on the server with FMS. The one on the server is a copy of the local one, made last night. While the network was down this morning I was futzing with a broken ExecuteSQL script step and got it functioning. So when our network came back up and I had access to the hosted version I went in to the broken script and replaced the executeSQL step with the functioning one. Copy, paste. But it didn't work. I checked many many ways to make sure the two scripts were exactly the same. I tried importing the whole script directly. Tried copy and paste both individual steps and the whole script. I checked to make sure there were no global variables being used that might be different. Everything is exactly the same. But it works on the local version and not the hosted one. 

           tldr: Does the ExecuteSQL script step work differently if the database is accessed locally vs remotely? 

      Screen_Shot_2013-10-09_at_1.18.37_PM.png

        • 1. Re: ExecuteSQL local vs remote
          philmodjunk

               Careful. There is an Execute SQL script step and an ExecuteSQL function. From the screen shot, you are using the Execute SQL function.

               How do the two variables, $Stage and $$CurrentFitureID get values? I can see that $Stage is passed as a parameter but what did you put in the optional script parameter box to pass that value to this script?

               Is there any global field involved in the expressions that assign values to either of these variables?

               Is this script performed from a server schedule on the server?

               You might want to either use FileMaker Advanced's debugger and data viewer or add a show custom dialog to check the value of this variables when you test your script on the server.

          • 2. Re: ExecuteSQL local vs remote
            cresal

                 Derp, yes, i meant function. 

                  

                 Sorry the image has been updated. I was trying the global variable during my testing but went back to the $Figure_ID variable that is set in the same way as $Stage. These are set earlier in the script. 

                  

                 no global fields involved. 

                 triggered from a button in both cases

                 i did use the data viewer, and all the variables come up exactly the same, but on the local version it gives the correct result and on the hosted version it gives ? I've attached a screenie of the dataview from the local side here, and the hosted in the next post

            • 3. Re: ExecuteSQL local vs remote
              cresal

                   Here's when i run the same script on the same record, but from the hosted version

              • 4. Re: ExecuteSQL local vs remote
                philmodjunk

                     That question mark result indicates a syntax error with your SQL. I can't see why that would be the case for the hosted file and not on the local file. Can't help thinking that the hosted file's data model is different in some way such as a field or table with a different name than that on you local copy.

                • 5. Re: ExecuteSQL local vs remote
                  cresal

                       AH HA! 

                        

                       i went and double checked the table names on the relationship graph. this morning i had also updated the ApprovalLog table name, and had not updated it on the hosted version. Thank you for that suggestion! I've been battling this for an hour. Doh!