Cowboy Coder

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.

Read Full Post »