Can I skip locked records?

[taylormade]

If I'm doing a FOR EACH thru a table that has a locked record (by a user) is there any way to either look at it only or skip it and move on?
( I can't access the program that is locking the record)

 

[mpowell_esq]

Greetings,
Sorry I can NOT give you any example syntax, because I am not by my PSC version presently. Though this stikes myself as being fairly intuative and obvious. From the-top-of-my-head yes you can. You just need to query the database status field of the record. You require an IF statement clause in your FOR EACH block. As PSC is iterating through the records on the FOR EACH, once the record is moved into the buffer conditionaly check the database status. Once the record is in the buffer, do a check on the appropriate _field, this field will have the current status. All the _field's are PSC working / database fields.

 

[taylormade]

thank you.
I will look at this.

 

[jongpau]

What is your for each meant to be doing? Is it a report or some query that needs to update your data?

If your for each is meant to do something that does not update your database contents (including deleting records), use the NO-LOCK option on your for each and you should be laughing. For instance:

FOR EACH <TableName> NO-LOCK
WHERE <WhereClause>:
  DISPLAY <TableName>.<FieldName1> <TableName>.<FieldName2>.
END.

The above will run through the records and display them, even the locked ones. If your for each is meant to update the database contents things get a lot trickier. Especially because you will have to ask yourself about the "what if". If your query updates the database and you make it skip locked records you may end up with a stack of bad data in your db, which I doubt you will want to happen.

Now, if you want to get deep into stuff and depending on your Progress version, you may also be able to tap into the hidden tables of your database and read the lock table. This allows you to see what records are locked and even which user has applied the lock. Let me know if you need an example of how to do that (I will have to dig into some of my code).

HTH

 

[nsanthosh]

dear jongpau,

can you pls dig into your code and through some light on the issue of locked records updation.

regds,
santhosh

 

[jongpau]

Will do some time tomorrow. I have some other things to sort out first (hope you do not mind).

As far as your last message. Updating locked records is not ever possible and this would not be a good idea at all if it were. All I may be able to do is offer some code that allows you to see if a record is locked and by what userid (as known to Progress). Whether this code works or not depends on your Progress version.

If you have really serious record locking issues you should take this on with the supplier of your application (I understand you do not have source code, is that correct?).

Will get back to you asap with some code snipplets.

 

[nsanthosh]

pls take your time to find the code segment (to find the records locked by the userids).

regds,
santhosh

 

[jongpau]

Hi,

Try something like this:

DEF INPUT PARAMETER ipRecid AS RECID NO-UNDO.

FIND FIRST _lock 
WHERE _lock._lock-recid EQ INTEGER(ipRecid) 
NO-LOCK NO-ERROR.  

IF AVAILABLE _lock 
THEN MESSAGE "The record you are trying to open is in use by ":L + _lock._lock-name + " (":U + _lock._lock-usr + ")":U
VIEW-AS ALERT-BOX.

As you can see it queries the hidden "_lock" table based on the recid of a record. I am not 100% certain, but I think the _lock table was introduced some time in or around Progress version 9 - am sure someone out there can bash my head if I am wrong :awink:. The little "procedure" above can be used in combination with the "EXCLUSIVE-LOCK NO-WAIT" AND "LOCKED" features of Progress (see documentation). Of course it can also be changed so you can find all records locked by a particular user id etc. Is all up to your own ingenuity.

One little hint: Be VERY careful when you start playing with the hidden tables. Always and I mean ALWAYS use NO-LOCK when you query these. Never ever ever just delete or change anything that is in them etc etc etc. I can only begin to imagine what will happen when you start to do anything like that (but am pretty sure your whole application will die a dreadful death)

HTH

 

[nsanthosh]

Thanx. It works really fine.

And thanx for the caution.

Rgds,
Santhosh

 

[RalphV]

Hi Paul,

It is much easier to figure out if a record is locked. See next example

DEFINE BUFFER bf_<TABLE> FOR <TABLE>.
FOR EACH <TABLE> NO-LOCK:
DISP <TABLE>.
DO TRANSACTION:
FIND FIRST bf_<TABLE>
WHERE ROWID( bf_<TABLE> ) = ROWID( <TABLE> )
EXCLUSIVE-LOCK NO-ERROR NO-WAIT.

IF LOCKED bf_<TABLE>
THEN DISPLAY "LOCKED".
ELSE DO:
/* CHANGE */
END.
END.
END.

I hope it is usefull.

Regards,

Ralph.

 

[jongpau]

It is much easier to figure out if a record is locked. See next example

Yes of course, I did say to use my sample in combination with NO-WAIT and LOCKED.

Thing is that your construction (which is standard) only shows the record is locked and does not allow you to see who actually locked the record. That's why I created code similar to what I posted before. In some situations you will actually want to know who locks records. Afterall, just knowing that a record is locked does not allow you to go see the culprit an kick his/her you-know-what :biggrin: - especially if records are locked for a long time or someone locks a whole batch of records (which I have seen happen with bad code and silly programmers).

Also, playing with the code you could create a query that shows all the records that are locked in your db, how many locks exist, how many each person (userid) locks etc etc. This information can be handy to have when you develop or work with an older system that has locking issues. Of course, there is always promon to do this, but being able to do this from within the application or development environment itself can have some advantages as well.