Temp-Table

[Rajat Sharma]

Hi everyone, hope you all are well!

I have couple of question regarding temp-tables. I know these are very basic questions for this forum, for that i did go through “PROHAND” and few other docs that I have but couldn’t conclude anything regarding the same.

1. We have been working with temp-tables (in our shop), but normally we do use temp-tables sharing by using input-output parameters. I read somewhere that, when we pass temp-table by using input-output parameters then progress(AVM) copy the data to called procedure (.p file) and that is a kind of overhead so instead of passing temp-table by input-output parameters we should use BY-REFERENCE or BIND or TEMP-TABLE handle. I have been trying to do/create practical scenarios for that but couldn’t difference between them.

2. Should our sharing techniques (input-output, by-reference, bind, handle, and temp-handle) depends on the kind of sharing like: sharing temp-tables in different procedures (.p files) of single session or multiple session?

3. In many previous threads, I read that we shouldn’t use SHARED TEMP-TABLE are devils (why, please suggest). If yes, then what is the alternative that we should use and is it applicable only to shared temp-tables or we shouldn’t use SHARED anywhere (like: SHARED VARIABLE, STREAMS). Please suggest.

Please suggest or share any document relevant to my issues.

Thanks & Regards!
Rajat.

 

[tamhas]

Shared variables of any kind are evil because they provide a very poorly defined interface between code units. With parameters you know exactly what is being passed and in which direction. With shared variables you have no idea where it might be modified. The worst case example of this are a number of legacy applications that have large includes containing many shared values which are included everywhere, even though only a couple are referenced in any given code unit (if any!).

By reference is considered an wonderful invention by many since it allows passing a parameter temp-table without making a deep copy, a significant saving in memory and processing if the temp-table is large. I personally have never used it because my preference is to encapsulate all logic related to a temp-table in a single object and therefore avoid passing the temp-table at all. This puts all the logic related to the temp-table in one place and allows an interface to the outside world which is independent of the implementation details.

 

[GregTomkins]

I would probably say BY-REFERENCE is wonderful, except we have thousands of programs written before it came along, and in real life, 99% of our TT's are so small that the copying time doesn't matter. So we switch things to BY-REFERENCE as necessary in the few places where we pass around giant temp-tables. As a matter of best practice, it's probably a good idea, assuming you don't use OOABL as Tamhas suggests (we don't).

A couple of other points:

1. BY-REFERENCE won't have any effect if you are going across a process boundary (eg. Win32 client to AppServer).

2. BIND is relevant to persistent procedures. We never use it. I have a blog post somewhere that tries to explain it.

3. You didn't mention REFERENCE-ONLY. I forget what that does

4. I agree that SHARED anything is almost always a bad thing. PARAM is the better way for sure. On the other hand, the DB and the DOM are both really giant shared variables

--

As an aside: TT's are, IMO, the 2nd best thing about Progress. They're one of those things that are very simple and flexible, almost always do what I want, are almost always fast enough that I don't care if an array or whatever would be better, and are syntactically and functionally very consistent with how DB tables work. When I go back into the non-Progress world, lack of TT's is something that really pains me.

 

[tamhas]

Note that encapsulation is not just for OO. One can encapsulate a TT in a PP just as well as in a class.

 

[Cringer]

I'm just going to quote something from a colleague as we had an email discussion about one of the issues around "by value" and "by reference":

Consider the following:-

DEFINE TEMP-TABLE tt NO-UNDO

  FIELD ErrorText AS CHARACTER.


CREATE tt.

RUN ProcessTest.p (INPUT-OUTPUT TABLE tt) NO-ERROR.

FIND tt.


MESSAGE"Error="  ERROR-STATUS:ERROR SKIP 

"Return Value="  RETURN-VALUE  SKIP

"tt error text=" tt.ErrorText  VIEW-AS ALERT-BOX.

Where ProcessTest.p is the following:-

DEFINE TEMP-TABLE tt NO-UNDO

  FIELD ErrorText AS CHARACTER.

 

DEFINE VARIABLE lv-ReturnValue AS CHARACTERNO-UNDO. 


DEFINE INPUT-OUTPUT PARAMETER TABLE FOR tt.

 

FOR EACH tt TRANSACTION:

  /* Do some processing and if this fails */

  ASSIGNtt.ErrorText = "some error text".

END.


IF CAN-FIND(FIRST tt

  WHERE tt.ErrorText NE"") THEN RETURN ERROR"At least one record contains Errors".

Seemed pretty straightforward to me however, when run, it didn’t give me what I initially expected.

As the temp-table is NO-UNDO I assumed I would get back the ‘updated’ version regardless of whether the ERROR condition was raised.

Unfortunately this is not the case L

Basically what happens in this type of situation is that the temp-table is passed BY-VALUE and what this means is that it is copied from the temp-table in the calling procedure to a new instance in the called procedure and on exit of the procedure the values in the called procedure are copied ‘over’ the calling procedure’s temp-table.

Sadly the reverse copying isn’t done if you RETURN ERROR.

Sensible enough when you have time to think about it.


So can I get round this? Yes J (and no L).


If I change the calling code to:-

DEFINE TEMP-TABLE tt NO-UNDO

  FIELD ErrorText AS CHARACTER.


CREATE tt.

RUN ProcessTest.p (INPUT-OUTPUT TABLE tt BY-REFERENCE) NO-ERROR.

FIND tt.


MESSAGE "Error="  ERROR-STATUS:ERRORSKIP 

"Return Value="  RETURN-VALUE  SKIP

"tt error text=" tt.ErrorText  VIEW-AS ALERT-BOX.

..and the called code to:-

DEFINE TEMP-TABLE tt NO-UNDO REFERENCE-ONLY

  FIELD ErrorText AS CHARACTER.

 

DEFINE VARIABLE lv-ReturnValue AS CHARACTER NO-UNDO. 


DEFINE INPUT-OUTPUT PARAMETER TABLE FOR tt.

 

FOR EACH tt TRANSACTION:

  /* Do some processing and if this fails */

  ASSIGN tt.ErrorText = "some error text".

END.


IF CAN-FIND(FIRST tt

  WHERE tt.ErrorText NE"") THEN RETURN ERROR "At least one record contains Errors".

then I get what I expected.

So what I am doing now is passing the temp-table BY-REFERENCE i.e. telling Progress not to copy it but use this actual version in both procedures.

However, Progress only takes notice of this if the called program defines its version of the temp-table as REFERENCE-ONLY . If you don’t then it ignores the BY-REFERENCE and reverts to BY-VALUE.