6 Replies Latest reply on Feb 1, 2016 12:23 PM by Extensitech

    Delayed executeSQL result?


      I've found a workaround that doesn't use executeSQL, but I'm not sure why this would happen.


      Using the function below, I'm trying to query a table of hours to get the most recent clockout time today, for the current user. It works, but sometimes, if the user has just added a clockout time, there's a delay before the result of this expression updates. I triple-checked that the change is committed. I tried a refresh with flush. Where I had it in the data viewer I clicked refresh there, too. Regardless, I get the clockout before last for several seconds, and then I get the correct (most recent) result.

      (Background: We're using this to determine the next clock in time, and whether to ask the user, basically, "Where've you been?" so we can catch undocumented breaks or where the user just forgot to clock in to the next work order.)


      Anyhow, here's the function. Can anyone spot what I'm doing wrong, or point me to the reason why this would behave, well... sluggishly? This was easy enough to work around here, but I'm concerned that I might be missing some detail about esql that could bite me in a more significant way in the future...



      Let ( [


      ; dat = GetAsDate ( Get ( CurrentHostTimestamp ) )

      ; tim = GetAsTime ( Get ( CurrentHostTimestamp ) )

      ; acc = $$ACC_cur


      ; now = Timestamp ( dat ; tim )

      ; sql =


      "SELECT (" & Quote ( "A_lm_End" ) & ")"

      & "¶FROM " & Quote ( "HOU__Hours" )

      & "¶WHERE " & Quote ( "A_d_End" ) & " = ?"

      & "¶AND " & Quote ( "A_i_End" ) & " <= ?"

      & "¶AND " & Quote ( "_ACC" ) & " = ?"


      ; lst =


      ExecuteSQL (


        ; ""

        ; ""

        ; dat

        ; tim

        ; acc



      ] ;


      GetAsTime ( RightWords ( lst ; 1 ) )




      Chris Cain


        • 1. Re: Delayed executeSQL result?

          All your flushes and commits regard only the current user, in a multiuser environment you can't have that for all the users.


          Troi Activator has some bold claims, like


          • get someone out of a record, so that it is no longer locked.


          I don't know if and how they can be turned into something useful, but you might consider it.

          • 2. Re: Delayed executeSQL result?

            siplus wrote:


            All your flushes and commits regard only the current user, in a multiuser environment you can't have that for all the users.


            Understood, but in the use case where I'm seeing this, I'm the user that added the "missing" clockout. It's my own, committed, update that's missing, not another user's.


            I could actually understand if another user had added the clockout and I couldn't "see" it right away.



            • 3. Re: Delayed executeSQL result?

              Yeah, but the xSQL problem – as I know it – is that it acts upon the whole table/database, and as long as you have an uncommitted record (yours or somebody else's) it will hit the brake pedal strongly.

              • 4. Re: Delayed executeSQL result?

                Right.  I can make this happen when I'm the only user and I've committed my changes, though.

                • 5. Re: Delayed executeSQL result?

                  I have noticed the same problem and my users have too.


                  Just as in your situation, all additions and changes are committed and sometimes a simple sum via an eSQL-calc omits the last entry/entries at that same workstation. A minute or so later, the correct result is shown from the same calculation.


                  Maybe it has something to do with the cache and local temp-files and flushing the cache to the fm-server. When I change and commit a record on WS-x, it takes a while before that change is visible on WS-y and I suspect that has the same root-cause

                  • 6. Re: Delayed executeSQL result?

                    Good to know. I figured there was some caveat I was missing. I'll need to do some more testing (in my spare time) but for now I'll just be cautious with aggregate esql functions when up-to-the-second results are required.


                    I still wonder if this is a known issue. It seems pretty obscure, though, and easy enough to work around....


                    Chris Cain