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.