FIND xxx OF yyy rules

Posted by Riverside Software on 28-Nov-2017 10:23

Hello all,

I vaguely remember having read somewhere the rules of the OF option (how the join is being resolved). The documentation only gives limited information. Would anybody have a link to a more detailed source of information ?

Thanks

Gilles

Posted by Torben on 28-Nov-2017 10:56

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.

All Replies

Posted by ChUIMonster on 28-Nov-2017 10:32

OF should be treated as an error.

Posted by gdb390 on 28-Nov-2017 10:34

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

I saw your session @ Prague with your anomalies

Posted by Torben on 28-Nov-2017 10:56

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.

Posted by Jeff Ledbetter on 28-Nov-2017 12:07

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

Posted by ChUIMonster on 28-Nov-2017 12:30

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!

Posted by Jeff Ledbetter on 28-Nov-2017 12:34

"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? :)

Posted by Mike Fechner on 28-Nov-2017 12:40
Jeff, you never had headaches caused by an OF resolved differently than you thought?
 
The 4GL should protect us from those headaches.
 
Posted by Jeff Ledbetter on 28-Nov-2017 12:48

"..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.

Posted by Mike Fechner on 28-Nov-2017 12:52
Assumingly Tom had enough headaches in his life already.
Posted by Thomas Wurl on 28-Nov-2017 12:53

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 ...

Posted by Mike Fechner on 28-Nov-2017 12:58
Fully agree with Thomas – when you know what you are doping. Those things are fine in ad hoc queries etc….
 
But when talking about code that lives for decades and should survive generations of developers, clear and easy understandable code rules.
Posted by Thomas Wurl on 28-Nov-2017 13:14

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

Posted by ChUIMonster on 28-Nov-2017 13:17

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.

Posted by marian.edu on 28-Nov-2017 13:18
It’s funny the doc actually mention ‘relationship’ :)

What Progress need to do is to first mark the darn thing as *deprecated* in the doc, then think about actually implementing foreign-key relationship so then this can be activated back… that will be the only case when the 4GL might make it easier while not helping one shoot himself in the foot.

I really don’t understand why they would look for unique indexes in both parent/child tables and matching on the name and don’t care about the data type isn’t probably the best decision either… but then again we only infer relationships using best guess when we load a definition file so we definitively lack experience here ;)
 
Marian Edu

Acorn IT 
+40 740 036 212

Posted by Mike Fechner on 28-Nov-2017 13:18
😊
 
There’s always room for improvement.
Posted by marian.edu on 28-Nov-2017 13:22

Posted by Mike Fechner on 28-Nov-2017 13:32
Speechless, Marian?
Von: marian.edu [mailto:bounce-marianedu@community.progress.com]
Gesendet: Dienstag, 28. November 2017 20:24
An: TU.OE.Development@community.progress.com
Betreff: RE: [Technical Users - OE Development] FIND xxx OF yyy rules
 
Update from Progress Community
 
 

View online

 

You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

Flag this post as spam/abuse.

 
Posted by marian.edu on 28-Nov-2017 13:38
Let me ask Alexa… yeah, I do agree there is always room for improvements though :)

Marian Edu

Acorn IT 
+40 740 036 212

Posted by Thomas Wurl on 28-Nov-2017 13:55

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.

Posted by jmls on 28-Nov-2017 14:11

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 ;)

Posted by Riverside Software on 28-Nov-2017 14:26

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).

Posted by ChUIMonster on 28-Nov-2017 14:55

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.

Posted by Riverside Software on 28-Nov-2017 15:00

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

Posted by Mike Fechner on 28-Nov-2017 15:03

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

Posted by ChUIMonster on 28-Nov-2017 15:15

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.

Posted by marian.edu on 29-Nov-2017 01:22
Sure thing, maybe if you tell us more about the request… how bad do you want the punishment to be?

I’m with Tom on this one, definitively treat it as error and should be banned from any real code.

Just for fun, this is how the constraints are ‘modelled’ in the database schema… some tables there do have a numeric primary key (some integer, some int64), still recid is the ‘preferred’ way of joining tables, I’ll be damn if we ever add such a ‘feature’, don’t even think about it Mike :)



Marian Edu

Acorn IT 
+40 740 036 212

Posted by Patrick Tingen on 29-Nov-2017 01:24

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. 

Posted by ChUIMonster on 29-Nov-2017 12:20

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.

Posted by Etienne Begin on 29-Nov-2017 13:07

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

Posted by Patrick Tingen on 29-Nov-2017 14:12

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?)

Posted by Rick Terrell on 29-Nov-2017 14:22
Depends on if you compile with-NL. It doesn’t effect run time unless you run uncompiled code. 

Rick Terrell 
Principle Consultant, Professional Services 
Progress

Sent from my iPhone
Posted by Riverside Software on 29-Nov-2017 15:13

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...

Posted by OctavioOlguin on 13-Dec-2017 13:28

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

Posted by Patrice Perrot on 19-Dec-2017 03:08

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 ...

Posted by George Potemkin on 19-Dec-2017 04:10

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

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

Posted by marian.edu on 19-Dec-2017 04:52
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.

  
Marian Edu

Acorn IT 
+40 740 036 212

Posted by Patrice Perrot on 19-Dec-2017 04:57

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                                            

This thread is closed