AnsweredAssumed Answered

FOR UPDATE OF

Question asked by dsburton@mac.com on May 31, 2018
Latest reply on May 31, 2018 by dsburton@mac.com

Greetings! Can someone help me understand the expected behavior of the FOR UPDATE OF (FUO) clause?  My understanding is that when performing a SELECT statement with FUO, the found records should be locked until they are either updated & committed, deleted or a new query is performed.  However, that's not what is happening when I test.

 

The Setup. I have a hosted file that I'm accessing with FMP 16 & 17 ( but get the same results with 15 ).  I'm running a script that performs a SELECT & UPDATE ( via BaseElements ) statement in the same Let function.  There is a pause between the SELECT & UPDATE courtesy of the plugin.

 

Let ( [

     $Title = SQLFieldNameClean ( DashboardToDoJoin::TitleModule );

     $IsHidden = SQLFieldNameClean ( DashboardToDoJoin::IsHidden );

     $Table = SQLParseTableName ( DashboardToDoJoin::_ID );

 

     q = "SELECT " & $Title & "

          FROM " & $Table & "

          WHERE " & $IsHidden & " = " & Dashboard::TestFind & "

          FOR UPDATE  ";

 

     q2 = "UPDATE " & $Table & " SET " & $isHidden & " = " & Dashboard::TestChange & " WHERE " & $IsHidden & " = " &      Dashboard::TestFind & "" ;

 

    r = ExecuteSQL ( q ; "," ; "¶" ) & BE_Pause ( 4000 ) ; // Execute the SELECT Statement & pause for 4 seconds

     r2  =  BE_FileMakerSQL ( q2  ) //Update the same records previously found

 

];

     SQLResult ( r ; q ) & "¶¶" & SQLResult ( r2 ; q2 )

)

 

The  Test. In FMP16 I start the script. During the 4 second pause, in FMP17 I change & commit one of the records from the found set. I would expect to get a record locked error but instead the record commits without issue. Being curious, I tried variations of this test with interesting results. But the big takeaway was a SELECT statement with a FUO clause returns a 301 error if any of the returned records are locked prior to performing the query.  A SELECT statement without the FUO returns the queried data in the same situation.

 

 

Edit: Sorry, I wasn't very clear above. I've updated the calculation to reflect using the ExecuteSQL() function for the SELECT statement. While this is being executed from a Set Variable script step I'm using the ExecuteSQL() function & not the script step. The point of including the UPDATE statement was to reiterate that in between the two statements (the 4 seconds) I expected the found records to be locked. Sorry for the confusion.

Outcomes