This example was reproduced with 4Dv11.8 HF3. This requires 4D Server and 4D Client setup. I was unable to reproduce in a stand alone environment.
While working with SQL and 4D, sometimes records would show locked status of blank.
LOCKED ATTRIBUTES returns -1 for the process in addition to the ‘blank’ user process and machine. Even though the documentation says this means you are trying to load a deleted record, this example has an existing record.
There have been a couple of examples on the 4D tech mailing list regarding blank locked records. The locked record appeared to manifest from the SQL engine not ‘letting’ go of the record.
In the following simplified example a SQL query updates a record in a table, which loads another record from another table in the trigger. If that record is not unloaded it remains ‘locked’. The only way I’ve found to unlock this record is to write a project method, make it available to SQL calls and load/unload this record. Regardless of how the ‘blank’ locked record manifest itself the IsRecordLocked should unlock it.
Setup
[Table_1]Field_1 (is alpha)
[Table_2]Field_2 (is alpha)
` the SQL code Begin SQL UPDATE Table_1 SET Field_1='test' WHERE Field_1='a record' End SQL ` [Table_1] trigger code READ WRITE([Table_2]) ALL RECORDS([Table_2]) ONE RECORD SELECT([Table_2]) LOAD RECORD([Table_2]) ` this 'locks' it for SQL server ` without an UNLOAD RECORD it will stay locked
Solution
C_TEXT($result)
` Using the SQL engine to LOAD and the UNLOAD the record works
Begin SQL
SELECT {fn IsRecordLocked('Table_2','Field_2="test"') AS TEXT}
FROM ODBCTest
LIMIT 1
INTO :$result
End SQL
ALERT($result)
IsRecordLocked function
` ----------------------------------------------------
` User name (OS): jrowe
` Date and time: 05/20/11, 12:26:45
` ----------------------------------------------------
` Method: IsRecordLocked
` Description
` Unlock any record via the SQL engine
`
` Parameters
` ----------------------------------------------------
`$1 table name
`$2 where clause supports any type of where clause (executed with QUERY BY SQL)
`$0 returns string "False" if not locked (or success), "" if error, otherwise locked information
`Error handling
ON ERR CALL("Generic_Error_Proc")
C_LONGINT($process)
C_TEXT($4Duser;$sessionUser;$processName)
C_POINTER($tablePointer)
C_TEXT($1;$2;$returnText;$tableName)
$returnText:=""
` cycle through the tables to get a pointer
For ($tableNumber;1;Get last table number)
If (Is table number valid($tableNumber))
$tablePointer:=Table($tableNumber)
$tableName:=Table name($tableNumber)
If ($tableName=$1)
` now execute the where clause
QUERY BY SQL($tablePointer->;$2)
LOAD RECORD($tablePointer->)
$returnText:="Read only: "+String(Read only state($tablePointer->))+" Records: "+String(Records in selection($tablePointer->))+"\n"
`We expect an update statment to only have one result
If ((Locked($tablePointer->)) & (Records in selection($tablePointer->)=1))
LOCKED ATTRIBUTES($tablePointer->;$process;$4Duser;$sessionUser;$processName)
$returnText:=$returnText+"Process ID: "+String($process)+"\n"
$returnText:=$returnText+"4D User: "+$4Duser+"\n"
$returnText:=$returnText+"Session User: "+$sessionUser+"\n"
$returnText:=$returnText+"Process Name: "+$processName+"\n"
Else
$returnText:="False" ` ie this is not locked, or was locked by sql engine
End if
UNLOAD RECORD($tablePointer->) ` this will free the locked status
`lets break the loop so we don't keep processing
$tableNumber:=Get last table number
$0:=$returnText
End if
End if
End for
`Error handling
ON ERR CALL("")
Source code replicating this problem available upon request.
Cowboy Coder 