Procedures often modify the data in a database. When a procedure modifies data in a shared database record locking is in effect just as it is when data is modified manually.
As a procedure runs, Panorama will automatically lock and unlock records as necessary. This is called implicit record locking. Implicit record locking follows two simple rules — 1) The record is locked whenever any field in the record is about to be modified, and 2) The record remains locked until the procedure moves to another record, moves to another database, saves, or explicitly unlocks the record (see next section). It will also automatically unlock if the procedure ends with a “secret” window active.
The most common method for a procedure to modify a record is with an equation. For example, consider the equation below which adjusts the quantity level in an inventory database. This example assumes that Qty is a numeric field in the current sharable database, and SaleQty is a field or variable that contains a numeric value.
Qty=Qty-SaleQty
When Panorama is running a procedure and encounters an equation that modifies a field in a sharable database it first attempts to lock the current record (if it is not already locked). To do this, Panorama contacts the server to see if anyone else has locked this record. If not, the server responds that everything is ok and updates the record that is about to be locked (see Editing Data and Record Locking). It then calculates the formula and modifies the Qty field. Since the data in the local copy of the record is updated as part of the record locking mechanism, the formula result is guaranteed to be based on the most recent data. (For example suppose the quantity starts at 100. Then Bob subtracts 11 and Kelly subtracts 14. The final quantity will always be 75, not 89 or 86.)
But what if the record is already locked by another user on the network? In that case the procedure will stop with an error. If you don’t want it to stop, you can trap the error with if error or the try statement (see Error Handling), for example:
Qty=Qty-SaleQty
if error
... code to deal with the fact that record is locked
endif
Here is the alternate method using the try statement.
try
Qty=Qty-SaleQty
catch
... code to deal with the fact that record is locked
endcatch
Before this equation begins running, Panorama automatically locks the current record. That way another user cannot change the Qty value while the equation is being calculated, possibly causing a calculation error. When the calculation is complete, the record remains locked until Panorama moves to a different record (or you can explicitly unlock the record as described in the next section).
In the last section, you saw how a Panorama will implicitly lock and unlock a record during a procedure. However, sometimes the programmer may want to control exactly when and how records are locked and unlocked. The programmer may also want to control what happens when a record is already locked by another user.
A good method for locking in code is the lockcurrentrecord( function. This function attempts to lock the current record, and returns true if it succeeds and false if it fails (because someone else has already locked the record).
if lockcurrentrecord()
Quantity = Quantity-1
unlockrecord
else
message "Please try again later, someone else is editing this record."
endif
As shown in the example above, the UnlockRecord statement unlocks the current record. If the record wasn’t locked, the UnlockRecord statement does nothing. Remember, a locked record is also unlocked automatically whenever Panorama moves to another record in the current database or any other database.
If you want Panorama to wait until the record can be locked, use the LockRecord) statement. If the current record is not already locked by someone else on the network, the record is locked and the procedure continues immediately. If the current record is already locked, the LockRecord statement will wait until the record is available. While it is waiting the statement displays a dialog telling the person running the procedure to wait. If the person doesn’t want to wait, he or she can press the Cancel button. If the user presses the Cancel button the procedure stops and an error dialog appears. However, the programmer can intercept this error by putting an if error statement after the LockRecord statement. In that case when the user presses the Cancel button, the procedure will continue running with the first statement after the if error statement. Here is an example showing how this statement can be used.
let ReceivedQuantity = ""
gettext "How many "+Item+" were received?",ReceivedQuantity
ReceivedQuantity = val(ReceivedQuantity)
lockrecord
if error
message “These items could not be added to the database quantity. “+
“Be sure to process them again later.“
rtn
endif
Qty = Qty+ReceivedQuantity
The lockcurrentrecord( function was not available in Panorama 6 or earlier. In those older versions a similar capability was provided by the LockOrStop) statement, which is still available in today. Like the lockcurrentrecord( function, the LockOrStop statement attempts to lock the current record. If the current record is not being edited by someone else, the record is locked and the procedure continues. If the current record is already locked, the procedure stops immediately and an error dialog appears. However, the programmer can intercept this error by putting an if error statement after the LockOrStop statement.
Here is an example of a procedure that uses LockOrStop to explicitly lock and unlock the current record.
let ReceivedQuantity = ""
gettext "How many "+Item+" were received?",ReceivedQuantity
ReceivedQuantity = val(ReceivedQuantity)
LockOrStop
if error
message "Could not update inventory, try again later."
stop
endif
Qty = Qty+ReceivedQuantity
UnlockRecord
For new code, we recommend using the lockcurrentrecord( function instead, like this:
let ReceivedQuantity = ""
gettext "How many "+Item+" were received?",ReceivedQuantity
ReceivedQuantity = val(ReceivedQuantity)
if lockcurrentrecord()
Quantity = Quantity+ReceivedQuantity
unlockrecord
else
message "Could not update inventory, try again later."
endif
The lockedrecordlist statement returns the number of records locked in the current database, and also returns a list of those records (listed by record ID number). This statement has one parameter, the name of a field or variable to receive the list. The result will be formatted like this:
Ok: 2 records locked
378
864
This procedure displays the number of records locked in the current database.
local locks
lockedrecordlist locks
message firstline(locks)[4,-1]
The forceunlockallrecords statement will tell the server to unlock ALL of the records in the current database. You should only use this statement if a client has disconnected from the network or crashed, and no other clients have any locked records at the moment. If another client had locked any of these records, they will be unlocked behind their back. They will get an error message when they try to unlock the record, and their changes will not be saved. This makes most users very unhappy, so be very careful before using this command.
The forceunlockrecord statement will tell the server to unlock the current record in the current database. You should only use this statement if a client has locked this record and disconnected from the network or crashed. Make sure that you know that it is the disconnected client that locked this record. If another client that is still running is the one who locked this record, they will be unlocked behind their back. They will get an error message when they try to unlock the record, and their changes will not be saved. This makes most users very unhappy, so be very careful before using this command.
The forcelockrecord statement tells the server to lock this record (the current record on the client) on the server, as if another client had locked the record. Once this is done, the only way to unlock the record is with the forceunlockallrecords or forceunlockrecord statement. This statement can be used for debugging if you don’t have two client computers available for testing.
When using a shared database, Panorama normally updates the server with full record locking every time any change is made to the database. Sometimes, however, you may want to make temporary changes to a database simply for the purposes of data analysis. For example, perhaps you need to use the formulafill statement to prepare data for printing, but will no longer need the calculated data after the report is printed. If you don’t want to keep these changes you can temporarily turn off record locking, essentially turning a shared database back into single user mode for a short while.
To temporarily disable record locking use the serverupdate statement.
serverupdate truefalse
This statement has one parameter: truefalse. This parameter indicates whether you want to disable or enable record locking and server updates. To disable updates and record locking, use “off”, “no”, or “false”. To enable updates and record locking, use “on”, “yes”, or “true”.
The example below turns off server updates, then uses the formulafill statement to calculate P/E ratios. Since server updates (and record locking) are turned off, the formulafill will be very fast. In this case we don’t need to keep the calculated P/E ratios after we are finished printing, so it is acceptable to turn off the server update.
serverupdate "off"
field Ratio
formulafill Price/Earnings
print dialog
serverupdate "on"
forcesynchronize /* restore original data from server */
The last line in this example may not be necessary. If you don’t ever use this procedure for permanent changes to the database for permanent data, you can leave this line out.
You can only turn server updates off for a short time. We recommend that you explicitly turn them back on as soon as possible, but if you don’t, they will automatically turn back on when the procedure finishes, or if the procedure switches to another database (via the window or openfile statements).
The info(“serverupdate”) function can be used to check if server updates are currently enabled. The function returns the true if the server update option is currently turned on (this is the default). The example below shows how this function can be used. The procedure turns off server updates and performs a formula fill. It then turns server updates back on, but only if they were already on before this procedure began.
let sup = info("serverupdate")
serverupdate "off"
field Z
formulafill A+B
if sup
serverupdate "on"
endif
If serverupdate was off before this procedure was called, it will remain off.
See Also
History
| Version | Status | Notes |
| 10.2 | New | New in this version. |