Answered Work with VST Tables

Hi guy's

As said in my previous post a "Lock on the run" :

What I can do:
Manually I can write request to check the data.

What I wanna do know:
I wanna prompt automatically the all the field of the row defined byt the RECID/ROWID.
I don't manage to do it because I don't know how to use the string variable of the _File VST table.

This is my code:

Code:
FOR EACH _connect WHERE _connect._connect-usr <> ? NO-LOCK:
    CREATE silvcon.
    BUFFER-COPY _connect TO silvcon . 

    IF silvcon._connect-wait <> " -- "  THEN DO:
        CREATE silvtrans.
        ASSIGN
            silvtrans.transid = silvcon._connect-transid
            silvtrans.irecid  = silvcon._connect-wait1.
        
        RUN 99-getrowid(silvtrans.irecid, OUTPUT cRowID). /* This part of the code don't work */
        silvtrans.cRowid  = cRowid.
        silvtrans.rRowid  = TO-ROWID(cRowid).
     
          /* What I want is there */
          /* With _Connect._Connect-ReciD how can I find the corresponding table and all the field and there value ? */
     
    END.

END.
 

TomBascom

Curmudgeon
_connect-wait2 contains the table number if _connect-wait = "REC".

_connect-wait <> " -- " is NOT what you want -- you want _connect-wait = "REC"

Why are you making a copy of _connect? That seems like a pointless waste of CPU time and memory.

The complete definition of the _connect VST: OpenEdge 11.7 Documentation
 

cj_brandt

Active Member
quickly read through your previous post, guessing there is an issue with Q'd record locks.

There are a few posts on ProgressTalk that talk about scanning the _Lock table to find the connection holding a lock that has other connections Q'd behind it. There should be a few warnings sprinkled in with those posts about _Lock.

If you are stuck on OE 10, I'd recommend applying service pack 08 on 10.2B, there is a lot of stuff added since 10.2B05.
 
If you are stuck on OE 10, I'd recommend applying service pack 08 on 10.2B, there is a lot of stuff added since 10.2B05.

We have planned the upgrade to sp08.

_connect-wait2 contains the table number if _connect-wait = "REC".

_connect-wait <> " -- " is NOT what you want -- you want _connect-wait = "REC"

Why are you making a copy of _connect? That seems like a pointless waste of CPU time and memory.

The complete definition of the _connect VST: OpenEdge 11.7 Documentation

Ok I will change that.

How you used the table number do make a query on the define table ?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
How you used the table number do make a query on the define table ?
First get the table name:
find dictdb._file no-lock where _file._file-number = /* the number you have obtained */.
The name of the table is in _file._file-name.

Then you can write a dynamic query to read that table based on its name and some WHERE clause you provide.
 

TomBascom

Curmudgeon
I'm trying to "read between the lines" and speculate about what you are trying to achieve.

As best I can tell you seem to think that a session that is being blocked by a record lock has "corrupt" data in some field of the record being locked? So you are seeking to write code which will allow you to inspect such records?
 
I'm trying to "read between the lines" and speculate about what you are trying to achieve.

As best I can tell you seem to think that a session that is being blocked by a record lock has "corrupt" data in some field of the record being locked? So you are seeking to write code which will allow you to inspect such records?

Yes. I want more data about my issue. Like that I will be able to improve my code in the best way.
 
So I'm out of idea to find my mistakes and how to continue....

Code:
    DEFINE VARIABLE hQuery  AS HANDLE      NO-UNDO. /* Query  dynamic */
    DEFINE VARIABLE hBuffer AS HANDLE      NO-UNDO. /* Buffer dynamic */
    DEFINE VARIABLE hField  AS HANDLE      NO-UNDO. /* Champ  dynamic */


    DEFINE VARIABLE iCmpt AS INTEGER     NO-UNDO.


    IF BR-TT1:NUM-SELECTED-ROWS <> 0 THEN DO: /* silverdb */
            
        CREATE BUFFER hBuffer FOR TABLE(TT1.tablename) .
        CREATE QUERY hQuery.

        hQuery:SET-BUFFERS(hBuffer) . 
/* I'm not sure if -> " WHERE RECID(" + TT1.tablename + ") = " + STRING(TT1._lock-recid)  is write correctly
        hQuery:QUERY-PREPARE("FOR EACH " + TT1.tablename + " WHERE RECID(" + TT1.tablename + ") = " + STRING(TT1._lock-recid) + " NO-LOCK"). 
        
        hQuery:QUERY-OPEN.
        REPEAT:
            
            IF hQuery:QUERY-OFF-END THEN DO:
                MESSAGE "nothing"
                    VIEW-AS ALERT-BOX INFO BUTTONS OK.
                LEAVE.
            END.
          
           /* There if my request find some entry, I want to displayed the field value dynamicly but I don't know how to exactly do it.... */

            hQuery:GET-NEXT.
        END.
        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.
        DELETE OBJECT hBuffer.

    END.
 

Cringer

ProgressTalk.com Moderator
Staff member
There's a lot wrong with your code. And I don't know what the answer is. Also telling you the fix probably won't help you learn. So, are you getting errors when you run that? What are the errors? What is that telling you?

Also, for a dynamic query, it's better, in my opinion to do:
Code:
hQuery:QUERY-OPEN. 
DO WHILE hQuery:GET-NEXT:
END.

If there's no records then nothing happens (you can check hQuery:NUM-RESULTS EQ 0for your error message). And you don't need to handle the GET-NEXT later down the line. Plus you know, if there's results, then you'll have the first result in the buffer immediately rather than having to do a GET-FIRST or whatever.
 
Hi, in fact i prefer the DO WHILE method.

For the next of my code I found the way to use my handle buffer and mu handle field like this :

Code:
 DO WHILE hQuery:GET-NEXT :
            DO iCmpt = 1 TO hBuffer:NUM-FIELDS:
                hField = hBuffer:BUFFER-FIELD(iCmpt).
                MESSAGE hField:NAME SKIP
                        STRING(hField:BUFFER-VALUE) 
                    VIEW-AS ALERT-BOX INFO BUTTONS OK.
            END.
        END.

Thank you for your help.

I really need more knowledge of this powerfull language and db tools . :)
 

TomBascom

Curmudgeon
I'm trying to "read between the lines" and speculate about what you are trying to achieve.

As best I can tell you seem to think that a session that is being blocked by a record lock has "corrupt" data in some field of the record being locked? So you are seeking to write code which will allow you to inspect such records?

Yes. I want more data about my issue. Like that I will be able to improve my code in the best way.

Locking issues have virtually nothing to do with the data in the record. You do not have lock conflicts because of corrupt data. You have lock conflicts because two or more sessions are attempting to lock the same record at the same time.

There are two distinct kinds of lock conflicts:

1) Expected lock conflicts. These are normal. You expect them to happen because it is the nature of your application that the resource being accessed needs to be locked and you *expect* that there will be times when you have to wait for access to that resource. Since you expect it you have coded for it by using a strategy of checking to see if the record is AVAILABLE or LOCKED in appropriate places and acting accordingly (maybe you wait and retry for instance).

2) Unexpected lock conflicts. These occur when you do not think a record should be locked by another session and yet it is. You can (and should) code defensively by by adding more checks for AVAILABLE and LOCKED.

3) Yes, I know that I said that there are *two* types ;) Forgive me if I am wrong but you probably got here because you are used to thinking in terms of single user applications. It takes some time to wrap your head around multi-user access and record locking. A long time ago, in a galaxy far, far away I went through that. I wrote a lot of really bad code because of that. A lot of that code had to be re-written and it was kind of painful. That is where the following rules came from.

Both sorts of lock conflicts can be greatly reduced by following some very simple rules in your code:

1) *ALWAYS* specify a lock type. *NEVER* allow a default SHARE-LOCK. In other words code like this:
Code:
for each order NO-LOCK:
  find customer NO-LOCK where customer.custNum = order.custNum no-error.
  /* do something */
end.

NOT:
Code:
for each order: /* defaults to SHARE-LOCK, other sessions will be unable to get an EXCLUSIVE-LOCK while this runs... */
  find customer where customer.custNum = order.custNum no-error.  /* also defaults to SHARE-LOCK... */
  /* do something */
end.

2) *ALWAYS* perform create/update/delete operations within a strong-scoped FOR block with a named buffer.
Code:
define buffer update_customer for customer.
do for upd_customer transaction:
  find upd_customer EXCLUSIVE-LOCK where upd_customer.custNum = 99 no-error.  /* this will WAIT for the record to be available, add NO-WAIT, test LOCKED and add some logic if you want to handle that somehow */
  if available upd_customer then
    upd_customer.discount = 0.10.
end.

3) *NEVER* have any user interaction while a transaction is active or a record lock is live. Users do things like get a coffee, go to lunch or leave on a two-week holiday while being prompted on the screen....
Code:
/* horrible, very bad, awful, never do it like this code */
find customer where custNum = 99.
update discount.

4) The compiler is smarter than you are. If the compiler warns you that you already have an active transaction the solution is NOT to remove the transaction keyword. The solution is to find the enclosing accidental transaction and fix it. Likewise, if the compiler complains about your upd_customer buffer and the FOR block the issue is with a *previous* reference to that buffer.
 
Top