FIND xxx OF yyy rules

OF should be treated as an error.

I was waiting how long it would take before Tom was going to answer :)

I saw your session @ Prague with your anomalies

From 11.7.2 OpenEdge® Development: ABL Reference page 1023+1024

OF table

Relates record to one other table specified by a table or buffer name (table). The relationship is based on common field names between record and table that also participate in a UNIQUE index for either record or table. When you use OF and the UNIQUE index is multi-field, all fields in the index participate in the match criteria.

Note: For the OF keyword to properly detect a relationship between two tables, only one such relationship is allowed.

Not having attended your session, can you edify us mere mortals on your disdain for this construct?

Gilles' question is the answer ;)

Nobody knows what OF really does.

Feel free to explain how the documented OF syntax shown above applies to:

for each _file no-lock, each _field no-lock of _file:

 display _file-name _field-name.

end.

When you see OF it isn't obvious from the code how the join is done.  You have to scratch your head and think about it and even then you're likely to be wrong.  It is one of those "makes a nice demo" features that, IMHO, nobody should ever actually use.

As if that isn't bad enough there is also:

find customer 1 no-lock.  /* pointed out by a vigilant attendee! */

You should have attended my session - it was the highlight of the conference :)  I have submitted my slides though so you should be able to download them soon and be able to see what you missed - and know better next time!

"When you see OF it isn't obvious from the code how the join is done."

Isn't that the point of a 4GL though? Limiting our necessity to think too hard? :)

"..you never had headaches caused by an OF resolved differently than you thought?"

Probably. I have nothing against it though.

I've had memory leaks because we forgot to do a DELETE OBJECT statement but that doesn't mean I want to stop using TABLE-HANDLE as an output parameter. :)

Anyhow, I just wanted to find out why the ChUIMonster didn't like; not start a holy war.

for each _file no-lock, each _field no-lock of _file:

display _file-name _field-name.

end.

That's easy. The join is done by recid(_file) and _field._file-recid. A special rule documented somewhere. :-)

find customer 1 no-lock.  /* pointed out by a vigilant attendee! */

Also easy: Single field primary index and put in a value. Worked forever! Crap!

But I agree. Using OF is not always the best idea, except you know what you do ...

Fully agree with Mike except that I stoped 'doping' already.

Well, yeah, I happen to know how those things work.  But... I have no idea where the "somewhere" that they are documented is and the easy to find documentation doesn't cover them.  So that poor suffering programmer who comes along to maintain my all too clever code is going cuss me out.  If I'm lucky.  More likely the programmer will copy my terrible example without understanding it and it will become an out of control monster.

It’s funny the doc actually mention ‘relationship’ :)

Let me ask Alexa… yeah, I do agree there is always room for improvements though :)

Ok. We can talk forever about this. OF is just there and it works fine. It doesn't help to understand code, but it's very easy to wright other code that nobody unterstands. It's a question of a style guide not of the 4GL.

@Marian: OF cares about data types:

/* Doesn't work */

DEFINE TEMP-TABLE X NO-UNDO

   FIELD X AS INTEGER

   INDEX pix IS PRIMARY UNIQUE X.

DEFINE TEMP-TABLE Y NO-UNDO

   FIELD X AS DECIMAL

   FIELD Y AS CHARACTER.

FOR EACH X, EACH Y OF X:

END.

/* Works */

DEFINE TEMP-TABLE X NO-UNDO

   FIELD X AS INTEGER

   INDEX pix IS PRIMARY UNIQUE X.

DEFINE TEMP-TABLE Y NO-UNDO

   FIELD X AS INTEGER

   FIELD Y AS CHARACTER.

FOR EACH X, EACH Y OF X:

END.

just because you can use "of" doesn't mean that you should ... same as "recid", "find first" and "share lock" by default etc

my problem with "of" is that

a) behaviour can change without changing the code (adding another index, for example), which from my point of view is a no-no
b) it may not be the most efficient mechanism for retrieving data
c) the coder has no idea which fields are being used

but then, hey, I'm just a dinosaur ;)

Thank you all ! I promise I won't use OF anywhere in my code, the question was on how to improve the SonarQube rule which detects the OF keyword.

@Torben: I've found this documentation snippet. I *think* I've read something more detailed some time ago, but I can't find it (if it even exists).

I stand by my original suggestion -- SonarQube should treat it as an error.

Can you integrate SonarQube with a cattle prod embedded in the programmer's chair?  That might help.

Sonarqube already reports OF as a code smell. I'd like to suggest the WHERE clause which is used by the compiler.

Yes all, please help Gilles to implement my enhancement request :-)

I like the cattle prod idea better.

But it seems like you've probably got enough to make a  stab at it in this thread - I think the RECID variation is probably the biggest missing part of the official documentation.

Sure thing, maybe if you tell us more about the request… how bad do you want the punishment to be?

I'm not totally convinced (and far enough away from Toms cattle prod) that 'OF' is a no-go area. To be honest I find a construction like

FOR EACH _file, 
  EACH _field OF _file:
  ...
END.

more readable than 

FOR EACH _file, 
  EACH _field WHERE _field._file-recid = RECID(_file):
  ...
END.

But that may be a matter of personal taste. What I often hear in an 'OF-discussion' is that the behaviour can change when new indexes are added. I would like to see an example of that. How can a new index be 'better' than an already existing one? Removing indexes from a database is - imo - only done when it is certain that they are not used, so that should not be an issue. 

NO-LOCK!!!  You left out the NO-LOCK :(  Even trivial examples need to have NO-LOCK.

I'm going to hate myself if anyone ever uses this post as an excuse but in some ways I agree that it is more natural and "readable" with OF.  That's why I say "it makes a good demo".  The problem is that on introspection it is unclear what OF actually means.

I find OF actually less readable as you cannot tell what the join is.  I am a lot more concerned with that than any technical issues OF may introduce.

Using OF with schema tables is fine with me.  Using an explicit join clause could cause problems when Progress changes their schema table relationships, whereas OF would continue to work.... I guess !

Etienne

AAAAAARCH Tom, you're right. OMG I left it out. I really left it out. I'll poke myself with the cattle prod, I promise.

Ok, kidding aside, I must admit that I only use the OF-construction for schema tables. Using it on normal tables just doesn't feel good since it is not explicitly clear what the join is. And in code I prefer clarity above all. Even above performance in most cases. I would still like to see an example though where adding an index would change the OF-relation.

(would it help if I said that I started my session with the -NL parameter?)

Depends on if you compile with-NL. It doesn’t effect run time unless you run uncompiled code. 

Back to the original question, is there a pseudo-code version available somewhere ? I'd feel better using something already verified than rewriting from scratch...

I can give you my FIND xxx OF yyy rules I live by...

Rule #1 :  don't use OF constructor

Rule #2 :  go to rule #1

It is not a rule, the good rules are well known...

you can use COMPILE  XREF as a workaround.

Example :

          Orderline_of_item.p

          find first orderline of item no-lock no-error .

         COMPILE Orderline_of_item.p XREF  xref-Orderline_of_item.p

        In Xref-Orderline_of_item.p , you have the "ACCESS" lines.

               COMPILE C:\travail\progress\test_riverside\orderline_of_item.p                

               CPINTERNAL utf-8                                                              

               CPSTREAM 1252                                                                  

               STRING "OrderLine" 9 NONE UNTRANSLATABLE                                      

               STRING "Item" 4 NONE UNTRANSLATABLE                                            

       =>   ACCESS sports2000.OrderLine Itemnum                                            

       =>   ACCESS sports2000.Item Itemnum                                                

              SEARCH sports2000.OrderLine itemnum                                            

Xref show that you are using orderline.itemnum and item.itemnum when you are doing a "find first orderline of item no-lock no-error ."

It seems that

        "find first orderline of item no-lock no-error ."

is the same that

"find first orderline where orderline.itemnum = item.itemnum no-lock no-error ."

I use this workaround on more complex queries ...

> I use this workaround on more complex queries ...

Would it be easier to use Data Administration -> Database -> Reports -> Table Relations ?

It would, if there would actually be support for those darn foreign-keys. Hell, that’s why it’s called (R)DBMS in the first place and this is already supported by the SQL engine so please PSC just make a new VST to hold info for those FK, add support for it in DDL… then yes, using OF would actually be a 4GL feature.

Hi George,

1) I must deal with more than 10 000 Db, it should be hard and it should be cost  effective to have an uptodate "Table Realtions" for those 10 000 DB,

 - a compile XREF for a 2 lines sources code spend less than 0.017 seconds, i do it when i need

2) I do not think that  Data Administration -> Database -> Reports -> Table Relations will work with 2 Db .

In the Example below Compile XREF will give me an answer  :

FIND FIRST Compta.cptcli OF gco.client NO-LOCK NO-ERROR.

COMPILE C:\travail\progress\test_riverside\compta-cptcli_OF_gco-client.p      XREF    

CPINTERNAL iso8859-1                                                            

CPSTREAM iso8859-1                                                              

STRING "cptcli" 6 NONE UNTRANSLATABLE                                            

STRING "Compte client" 13 NONE TRANSLATABLE                                      

STRING "client" 6 NONE UNTRANSLATABLE                                            

STRING "CLIENTS" 7 NONE TRANSLATABLE                                            

ACCESS Compta.cptcli cod_cli                                                    

ACCESS GCO.client cod_cli                                                        

SEARCH Compta.cptcli clicod                                                      

STRING "clicod" 6 NONE UNTRANSLATABLE