Question more efficient query to modify

#1
Hi Guys,

It has been while since my last question.

So my work of the day is this:
I have a browser who is directly connected to a table like a Customer table.
I let my user change a value on this table and I wanna applied the modification like this:

Code:
DEFINE BUFFER bCustomer FOR CUSTOMER.

FIND FIRST bCustomer WHERE bCustomer.uticod = CUSTOMER.uticod NO-LOCK NO-ERROR.
IF NOT AVAILABLE bCustomer THEN
    RETURN.
    
FIND CURRENT bCustomer NO-ERROR.
bCustomer.value = new-value
RELEASE bCustomer.

But I also want to improve my process in a more reliable way and efficiency.

So I was thinking to find the row with the recid like this:

Code:
DEFINE BUFFER bCustomer FOR CUSTOMER.

FIND FIRST bCustomer WHERE RECID(Customer) = RECID(CUSTOMER) NO-LOCK NO-ERROR.
IF NOT AVAILABLE bCustomer THEN
    RETURN.
    
FIND CURRENT bCustomer NO-ERROR.
bCustomer.value = new-value
RELEASE bCustomer.
What is the best solution? Or is there a better way to do it ?

Thanks in advance.

Best Regards
 
#2
You want to keep your transaction small and concise. Also, "RELEASE" does not do what you might think -- it is pretty much unnecessary.

Try this:

Code:
DEFINE BUFFER bCustomer FOR CUSTOMER.

do for bCustomer TRANSACTION:
    FIND FIRST bCustomer WHERE RECID(Customer) = RECID(CUSTOMER) EXCLUSIVE-LOCK NO-ERROR.
    IF AVAILABLE bCustomer THEN DO:
        bCustomer.value = new-value
    END.
end.
Also, you may want to consider using ROWID instead of RECID. While both may work, I believe when using Type II storage ROWID is preferable (cannot remember exact reason why but others I'm sure will weigh in with this).
 

Cringer

ProgressTalk.com Moderator
Staff member
#3
There is more to it than this, but AFAIK RECID is not guaranteed to be unique across storage areas.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#6
FIND FIRST bCustomer WHERE RECID(Customer) = RECID(CUSTOMER) EXCLUSIVE-LOCK NO-ERROR.
The RECID function is deprecated and should not be used with application tables. There is one acceptable use case for it as far as I am aware, which is joins on schema tables with recid key fields. You should use ROWID instead.

Also, I have a feeling that a certain someone will take issue with your use of FIRST in this context.

There is more to it than this, but AFAIK RECID is not guaranteed to be unique across storage areas.
That is true for both RECID and ROWID. However a ROWID is unique within a given table.
 
#7
Good catch Rob on the FIRST -- cut/paste/not paying attention error on my part.
And thanks for the clarifications on the ROWID -- we changed years ago but I couldn't remember the exact reason other than it is deprecated.
 
#8
FIRST is especially pointless and utterly confusing in this example. Why is it there? What benefit does the poster believe it brings to his code?

Larry also quietly corrected the lock status issues in the original code. It would be good to point out that the original FIND CURRENT is also problematic because it does not specify a lock -- thus it gets the record SHARE-LOCK by default and then the 4gl has to upgrade the lock to EXCLUSIVE-LOCK behind the scenes in order to actually update the data. It will then be downgraded to SHARE-LOCK when the transaction ends and due to the original poor scoping will stay that way until the buffer goes out of scope (Larry's code fixes that).

In general the question seems to have nothing to do with making a query more efficient but a lot to do with correctly constructing an update.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#9
And thanks for the clarifications on the ROWID -- we changed years ago but I couldn't remember the exact reason other than it is deprecated.
Certainly, deprecation on its own is good enough reason to not write any new code that uses RECID.

Another reason is functional: in 11.4 PSC added Table Partitioning. When this feature is used on a table, the rule that a recid is unique within a table is no longer guaranteed. By contrast, a rowid in a partitioned table is still guaranteed to be unique as the rowid definition was extended to include the partition ID of the partition in which the record is stored.
 
#10
Larry also quietly corrected the lock status issues in the original code. It would be good to point out that the original FIND CURRENT is also problematic because it does not specify a lock -- thus it gets the record SHARE-LOCK by default and then the 4gl has to upgrade the lock to EXCLUSIVE-LOCK behind the scenes in order to actually update the data. It will then be downgraded to SHARE-LOCK when the transaction ends and due to the original poor scoping will stay that way until the buffer goes out of scope (Larry's code fixes that).

In general the question seems to have nothing to do with making a query more efficient but a lot to do with correctly constructing an update.
Thank you for the clearing on the lock status.

And yes you are right, my main issue is to build a correct update procedure.

I will use your method and update my method.
 
#11
So this should be coud:

Code:
DEFINE BUFFER bCustomer FOR CUSTOMER.

do for bCustomer TRANSACTION:
    FIND bCustomer WHERE RECID(Customer) = RECID(CUSTOMER) EXCLUSIVE-LOCK NO-ERROR.
    IF AVAILABLE bCustomer THEN DO:
        bCustomer.value = new-value
    END.
end.
And so the end of the DO TRANSACTION will release the row-id ?
 

Cringer

ProgressTalk.com Moderator
Staff member
#12
So long as the TRANSACTION keyword isn't actually within the scope of another transaction, then, yes, the end of the TRANSACTION block will see the record go out of scope and the updates will be committed.
 
#13
Actually there are a couple of typos in your code, you really want:
Code:
DEFINE BUFFER bCustomer FOR CUSTOMER.

DO FOR bCustomer TRANSACTION:
    FIND bCustomer WHERE ROWID( bCustomer ) = ROWID( CUSTOMER ) EXCLUSIVE-LOCK NO-ERROR.
    IF AVAILABLE bCustomer THEN DO:
        bCustomer.value = new-value.
    END.
end.
 
#14
So long as the TRANSACTION keyword isn't actually within the scope of another transaction, then, yes, the end of the TRANSACTION block will see the record go out of scope and the updates will be committed.
Thank you for the help @Cringer

Actually there are a couple of typos in your code, you really want:
Code:
DEFINE BUFFER bCustomer FOR CUSTOMER.

DO FOR bCustomer TRANSACTION:
    FIND bCustomer WHERE ROWID( bCustomer ) = ROWID( CUSTOMER ) EXCLUSIVE-LOCK NO-ERROR.
    IF AVAILABLE bCustomer THEN DO:
        bCustomer.value = new-value.
    END.
end.
Ow yeah, I missed the "b" for my buffer CUSTOMER table and also to use the ROWID instead of the RECID
 
#15
Hi,

One more question.

I was writting something like this inside a "choose" trigger :

Code:
DEFINE BUFFER bCustomer for Customer.

DO FOR bCustomer TRANSACTION :
    FIND bCustomer WHERE ROWID(bCustomer) = ROWID(Customer) EXCLUSIVE-LOCK NO-ERROR.
    ASSIGN
        bCustomer.utisig = c-uticod
        bCustomer.datsig = TODAY
        bCustomer.hmssig = STRING(TIME, "HH:MM:SS")
        bCustomer.stasig = 1
        bCustomer.remarq = FI-Remarq:SCREEN-VALUE.
END.


FIND FIRST bCustomer WHERE bCustomer.custnum  = Customer.custnum NO-LOCK NO-ERROR.
I used the "FIRST" keyword because only one record exist for this search.

But I have an error number 244 : "Reference to the table bCustomer in conflict with the instruction reference on block" (traduct from French with my English)

I tried some google search for nothing.

Do you have an idea?
 
Last edited:
#16
That is EXACTLY the WRONG reason to use FIRST.

If there is one record and a unique index then FIRST has no value and misleads the programmer into thinking that there may be multiple records in a result set. When your code then does nothing to handle a potential next record the thoughtful programmer should be scratching their head wondering WTF is going on and where the rest of the code disappeared to.

If there is one record and the index is NOT unique then you are making the first record special -- and that is a violation of 3rd normal form. IOW your db is no longer "relational". And the thoughtful programmer will also be wondering WTF is going on.

So please stop using FIRST. Put it on the keyword forget list. FIRST is not a mandatory appendage to every instance of FIND.

You have NOT followed our previous advice and strong-scoped bCustomer to the transaction block with "DO FOR bCustomer TRANSACTION". And you are re-finding bCustomer *outside* the transaction block.

The point of bCustomer is to use a distinct named buffer for the *update* operation. And *only* for the update operation. I personally prefer to name those buffers according to their usage. For instance, rather than "bCustomer" I would have called it "updCustomer". This is a *much* better naming convention and is the original spirit of "hungarian notation" (as opposed to the perverted idea that slapping gibberish about the data type onto a variable or field name is useful).

Outside of the transaction block you should be referring to "customer". Not "bCustomer" (or updCustomer").
 
#17
Ok, so I will update my code.
Can I put in the same procedure two DO FOR <tablename> TRANSACTION with the same Table Name ?

And also I understand that I lack of knowledge in the good way of programming with progress. Do you know where I can find a good "tuto" where all the concept and idea are explained ?
 
#18
Also -- "inside a choose trigger" implies that a user is interacting with your code. Therefore you very much want to be using NO-WAIT and handling potential LOCKED conditions appropriately. If you do not then the user could be stuck sitting there waiting for a record that someone else has locked for a long time.
 
#19
Also -- "inside a choose trigger" implies that a user is interacting with your code. Therefore you very much want to be using NO-WAIT and handling potential LOCKED conditions appropriately. If you do not then the user could be stuck sitting there waiting for a record that someone else has locked for a long time.
Do you have some example of that in addition to the progress documentation ?
 
Top